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

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