Passing hierarchy table with Linq

Question

I have a table with two columns, GroupId and ParentId (both are GUIDS). The table forms a hierarchy, so I can look up the value in the "GroupId" file, when I find it, I can look at its ParentId. This ParentId will also appear in the GroupId of another entry. I can use this to approach the hierarchy tree from any point to the root (root is an empty GUID). I like it when I like the identifier GroupId. This will be a record with GroupId and all parents back to the root record. Is this possible with Linq, and if so, can someone provide a piece of code?

Answer 1

LINQ is not intended to handle recursive selection.

Of course, you can write your own extension method to compensate for this in LINQ for objects, but I found that LINQ to Entities does not like functionality that is not easily translated into SQL.

Edit: Oddly enough, LINQ to Entities is not complaining that Matt Warren will take recursion using LINQ here . You can do:

var result = db.Table.Where(item => item.GroupId == 5)
                     .Traverse(item => db.Table.Where(parent 
                                                       => item.ParentId == parent.GroupId));

using the extension method defined here:

static class LinqExtensions
{
    public static IEnumerable<T> Traverse<T>(this IEnumerable<T> source,
                                             Func<T,IEnumerable<T>> selector){
    foreach(T item in source){
        yield return item;
        IEnumerable<T> children = selector(item);
        foreach (T child in children.Traverse(selector))
        {
            yield return child;
        }
    }
}

Perhaps crowding out may be unsatisfactory.

Answer 2

This is definitely possible with Linq, but you will need to make a DB call for each level in the hierarchy. Not entirely optimal.

Answer 3

Other respondents are right - performance will be very poor because you will have to make multiple trips. However, this will somewhat depend on your specific case - your tree is deep and people will often perform this operation, for example.

You may be well provided for creating a stored procedure that does this (using CTE), and posting it to the object constructor in order to return a specific specific Entity.