Fetch from foreign key tables with LINQ and LINQ to SQL

Question

Hi

I have an database table called MenuItem (MenuItemID, ParentMenuItemID, MenuItemName). I want to use this to populate a Menu control (probably the .NET Menu control).

Each MenuItem has a parent. If there is no parent, it is a top level menu item. I have made a LINQ to SQL class mapped to the MenuItem table. As my table has a foreign key to itself from ParentMenuItemID to MenuItemID, my auto generated MenuItem class contains a collection of MenuItem. This collection should contain all the children of a menu item.

The result should be stored in a List.

Is it possible in one query to fetch:

• all top level menu items
• all children to the top level menu items collected under their parent objects

It shouldn't be any problem to fetch the top level menu items and then iterate them and query for each child, or fetch all menu items and then put them inside the right parent. But if this could be done in a single query... I'd be happy :)

Answer 1

If you Linq to Sql model has a navigation property to itself then:

dataContext.MenuItem
.Where(m=>m.ParentMenuItemId==null)
.Select(m=> new {TopLevelItem = m,Children = m.Children})

Else you can replace m.Children with dataContext.MenuItem.Where(c=>c.ParentMenuItemId==m.MenuItemId)