Accepted answer

Assuming that you have a self-referential relationship for the table, you could do something like:

 var q = db.People
           .OrderBy( p => p.ParentID == null
                          ? p.Name
                          : p.Parent.Name + ":" + p.ID + ":" + p.Name );


You need a Common Table Expression (CTE) to do recursive SQL. CTEs are not supported by Linq to Sql. You can execute a query directly though.

This is what the SQL might look like although it does not group the children with their parents. I don't think you can do the grouping using CTEs:

WITH DirectReports (ID, Name, ParentID, Level)
    SELECT e.ID, e.Name, e.ParentID, 0 AS Level
    FROM Employee e
    WHERE e.ParentID IS NULL


    SELECT e.ID, e.Name, e.ParentID, Level + 1
    FROM Employee E
    JOIN DirectReports AS d
    ON e.ParentID = d.ID
FROM DirectReports

Related Articles