9

This is the second step of a question explained here: EF 4.1 code-first: How to load related data (parent-child-grandchild)?. With @Slauma's guidance, I have successfully retrieved data with this approach:

var model = DbContext.SitePages .Where(p => p.ParentId == null && p.Level == 1) .OrderBy(p => p.Order) // ordering parent .ToList(); foreach (var child in model) { // loading children DbContext.Entry(child) .Collection(t => t.Children) .Query() .OrderBy(t => t.Order) // ordering children .Load(); foreach (var grand in child.Children) { // loading grandchildren DbContext.Entry(grand) .Collection(t => t.Children) .Query() .OrderBy(t => t.Order) // ordering grandchildren .Load(); } }

Though this approach works, it sends many queries to the database and I am searching for a way to do this all in just one query. With @Slauma's guidance (explained in the answer at the above link), I have changed the query to this one:

var model2 = DbContext.SitePages .Where(p => p.ParentId == null && p.Level == 1) .OrderBy(p => p.Order) .Include(p => p.Children // Children: how to order theme??? .Select(c => c.Children) // Grandchildren: how to order them??? ).ToList();

Now, how can I order children (and grandchildren) when selecting them (such as shown in the first code example above)?

Improve this question
5
  • 1
    Take a look at this question: stackoverflow.com/questions/4156949/…. What you are trying to do is called "eager loading", and apparently, you you can't use OrderBy with Include. Commented Sep 23, 2011 at 1:41
  • Yes, I know about eager loading, and if you look at the first code (created by my own) you'll see I use a foreach statement on each object in each level (top for child, and, child for grandchild) same as what explained on your prepared link. but this need more queries against the database! I'm searching a way to do this all in one query, not more! Commented Sep 23, 2011 at 2:11
  • Can you eager load your entire structure, then do ordering in your views when they are required? There's almost no reason why you should be leaking presentation logic (ordering) in your data access logic. Commented Sep 23, 2011 at 2:21
  • There is just one reason: avoiding several loop. Here is a lot of foreach loop that I want to avoid them. In querying against the db or in view, there is no deference between them, we'll have: foreach(foreach()). So sorry I can't explain more ): Commented Sep 23, 2011 at 3:25
  • But I think in final, I have to do this. Select all of them by Include(Select()) and then order them. Thanks again, to your attention. Commented Sep 23, 2011 at 3:27

2 Answers 2

Reset to default
27

Unfortunately eager loading (Include) doesn't support any filtering or sorting of loaded child collections. There are three options to achieve what you want:

  • Multiple roundtrips to the database with explicite sorted loading. That's the first code snippet in your question. Be aware that multiple roundtrips are not necessarily bad and that Include and nested Include can lead to huge multiplication of transfered data between database and client.

  • Use eager loading with Include or Include(....Select(....)) and sort the data in memory after they are loaded:

    var model2 = DbContext.SitePages .Where(p => p.ParentId == null && p.Level == 1) .OrderBy(p => p.Order) .Include(p => p.Children.Select(c => c.Children)) .ToList(); foreach (var parent in model2) { parent.Children = parent.Children.OrderBy(c => c.Order).ToList(); foreach (var child in parent.Children) child.Children = child.Children.OrderBy(cc => cc.Order).ToList(); }

  • Use a projection:

    var model2 = DbContext.SitePages .Where(p => p.ParentId == null && p.Level == 1) .OrderBy(p => p.Order) .Select(p => new { Parent = p, Children = p.Children.OrderBy(c => c.Order) .Select(c => new { Child = c, Children = c.Children.OrderBy(cc => cc.Order) }) }) .ToList() // don't remove that! .Select(a => a.Parent) .ToList();

This is only a single roundtrip and works if you don't disable change tracking (don't use .AsNoTracking() in this query). All objects in this projection must be loaded into the context (the reason why the first ToList() is necessary) and the context will tie the navigation properties correctly together (which is a feature called "Relationship span").

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

OMG special thanks Slauma! Good done well job :D:D:D That works, I trace it and JUST ONE query sent against db (^_^) special thanks dear. I just changed the inner Select with .Select(c => new { Parent = c, Children = c.Children. OrderBy(cc => cc.Order) }). Thanks again and again again.
@ChrisMoschini: Are you sure that ProxyCreationEnabled matters? I almost never use change tracking proxies and relationship span still works.
@Slauma You're right! We have code that uses both Proxy disabled and no tracking for fast read-only queries that fails to do this kind of projection; but Proxy disabled is just fine, it's just the AsNoTracking() that throws a wrench in.
One other thing as long as I'm testing this though - ordering child lists seems to query much faster from SQL Server for long lists, than leaving order undefined, even if you're fine with unordered lists as the final outcome.
UPD info: since EF Core 5.0 - Filteing and Ordering are supported Filtered include
|
0

Have you try following?

... Select(from ch in c.children orderby ch.property desending select ch) ...
Improve this answer

1 Comment

Doesn't work! The error is The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.