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?

tvanfosson · Accepted Answer · 2009-03-03 15:18:22Z

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

answered Mar 3, 2009 at 15:18

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jakob Christensen Over a year ago

This breaks if you have more than two levels.

tvanfosson Over a year ago

I didn't claim that it solved every problem in this space, just this one.

codette Over a year ago

i wish there was a way for me to assign "assisted" answers or something like that. but for my specific needs this is the better answer.

Jakob Christensen · Accepted Answer · 2009-03-03 15:33:23Z

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

edited Mar 3, 2009 at 15:33

answered Mar 3, 2009 at 15:07

Jakob Christensen

15k2 gold badges53 silver badges82 bronze badges

1 Comment

codette Over a year ago

Could you please show me how that CTE would look? By your comment I am assuming it's used in a direct query

Collectives™ on Stack Overflow

getting nested results from linq to sql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related