2

I used SQLite-Net Extensions in the following code to retrieve 1000 rows with their children relationships from an Sqlite database: 

var list =
SQLiteNetExtensions.Extensions.ReadOperations.GetAllWithChildren<DataModel>(connection);

The problem is that the performance is awkward. Because GetAllWithChildren() returns a List not an Enumerable. Does exist any way to load the records in to an Enumerable using Sqlite.net extensions?

I now use Table() method from Sqlite.net, loads the fetched rows in to the Enumerable but I dont want to use it because it does not understand the relationships and does not load the children entities at all.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
a.toraby
  • 3,232
  • 5
  • 41
  • 73

2 Answers2

7

GetAllWithChildren suffers from the N+1 problem, and in your specific scenario this performs specially bad. It's not clear in your question what you're trying, but you could try these solutions:

Use the filterparameter in GetAllWithChildren:

Instead of loading all the objects to memory and then filter, you can use the filter property, that internally performs a Table<T>().Where(filter) query, and SQLite-Net will convert to a SELECT-WHERE clause, so it's very efficient:

var list = connection.GetAllWithChildren<DataModel>(d => d.Name == "Jason");

Perform the query and then load the relationships

If you look at the GetAllWithChildren code you'll realize that it just performs the query and then loads the existing relationships. You can do that by yourself to avoid automatically loading unwanted relationships:

// Load elements from database
var list = connection.Table<DataModel>().Where(d => d.Name == "Jason").toList();
// Iterate elements and load relationships
foreach (DataModel element in list) {
    connection.GetChildren(element, recursive = false);
}

Load relationships manually

To completely workaround the N+1 problem you can manually fetch relationships using a Contains filter with the foreign keys. This highly depends on you entity model, but would look like this:

// Load elements from database
var list = connection.Table<DataModel>().Where(d => d.Name == "Jason").toList();
// Get list of dependency IDs
var dependencyIds = list.Select(d => d.DependencyId).toList();
// Load all dependencies from database on a single query
var dependencies = connection.Table<Dependency>.Where(d => dependencyIds.Contains(d.Id)).ToList();
// Assign relationships back to the elements
foreach (DataModel element in list) {
    element.Dependency = dependencies.FirstOrDefault(d => d.Id == element.DependencyId);
}

This solution solves the N+1 problem, because it performs only two database queries.

Community
  • 1
  • 1
redent84
  • 18,901
  • 4
  • 62
  • 85
  • Thanks for your answer. But using `Table()` in your solutions we are limited to filter only on the properties of the main entity. What should I do if I want to filter on children properties? Do I have to join the tables manually in sql? – a.toraby Jul 27 '16 at 08:43
  • 2
    That would be the most efficient way, yes. Converting your 'in-memory' filters to SQL 'where' statements is usually much more efficient. Your case is even worse because of the N+1 problem when loading the entity tree. – redent84 Jul 27 '16 at 08:58
  • Thanks for your reply. So, actually your thing is not as useful as I thought yesterday. I prefer to write all queries myself and care the application size instead of using a package just to implement a simple data List without any paging and practical search and real Relational Mapping. Thanks anyway. – a.toraby Jul 27 '16 at 09:11
  • Take into account that filtering on relationship properties is not supported on many server-side ORM. However, if you think that the library can be improved, feel free to open a issue, or even better, submit a pull request. – redent84 Jul 27 '16 at 09:43
  • Except if your DB does not consist of 2 tables, but dozens...then implementing "Load relationships manually" is basically impossible... – Zoli Oct 12 '20 at 12:25
0

Another method to load relationships manually

Imagine we have these classes: 

public class Parent
{
    [PrimaryKey, AutoIncrement] public int Id { get; set; }
    public string Name { get; set; }

    public List<Child> children { get; set; }

    public override bool Equals(object obj)
    {
        return obj != null && Id.Equals(((BaseModel) obj).Id);
    }
    public override int GetHashCode()
    {
        return Id.GetHashCode();
    }
}

and 

public class Child
{
    [PrimaryKey, AutoIncrement] public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }
}

Hint these classes have one-to-many relation. Then inner join between them would be: 

        var parents = databaseSync.Table<Parent>().ToList();
        var children = databaseSync.Table<Child>().ToList();

        List<Parent> parentsWithChildren = parents.GroupJoin(children, parent => parent.Id, child => child.ParentId,
            (parent, children1) =>
            {
                parent.children = children1.ToList();
                return parent;
            }).Where(parent => parent.children.Any()).ToList();
iman
  • 121
  • 1
  • 4