Ordering Entity Framework items and child items for MVC view

Question

How can I sort a query from a DbSet and include child items which should also be sorted.

Example:

I have a model for scheduling orders.

public class Order
{
  public virtual int Id { get; set; }
  public virtual int? SchedulingOrder { get; set; }
  public virtual int? WeekId { get; set; }
  public virtual Week Week { get; set; }
}
public class Week
{
  public virtual int Id { get; set; }
  public virtual DateTime StartDate { get; set; }
  public virtual ICollection<Order> Orders { get; set; }
}
...
public DbSet<Week> Weeks { get; set; }
public DbSet<Order> Orders { get; set; }

Then an action method

public ActionResult ShopSchedule()
{
  return View(db.Weeks.OrderBy(w => w.StartDate)
                 .Include(w => w.Orders.OrderBy(o => o.SchedulingOrder))
                 .ToList());
}

This doesn't work I think because of the nature of Include. Do I have to create a separate view model and map to it? Or is there some way to get around it right there in the query? There is some kind of syntax where people say new { left = right, etc } within the query?

related questions:
Ordering Entity Framework sub-items for EditorFor
C# Entity Framework 4.1 Lambda Include - only select specific included values

I don't think I can pass anonymous types to the View because the Html Helpers use lambda's. I'm just guessing there would be a problem with that but maybe I should check. — Benjamin, Jan 30 '12 at 19:11
Same question: http://stackoverflow.com/questions/8447384/how-to-order-child-collections-of-entities-in-ef?rq=1 Same question using LINQ-to-Entities (SQL-like) syntax: http://stackoverflow.com/questions/3981417/how-to-sort-inner-list-that-is-returned-by-entity-framework?rq=1 — Chris Moschini, Jan 24 '13 at 03:09
Also http://stackoverflow.com/questions/7522784/ef-4-1-code-first-how-to-order-navigation-properties-when-using-include-and-or/7528266#7528266 — Chris Moschini, Jul 14 '14 at 16:53

score 4 · Answer 1 · edited May 23 '17 at 12:01

It's worth noting that the other 2 solutions here pull the data via SQL, then reorder things in memory, which is very wasteful in terms of performance during both the query and the post-processing. This solution gets things in one go via SQL alone, without the extra in-memory step.

It can be done as described in the second approach here: How to order child collections of entities in EF

Like:

db.VendorProducts.Select(p =>
    new { Product = p, S = p.Schedules.OrderBy(s => s.From) })
    .FirstOrDefault(q => q.Product.Id == id).Product

So instead of an Include statement, you call up the related data in an anonymous object along with the original root data you were going to fetch, order it in that subquery and finally return the root data. The ordering remains intact. Twisted but it works.

To stick with your original code:

db.Weeks.Select(w => new { W = w, O = w.Orders.OrderBy(o => o.SchedulingOrder) })
    .OrderBy(q => q.W.StartDate).Select(q => q.W);

score 3 · Accepted Answer · answered Jan 30 '12 at 19:43

3

You are right, you can't use orders in Include, it's not meant to work that way. But you could sort the results within the view using the OrderBy on the Orders collection. Also, you're returning a result directly, shouldn't it be return View(db.Weeks...);

answered Jan 30 '12 at 19:43

Brian Mains

50,520
35
148
257

oops, yeah it should say `return View(etc)` I'll fix that and try sorting it in the view. Thanks. – Benjamin Jan 30 '12 at 20:26
Note that this isn't very performant - you're loading the items unordered, which can often be slower in SQL Server, then reordering them in memory - when you could be using the database for what it's good at by ordering them in SQL in the first place (see my answer). – Chris Moschini Feb 22 '13 at 20:36

score 1 · Answer 3 · answered Feb 04 '12 at 22:34

Something like this should work :

public ActionResult ShopSchedule()
{
  var vw = db.Weeks.OrderBy(w => w.StartDate)
                 .Include(w => w.Orders)
                 .ToList();
  vw.Orders = vw.Orders.OrderBy(o => o.SchedulingOrder).ToList()
  return view(vw);
}

Ordering Entity Framework items and child items for MVC view

3 Answers3