How can I do a multi level parent-child sort using Linq?

Question

How can I do a multi-level parent-child sort using Linq if I have a table structure like the one below:

[Table: Sections]  
Id   Seq   Name        ParentSectionId  
1    1     TOP         NULL  
2    1     AAAA        1  
3    2     SSSS        1
4    3     DDDD        1  
5    1     SectionA1   2
6    2     SectionA2   2  
7    1     SectionS1   3  
8    3     ASummary    2  

Expected sort result:

TOP  
  AAAA  
    SectionA1  
    SectionA2  
    ASummary  
  SSSS  
    SectionS1  
  DDDD  

Answer 1

Performing a hierarchical search/sort with an adjacency list is not an easy thing to do, especially in Linq.

Rather than write a big block of code here to do this, I will refer you to somebody else who has already done it:

Linq AsHierarchy() Extension Method

This will convert the adjacency list into an actual tree structure, which is then easy to display/search/sort in a hierarchical fashion.

Answer 2

I think this will do it. It is untested, so let me know:

from section in db.Sections
group section by section.ParentSectionId into childGroup
orderby childGroup.Key
from childSection in childGroup.OrderBy(child => child.Seq)
select childSection

Answer 3

I bet

from section in db.Sections
where section.sec=0
orderby section.Name

would be enough and Linq will do the rest only you have to specify state LoadWith statement