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.

See also: stackoverflow.com/questions/535436/…

Marc Gravell
– Marc Gravell

2009-12-29 19:38:24 +00:00
Commented Dec 29, 2009 at 19:38 — Marc Gravell
– Marc Gravell, Commented Dec 29, 2009 at 19:38

Community · Accepted Answer · 2017-05-23 12:24:23Z

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.

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 29, 2009 at 19:17

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Harvey Over a year ago

I see from the MSDN examples that this produces a list in the correct hierarchical order. Does that allow me to walk the list linearly to build my recursive structure, and is that why the performance is better?

Remus Rusanu Over a year ago

The CTE queries are the only way to produce the result set for 'I need all the childrens of current node, and the children of the children and so on' in SQL. Is not an issue of order (that is just a side effect of execution), but correctness. If you can afford to fetch the entire table in the client, then there is no need to use CTE, one can use just a an ordinary query then build the in memory tree. Also if you only need the children of a current node (eg. to instantiate the Childrens iterator) then again there is no need of CTEs, an ordinary SELECT ... WHERE Parent=@id will do.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related