Loading related entities in separate queries and sorting of children collection

Question

I need to retrieve data using EF 4.1 Code first that are contained in several related tables. Currently if i use this

return Context.Set<Entity>()
              .Include(x => x.Children.Select(y => y.GrandChildren.Select(z => z.Child)))
              .Include(x => x.SomeEntity)
              .Include(x => x.AnotherEntity)
              .OrderByDescending(x => x.Id)
              .FirstOrDefault();

The data is fetched correctly, but I am worried about two things:

1) it seems that there is no way to sort Chilren / GrandChildren

2) the data is flattened in one table, which means that Entity (and all others) data is duplicated for each GrandChild record

Questions:

What Do I need to do to be able to sort Children / GrandChildren?
The 2nd point itself may not be a problem in this particular case, since the amount of data that is transferred is not big - max 30 records with 30 columns. Still, I would like to know if there is a way to load Enttity, Children and GrandChildren separately (3 queries), and join them on client site?

Returned resultset needs to be updatable.

score 3 · Accepted Answer · edited May 23 '17 at 10:34

What Do I need to do to be able to sort Children / GrandChildren?

Sort them in your application. That is the only reliable way. Include doesn't allow sorting at all and the approach showed below is not reliable because you don't have control over EF's internal mechanism and you cannot have your navigation property as SortedList or other collection maintaining the sort order (which is requirement to have reliably sorted relation).

the data is flattened in one table, which means that Entity (and all others) data is duplicated for each GrandChild record

This is valid objection. You can avoid it by turning off lazy loading and use separate queries to load data:

context.Configuration.LazyLoadingEnabled = false;
var parent = context.Set<Entity>().First(e => e.Name = "ABC");
// Load relations in separate query
context.Set<Child>()
       .Where(c => c.Parent.Name == "ABC")
       .OrderBy(c => c.Name) // You can at least try it but as mentioned above it may not work in all scenarios
       .Load();
// Now parent.Children collection should be filled

You can follow same approach for other relation and for nested relations as well. The key is to correctly construct Where condition for children and grandchildren loading.

It doesn't mean that this approach will be faster that flattened table. This approach makes separate database roundtrip for every executed query so the flattened table can be faster in smaller data sets.

Loading related entities in separate queries and sorting of children collection

1 Answers1

Linked