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

Answer 1

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

Answer 2

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

Answer 3

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);
}