Help with recursive linq expression

Question

I have a database table where all the records are interconnected, that is, something similar to the image below:

alt text http://img691.imageshack.us/img691/6772/38103866.png

As you can see in the diagram, the record can be root and can contain 1 or more children, where each child element saves its parent ID property in its ParentID. I was wondering if anyone could help me with building a LINQ expression that returns all nodes, starting from the last child and ending with the root. I mean the following. Starting with Node 4 (ID = 4) I need to switch to Node 2 (ID = 2), then Node 1, and then Node 0, thereby skipping Node 3. I hope that I'm clear enough, but if something needs something clarification let me know.

Answer 1

This will do its job:

static IEnumerable<Node> ListParents(IEnumerable<Node> list, int? ID)
{
    var current = list.Where(n => n.ID == ID).FirstOrDefault();
    if (current == null)
        return Enumerable.Empty<Node>();
    return Enumerable.Concat(new []{current}, ListParents(list, current.ParentID));
}

This assumes a class Node, for example:

class Node
{
    public int ID;
    public int? ParentID;
}

Please note that if your relationship ParentIDcauses a loop, this function will return endlessly.

Answer 2

I just found articles on this issue as I was looking for information about the Linq query. They do not fit me exactly, but I believe that they do what you want.

http://www.scip.be/index.php?Page=ArticlesNET18

and he updated this article at the end with a link to a new article: http://www.scip.be/index.php?Page=ArticlesNET23

He created extension methods to create a hierarchical collection from a falt table with self-sustaining parent columns.

Hope they help if you are still looking for answers.