SQLite Extentions inner joins on GetAllWithChildren

Question

We're currently trying SQLite Extentions (PCL) as an ORM.

We're wondering if the mapping is supposed to build a SELECT with INNER JOINs on children if they are correctly configured in the entity?

public class Project
{
    [PrimaryKey]
    public long Id { get; set; }

    [ForeignKey(typeof(EnterpriseClient))]
    public long EnterpriseClientId { get; set; }

    [ManyToOne]
    public EnterpriseClient EnterpriseClient { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<WorkOrderHead> WorkOrderHeads { get; set; }
}

If we get all the Projects with GetAllWithChildren:

var x = _db.GetAllWithChildren<Project>(p => true);

Our result is multiple select for each child (EnterpriseClient) and we were hoping that it would en in one select and a join to collect all the data at once.

Is our configuration wrong or it's supposed to be that way?

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

Right now SQLite-Net Extensions performs a SELECT for each property to be fetched and also suffers from the N+1 issue in read operations (it is already solved for write operations). It's implemented as a very thin layer over SQLite.Net providing you some convenience methods for accessing entity relationships. Currently it works the way you described as an intended behavior. Accessing registers by primary key or an indexed property it's very fast, and performance is not an issue for small databases like the used in most mobile projects.

SQLite-Net Extensions is an evolving project, so feature requests (and pull requests, of course) are always welcome. However, INNER JOINs would break the SQLite.Net mapping, so a single SELECT returning all the required information would require re-implementing the SQLite.Net mapping mechanism.

It is theoretically possible to workaround the N+1 issue performing a single SELECT for each property, so recursive TO-MANY operations would see a performance improvement. I've created an issue to keep track of this request.

Happy coding!

edited May 23 '17 at 12:15

Community

1
1

answered Jul 07 '15 at 09:12

redent84

18,901
4
62
85

Thanks, that's what I feared. So in mobile world, should we forget about ORM and focus on doign our own SELECTs on the database? – Nicolas Belley Jul 07 '15 at 11:29
1

Well, I needed to perform manual selects in very few scenarios. Most of the times the performance improvement was negligible and the easy to use and refactor friendly approach tipped the scales in favor of SQLite-Net Extensions. Using asynchronous operations reduces the performance impact even more. So I'd suggest you to measure before optimizing. – redent84 Jul 07 '15 at 11:34
After benchmarking, doing our own selects was by a big factor faster than nested selects using the framework. We built EntityViews builders that build complex queries. Using the default resolve of the child objects was way slower... – Nicolas Belley Sep 04 '15 at 16:52

SQLite Extentions inner joins on GetAllWithChildren

1 Answers1