CODEWITHSUNDEEP
c#entity-frameworksql-order-by
Ryan O'Neill
Ryan O'Neill

Reputation: 5687

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 abstracted 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.

Upvotes: 2

Views: 1432

Answers (2)

Yet Another Geek
Yet Another Geek

Reputation: 4289

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

Upvotes: 1

The Evil Greebo
The Evil Greebo

Reputation: 7138

I THINK this is what you want to do.

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

Upvotes: 3

Related Questions