Retrieving a tree structure from a database using LINQ

Question

I have an organization chart tree structure stored in a database. Is 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 is the best way to retrieve these values from the database to fill up the C# Objects using LINQ (I am using Entity Framework)

There must be something better than making a call to get the top level then making repeated calls to get subs and so on.

How best to do it?

Answer 1

Hey,

An Entity Framework query should allow you to include related entity sets, though in a unary relationship, not sure how it would work...

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

Answer 2

Well... even with LINQ you will need two queries, because any single query will duplicate the main employee and thus will result in multiple employees (that are really the same) being created... However, you can hide this a bit with linq when you create the object, that's when you would execute 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()
        };

Answer 3

1. You can build a stored proc that has built in recursion. Take a look at http://msdn.microsoft.com/en-us/library/ms190766.aspx for more info on Common Table Expressions in SQL Server
2. You might want to find a different (better?) way to model your data. http://www.sqlteam.com/article/more-trees-hierarchies-in-sql lists a popular way of modeling hierarchical data in a database. Changing the modeling can allow you to create queries that can be expressed without recursion.

Answer 4

I'd add a field to the entity to include the parent ID, then I'd pull the whole table into memory leaving the List subs null. Id then iterate through the objects and populate the list using linq to objects. Only one DB query so should be reasonable.

Answer 5

If you're using SQL Server 2008, you could make use of the new HIERARCHYID feature.

Organizations have struggled in past with the representation of tree like structures in the databases, lot of joins lots of complex logic goes into the place, whether it is organization hierarchy or defining a BOM (Bill of Materials) where one finished product is dependent on another semi finished materials / kit items and these kit items are dependent on another semi finished items or raw materials.

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

So use the new functions available, and simply return the data you need without using LINQ. The drawback is you'll need to use UDF or stored procedures for anything beyond a simple root query:

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