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 autor 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.

Answer 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();

Answer 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;

Answer 3

In addition to needing to order, I needed to limit the results of the children. I did it like this:

var transactions = await _context.Transaction
            .Include(x => x.User)
            .OrderByDescending(x => x.CreatedAt)
            .Where(x => x.User.Id == _tenantInfo.UserId)
            .Take(10)
            .ToListAsync();

var viewmodel = _mapper.Map<UserViewModel>(transactions.First().User);