LoadSelect return all references

ServiceStack.OrmLite SQL Server
1

Hi,

I’m trying to use LoadSelect to return all references with a single database hit.

I can’t figure out how to get the Checklist.Manager1.Owner or Checklist.Manager2.Owner populated using LoadSelect against the model below. If the data set is small I can loop through the results and LoadReferences(Checklist.Manager1) and LoadReferences(Checklist.Manager2) but this is very slow and unusable with our data set size.

After doing some searching it appears that LoadSelect only returns the first level of references ?, so what is the best way to get Checklist.ManagerX.Owner populated?

Thanks for any help,
John

Model looks like:

public class Checklist
{
	[AutoIncrement, PrimaryKey]
	public int ChecklistId { get; set; }

	[References(typeof (Manager))]
	public int? Manager1Id { get; set; }

	[Reference]
	public Manager Manager1 { get; set; }

	[References(typeof (Manager))]
	public int? Manager2Id { get; set; }

	[Reference]
	public Manager Manager2 { get; set; }
}

public class Manager
{
	[AutoIncrement, PrimaryKey]
	public int idManager { get; set; }

	[References(typeof (Owner))]
	public int idOwner { get; set; }

	[Reference]
	public Owner Owner { get; set; }
}
2

Multi level references like this are likely going to cause inefficient N+1 queries which is why OrmLite only loads 1-level deep as it never transparently performs N+1 queries.

If you had the original query you may be able to reuse a clone of it and change it to select the Owner table. Otherwise you could fetch all owner ids and do a SELECT IN to fetch all owners in a single query, then you should be able to merge the 2 Owners and Managers results together with the Merge extension method: https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/README.md#merge-disconnected-poco-result-sets

3

Thanks for the reply.

The problem with fixed 1-level deep queries is when using a schema with many foreign keys, I’ve simplified the model - some other parts have nested self-referencing keys.

I can understand concerns around inefficient queries but I would like to be able to have a reliable method to traverse a complex model (ideally including the first level of self references) in a single direction as deep as the model goes. Avoiding the N+1 issue by using a join.

In this example when starting from Checklist, all the below would be populated.

Checklist -> Manager 1 -> Owner
Checklist -> Manager 2 -> Owner

Is there any combination of Load*, Select with Joins, Includes or otherwise which will allow me to do this in one hit?

It looks like the Merge extension will do what I need in this example.

4

There’s no API that does implicit N+1 queries as that relies on having a dynamic proxy built over virtual properties to execute db logic inside the model which is in contrast to the code-first POCOs you can use with OrmLite (i.e. where it doesn’t generate an interm dynamic data model that it uses internally instead).

You can’t do this with 1 db-hit, using the Merge API described above just requires 1 additional db query, which is a lot less than the 1 db hit per row (N+1) that multi nested references usually cost.