Recursive Linq Grouping

Question

Scenario: I have a database table that stores the hierarchy of another many-to-many relationship table. An item can have multiple children and can also have more than one parent.

Items ------ ItemID (key) Hierarchy --------- MemberID (key) ParentItemID (fk) ChildItemID (fk) 

Example hierarchy:

 Level1 Level2 Level3 XA A1 A2 B B1 X1 YC 

I would like to group all the child nodes by each parent node in the hierarchy.

 Parent Child X A1 A2 B1 X1 A A1 A2 B B1 X1 YC 

• Note that there are no leaf nodes in the Parent column, and as in the Child column, only leaf nodes are contained.
• Ideally, I would like the results to be in the form IEnumerable < IGrouping< Item, Item → , where the key is the parent and the group elements are all Children.
• Ideally, I would like to get a solution that an entity provider can translate to T-SQL, but if this is not possible, I need to minimize the trip.
• I intend to summarize the values ​​that exist in another table merged on leaf nodes.

Answer 1

Since you will always return ALL elements in the table, why not just make a recursive method that takes all the children for the parent and then use it in the memory cells:

 partial class Items { public IEnumerable<Item> GetAllChildren() { //recursively or otherwise get all the children (using the Hierarchy navigation property?) } } 

then

 var items = from item in Items.ToList() group new { item.itemID, item.GetAllChildren() } by item.itemID; 

Sorry for any syntax errors ...

Answer 2

Well, if the hierarchy is strictly 2 levels, you can always combine them and let LINQ sort SQL (this ends as one trip, although you need to see how fast it will work on your data volume):

 var hlist = from h in Hierarchies select new {h.Parent, h.Child}; var slist = from h in Hierarchies join h2 in hlist on h.Parent equals h2.Child select new {h2.Parent, h.Child}; hlist = hlist.Union(slist); 

This gives you a flat list of IEnumerable<{Item, Item}> , so if you want to group them, you just follow it:

 var glist = from pc in hlist.AsEnumerable() group pc.Child by pc.Parent into g select new { Parent = g.Key, Children = g }; 

I used AsEnumerable() here when we reached the LINQ SQL provider capability with an attempt to group the Union. If you try it against IQueryable, it will launch a basic alliance for willing parents, then take a round-the-world trip for each parent (which you want to avoid). Regardless of whether you are suitable for using regular LINQ for grouping, you need the same amount of data to go through the channel anyway.

EDIT: Alternatively, you can create a view that binds the parent to all its children, and use this view as the basis for binding the elements. Theoretically, this should allow you / L 2S to group it with one trip.