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

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.