Fill a Recursive Data Structure from a Self-Referential Database Table

Question

This question refers to http://www.matthidinger.com/archive/2009/02/08/asp.net-mvc-recursive-treeview-helper.aspx

Let's say I have a table that looks like this:

And I have a recursive data structure that looks like this:

public class TreeNode
{
    public TreeNode(){}
    public string NodeId { get; set; }
    public string ParentId { get; set; }
    public string Name { get; set; }
    public IEnumerable<TreeNode> Children { get; }
}

How do I fill this recursive data structure from the table using Linq?

NOTE: For purposes of this question, please assume that I already have a perfectly efficient table; i.e. it is completely resident in memory, or it is being access using a CTE. Really, I am just looking for the Linq queries to get it from the Linq to SQL DataContext to the recursive object. I am aware it will probably involve a ForEach and a recursive function call; I just can't quite get my head around it.

Thanks in advance.

Answer 1

I think your best option is to query the hierachy in SQL using CTEs. LINQ2SQL and hierachical/relational data don't mix too nicely. See Hierarchical data in Linq - options and performance.