Fetch from foreign key tables with LINQ and LINQ to SQL
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:
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 :)
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)
You can try using the DataLoadOptions.LoadWith method.
However, this scenario is not supported by Microsoft LINQ to SQL Server, only by the Devart implementations for Oracle, MySQL, PostgreSQL and SQLite.
Devart Team
http://www.devart.com/dotconnect
ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support