Entity Framework loading child collection with sort order

Question

I have two tables a parent and a child table. The child table has a column sortorder (a numeric value). Because of the missing support of the EF to persist a IList inclusive the sort order without exposing the sortorder (see: Entity Framework persisting child collection sort order) my child class has also a property SortOrder, so that i can store the children with the sort order.

In contrast to the author of the referenced question I try to load the children always sorted. So if I load a parent instance I expect, that the child collection is sorted by sort order. How can I achieve this behaviour with the Code First Fluent API and POCO's?

Hint: It's not an option to call .Sort(...) on the child collection.

Solution 1

You cannot achieve it directly because neither eager or lazy loading in EF supports ordering or filtering.

Your options are:

• Sort data in your application after you load them from database
• Execute separate query to load child records. Once you use separate query you can use OrderBy

The second option can be used with explicit loading:

var parent = context.Parents.First(...);
var entry = context.Entry(parent);
entry.Collection(e => e.Children)
     .Query()
     .OrderBy(c => c.SortOrder)
     .Load();

Solution 2

You can do this efficiently in a single query, the grammar is just awkward:

var groups = await db.Parents
    .Where(p => p.Id == id)
    .Select(p => new
        {
            P = p,
            C = p.Children.OrderBy(c => c.SortIndex)
        })
    .ToArrayAsync();

// Query/db interaction is over, now grab what we wanted from what was fetched

var model = groups
    .Select(g => g.P)
    .FirstOrDefault();

Explanation

async note

I happened to use the async extensions here, which you likely should be using, but you can get rid of await/async if you need a synchronous query without harming the efficient child sorting.

First chunk

By default all EF objects fetched from the Db are "tracked." In addition, EF's equivalent to SQL Select is designed around Anonymous Objects, which you see us selecting into above. When the Anonymous Object is created, the objects assigned to P and C are both tracked, meaning their relationships are noted and their state is maintained by the EF Change Tracker. Since C is a list of children in P, even though you didn't ask them to be related explicitly in your Anonymous Object, EF loads them as this child collection anyway, because of the relationship it sees in the schema.

To learn more, you can break the above into 2 separate queries, loading just the parent object, then just the child list, in completely different Db calls. The EF Change Tracker will notice and load the children into the parent object for you.

Second chunk

We've tricked EF into returning the ordered children. Now we grab just the Parent object - its children will still be attached in order just like we wanted.

Nulls and Tables as Sets

There's an awkward 2-step here mostly for best practices around nulls; it's there to do 2 things:

• Think of things in the db as sets until the absolute last moment possible.

• Avoid null exceptions.

In other words, the last chunk could've been:

var model = groups.First().P;

But if the object wasn't present in the db, that'll explode with a null reference exception. C# 6 will introduce another alternative though, the null property coalescence operator - so in the future you could replace the last chunk with:

var model = groups.FirstOrDefault()?.P;

Comments

[Eric]

don't forget to add: using statements for system.data.entity and system.linq

[ctb]

This worked for me! What a great hack! After several related questions, finally a good answer that works! X181 ought to mark this as the correct answer, IMO.

[Jo Smo]

I get this error: The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. All i changed in your code was i replaced FirstOrDefault() with ToList(). Any ideas what's going wrong?

[Chris Moschini]

@JoSmo Sounds like you've got a problem outside of this constrained sample. You most likely disposed your DbContext somewhere, possibly in another thread, and that caused this code to fail. Your question may be best as a new Question on StackOverflow rather than a discussion here in comments - post full code at least including any using statements and threading when you do.

[Jo Smo]

Ok. Thank you. Will do.

[vtortola]

I still don't understand how or why, but it works :) I have to paginate the child entity and this seems to be a solution.

[Chris Moschini]

@vtortola Added more explanation to try to explain what's going on here, hope it helps a bit.

[flux]

Is it possible to go another child level?

[Chris Moschini]

@flux That is likely a special enough case that it belongs as its own question. I guess I mostly worry about extending my answer ad infinitum by answering something not asked here - starts to drift outside the guidelines of the site.

[flux]

Found an answer that does that for anyone: stackoverflow.com/questions/7522784/…

[Kevin Brey]

Does anyone know if this works with EF7? It's not working for me and I'm guessing that is the reason.

[Joe H]

Also don't forget to null check the parent object

[Richard Collette]

In .NET 5 I get Collections in the final projection must be an 'IEnumerable<T>' type such as 'List<T>'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable<T>' or 'IOrderedEnumerable<T>' into an 'IEnumerable<T>'

[Jonas Benz]

10 years later... It seems your answer is still true for Entity Framework Core. This is really sad as NHibernate supports defining the sort oder in the mapping directly.