score:1

Accepted answer

Since you are always going to be returning ALL of the items in the table, why not just make a recursive method that gets all children for a parent and then use that on the in-memory Items:

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

score:0

Well, if the hierarchy is strictly 2 levels you can always union them and let LINQ sort out the SQL (it ends up being a single trip though it needs to be seen how fast it will run on your volume of data):

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 an flat IEnumerable<{Item, Item}> list so if you want to group them you just follow on:

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 as we reached the capability of LINQ SQL provider with attempting to group a Union. If you try it against IQueryable it will run a basic Union for eligable parents then do a round-trip for every parent (which is what you want to avoid). Whether or not its ok for you to use regular LINQ for the grouping is up to you, same volume of data would have to come through the pipe either way.

EDIT: Alternatively you could build a view linking parent to all its children and use that view as a basis for tying Items. In theory this should allow you/L2S to group over it with a single trip.


Related Articles