CODEWITHSUNDEEP
c#ormxamarin.androidsqlite-netsqlite-net-extensions
Nicolas Belley
Nicolas Belley

Reputation: 862

SQLite Extentions inner joins on GetAllWithChildren

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?

Upvotes: 0

Views: 1551

Answers (1)

redent84
redent84

Reputation: 19239

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!

Upvotes: 2

Related Questions