Linq to Sql - Hierarchical Query to Find Ancestors

Answer 1

First of all, you're welcome to use the hierarchical queries in my LINQ Extension Methods project. I think may help simplify your code.

The problem here is that this will create a database call for each node in the hierarchy. In the case of your example, you will have 5 round-trips to the database.

I'd go a different path and create a stored-procedure to do that for me and return the whole set of Employee objects. Since you're disconnecting the objects before returning them (disposing of the context), you could simply create new object from the stored procedure's result-set.

Answer 2

A simple solution that avoids loading the whole Employee table (but has a limited traversal depth) is...

var emps = dc.Employees.Where(e => (e.EmployeeId == EmployeeId) ||
                                   (e.SupervisorId == EmployeeId) ||
                                   (e.Supervisor.SupervisorId == EmployeeId) ||
                                   (e.Supervisor.Supervisor.SupervisorId == EmployeeId) ||
                                   ...);

Ultimately, you should use a common table expression to flatten the hierarchy, but LINQ to SQL doesn't currently support this. You could look into writing your own extension method (like the one in Omer's library but using IQueryable instead of IEnumerable to support server-side execution).