Help with recursive linq expression

I have a database table where all the records are linked among each other i.e. something similar to the image bellow:

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

As you can see on the diagram, a record can be root and can have 1 or more children, where each child stores in its ParentID pr开发者_运维问答operty the ID property of its parent. I was wondering if someone can help me with constructing a LINQ expression that returns all the nodes starting with the last child and finishing with the root. What I mean is the following. Starting from Node 4 (ID = 4) I have to move up to Node 2 (ID = 2), then Node 1 and then Node 0, thus skipping Node 3. I hope I am clear enough, but if something needs clarifying let me know.

This'll get the job done:

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 is assuming a Node class like:

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

Note that if your ParentID relationships cause a cycle, this function will recurse infinitely.

I just found articles on this issue as I was looking for some Linq query information. These dont fit what I need exactly but I believe they do what you want.

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

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

He created Extension methods to create a hierarchial collection from a falt table with self refencing parent columns.

Hope they help, if you are still looking for answers.