Entity Framework loads a sortable child collection.

code-first ef-code-first entity-framework poco

Question

My tables consist of a parent table and a child table. There is a sortorder column in the kid table (a numeric value). My child class additionally contains a property SortOrder, so that I may save the children with the sort order, due to the EF's lack of support for persisting an IList that includes the sort order without exposing the sortorder (see: Sorting children's collections in Entity Framework).

I attempt to load the children always sorted, in contrast to the author of the cited query. Therefore, I anticipate that the child collection will be sorted by sort order if I load a parent instance. How can I implement this behavior using POCOs and the Code First Fluent API?

Hint: Calling is not an option. On the child collection, sort(...)

1
32
5/23/2017 11:54:55 AM

Accepted Answer

Because ordering or filtering is not supported by eager or slow loading in EF, you cannot directly accomplish it.

Here are your choices:

  • Immediately after you load data from a database, sort it in your application.
  • Run a different query to load the child entries. Following the usage of a single query, you mayOrderBy

With explicit loading, you may utilize the second choice:

var parent = context.Parents.First(...);
var entry = context.Entry(parent);
entry.Collection(e => e.Children)
     .Query()
     .OrderBy(c => c.SortOrder)
     .Load();
42
3/30/2012 8:27:15 AM

Popular Answer

This is easily accomplished with a single query; the language is merely 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

note in async

I accidentally used theasync extensions here that you probably need to be utilizing, but you may removeawait /async if a synchronous query is required without impairing the effective child sorting.

first part

All EF objects retrieved from the database by default are "tracked." Additionally, EF's SQL equivalentSelect is built on Anonymous Objects, which you can see us choosing from in the previous image. The objects associated to the Anonymous Object are generated whenP and C are both monitored, that is, the EF Change Tracker keeps track of their connections and their current states. SinceC a list of the kids inP In any case, EF loads them as this child collection. as a result of the connection it perceives in the schema, even if you didn't expressly request that they be associated in your Anonymous Object.

You may split the aforementioned query into two distinct ones to learn more, loading only the parent object and then just the child list in two separate database requests. The children will be detected by the EF Change Tracker and loaded into the parent object for you.

Second piece

EF was duped into returning the requested kids. We now just need to capture the Parent object; its children will still be connected in the desired sequence.

Tables as Sets and Nulls

Here, a cumbersome 2-step is used mostly for best practices regarding nulls and serves two purposes:

  • Right up to the very last second, consider everything in the database as a set.

  • Abstain from null exceptions.

In other words, the last section may have been:

var model = groups.First().P;

However, if the item wasn't in the database, a null reference exception will blow out. Therefore, in the future, you may change the last piece to:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow