Ordering by the many side of an entity framework join

I'm trying to accomplish the following using Entity Framework 4.1 in .Net 4;

var context = new MyEntities();

var results = context.Diners
    .OrderBy(i => i.MenuItems.Course.DisplayOrder)
    .ThenBy(i => i.MenuItems.DisplayOrder);

The issue here is that MenuItems is an ObjectSet (collection) and a collection does not have a Course (another joined table). A MenuItem (singular) has a course though. Hope it is obvious.

The following is the SQL I would use to do this;

Select
    *
From
    Diner As D
Inner Join
    DinerSelection As DS -- This is the Many to Many that EF has abst开发者_JS百科racted away.
On  D.DinerId = DS.DinerId
Inner Join
    MenuItem As MI
On  DS.MenuItemId = MI.MenuItemId
Inner Join
    Course As C
On  MI.CourseId = C.CourseId
Order By
    C.DisplayOrder, MI.DisplayOrder

If it is not an easy thing to do in EF then I'll just do it as a view, although I'd rather not.

I THINK this is what you want to do.

.OrderBy(i => i.MenuItems.SelectMany(c=>c.Course).DisplayOrder)

If you want to translate your SQL query directly into LINQ you also have to do the joins there using the Enumerable.Join method.

e.g. Your first join

ctx.Diners.Join(ctx.DinerSelection, d => d.DinerId, ds => ds.DinerId, (d, ds) =>
   //Further logic here with the joined elements
);