How to get a full node path with LINQ to SQL?

Question

Hello, I have a basic hierarchy defined in an SQL table:

Id    ParentId  Name
----------------------
1     Null      Root
2     1         Node1
3     2         Node2

Is there a good LINQ-ish way to get the full path of a node? For example, i want to pass the Id of Node2 and get the full path like Root/Node1/Node2.

Thank you in advance :)

Answer 1

I can't think of a way to do this in a single query for an arbitrary length path. I'd probably resort to using a loop, building the path as you go, until you reach a node with no parent.

 var node = db.Nodes.Where( n => n.Name == "Node2" ).SingleOrDefault();
 string path = string.Empty;
 while (node != null)
 {
     path = string.Format( "/{0}{1}", node.Name, path );
     node = db.Nodes.Where( n => n.ParentId == node.Id ).SingleOrDefault();
 }