getting nested results from linq to sql

Question

my data looks like this in the table:

ID     Name     Parent ID
---    ----     ---------
1      Mike     null
2      Steve    1
3      George   null
4      Jim      1

I can't figure out how to write a linq to sql query that will return the results with the parent rows grouped with their child rows. So for example this is the result I want:

1 Mike (no parent)
   2 Steve (Parent is 1)
   4 Jim (Parent is 1)
3 George (no parent)

The way I'm doing it right now is to first grab a result set of all the parent rows. Then I loop through it and find the children for each parent and insert all this into a List<> as I loop. At the end the List<> has everything in the order I want it.

But is there a way to do this in just one linq query?

Answer 1

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)
AS
(
    SELECT e.ID, e.Name, e.ParentID, 0 AS Level
    FROM Employee e
    WHERE e.ParentID IS NULL

    UNION ALL

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

Answer 2

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 );