Order navigation properties when using Include and/or Select methods with EF 4.1 Code-First?

Question

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)?

Answer 1

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").