Getting a tree structure from a database using LINQ

Question

I have a tree structure of an organization chart stored in a database. This is something like

ID (int); Name (String); ParentID (int) 

In C #, it is represented by a class like

 class Employee { int ID, string Name, IList < Employee> Subs } 

I am wondering how best to get these values ​​from a database in order to populate C # objects using LINQ (I use Entity Framework)

There must be something better than making a call to get the top level, and then repeat the calls to get subtitles, etc.

What is the best way to do this?

Answer 1

I would add a field to the object to include the parent id, then I pulled the entire table into memory, leaving the Descending Lists down. The identifier then iterates over the objects and populates the list using linq for the objects. Only one database query should be reasonable.

Answer 2

• You can create a stored process built in recursion. See http://msdn.microsoft.com/en-us/library/ms190766.aspx for more information on common table expressions in SQL Server
• You might want to find another (better?) Way to model your data. http://www.sqlteam.com/article/more-trees-hierarchies-in-sql lists a popular way to model hierarchical data in a database. Modified modeling allows you to create queries that can be expressed without recursion.

Answer 3

If you are using SQL Server 2008, you can use the new HIERARCHYID function .

Organizations have struggled in the past with representing a tree like a structure in databases, joining a lot of complex place logic a lot, be it organizing a hierarchy or defining a specification (Bill Materials), where one finished product depends on another semi-finished material / set of items and a set of elements depend on other semi finished products or raw materials.

SQL Server 2008 has a solution to the problem when we store the entire hierarchy in the Hierarchyid` data type. The hierarchy ID is the variable length of the system data. hierarchyid` in is used to locate a hierarchy element like Scott CEO and Mark, as well as Ravi reports to Scott and Ben and Laura a report to Mark, Vijay, James and Frank to inform Ravi.

So, use the new features available and just return the data you need without using LINQ. The downside is that you need to use UDF or stored procedures for anything other than a simple root query:

 SELECT @Manager = CAST('/1/' AS hierarchyid) SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL) 

Answer 4

An Entity Framework query should allow you to include related entity sets, although in a unary relationship, I'm not sure how this works ...

Check this out for more information: http://msdn.microsoft.com/en-us/library/bb896272.aspx

Answer 5

Well ... even with LINQ you will need two queries, because any single query will duplicate the main employee and, thus, will lead to the creation of several employees (which are really the same) ... However, you can hide this bit with linq when creating the object that when executing the second query, something like this:

 var v = from u in TblUsers select new { SupervisorName = u.DisplayName, Subs = (from sub in TblUsers where sub.SupervisorID.Value==u.UserID select sub.DisplayName).ToList() };