LINQ to SQL, sorting by a related table

Question

I'm trying to order the values in a related table using LINQ to SQL.

I have 2 tables. Menu and MenuSection. They are related one to many on Menu.MenuId == MenuSection.MenuId

Currently, I'm pulling this information using the following query

var menus = from m in _context.Menus                        
                    select m;

This gets fed into an ASP.NET MVC page and works fine.

I'd like to be able to sort the data the column MenuSection.Order

I've tried doing this:

var menus = from m in _context.Menus 
                join ms in _context.MenuSections on m.MenuId equals ms.MenuId
                orderby ms.Order ascending select m;

But it's bringing back a set of data that is incorrect. It displays repeated Menu information.

EDIT: To clarify what i'm expecting the data shoud be:

There are x Menu's in Menu.

Each Menu has many MenuSection's

I'd like to list out each Menu and their related MenuSection. The MenuSections need to be in order based on MenuSection.Order

Menu 1
- MenuSection 1, Order = 1
- MenuSection 3, Order = 2
Menu 2
- MenuSection 4, Order = 1
- MenuSection 2, Order = 2

Answer 1

have you tried something like this? ...

var menus = (from m in _context.Menus
            join ms in _context.MenuSections on m.MenuId equals ms.MenuId
            orderby ms.Order ascending select m).Distinct();

or maybe this...

var menus = (from m in _context.Menus
            join ms in _context.MenuSections on m.MenuId equals ms.MenuId
            select new {m, ms.Order})
            .OrderBy(x => x.Order)
            .Select(x => x.m)
            .Distinct();

In that second case, you're projecting the sort column into a new type, sorting on it, then selecting just the original object, and distincting on that.

Answer 2

Round 3: That last revision to the question clarifies it a lot. It sounds like what you really need is a group-by. Those are harder to get right without the IntelliSense but I'll try my best:

var groupings =
 from m in _context.Menus
 orderby m.Foo
 from ms in m.MenuSections
 orderby ms.Order
 group ms by m;

foreach (var group in groupings)
{
 Menu menu = group.Key;
 // use the Menu

 foreach (MenuSection ms in group)
 {
  // use the MenuSection
 }
}

Now I added an extra orderby m.Foo where Foo is some property that I think you might want on Menu. Otherwise you aren't guaranteed to know the order of the top level menus.

Also note what I was saying earlier that the m.MenuSections is really a helper that is similar to from ms in _context.MenuSections where ms.MenuId == m.MenuId.

Answer 3

Just an FYI, I did manage to make this work by sorting as I performed the output.

My original intention was to have a presorted data set to work with.

Here is an example of what I meant:

var menus = from m in _context.Menus select m;

foreach (var menu in menus)
{
     Console.WriteLine(menu.Name);
     foreach (var menuSection in menu.MenuSections.OrderBy(o => o.Order))
     {
          Console.WriteLine("\t" + menuSection.Name + ", " + menuSection.Order);
     }
 }