c# - Hierarchy Problem -> Replace Recursion with Linq Join?

Question

I have a self referential table, which has ID, ParentID (nullable).

So, the table contains many nodes, each node could be the root in the hierarchy (parent is null), or any level of the hierarchy (parent exists elsewhere in the table).

Given an arbitrary starting node, is there an elegant linq query that will return all children of the hierarchy from that node?

Thanks.

Answer 1

Here is a quick one I just wrote:

class MyTable
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public MyTable(int id, int? parentId) { this.Id = id; this.ParentId = parentId; }
}

List<MyTable> allTables = new List<MyTable> {
    new MyTable(0, null), 
    new MyTable(1, 0),
    new MyTable(2, 1)
};

Func<int, IEnumerable<MyTable>> f = null;
f = (id) =>
{
    IEnumerable<MyTable> table = allTables.Where(t => t.Id == id);

    if (allTables
        .Where(t => t.ParentId.HasValue && t.ParentId.Value == table
            .First().Id).Count() != 0)
        return table
            .Union(f(
            allTables.Where(t => t.ParentId.HasValue && t.ParentId.Value == table
                .First().Id).First().Id));
    else return table;

};

But I believe it is possible to do using SQL with a Union ALL.