LINQ Grouping help

Question

I have a database table that holds parent and child records much like a Categories table. The ParentID field of this table holds the ID of that record's parent record...

My table columns are: SectionID, Title, Number, ParentID, Active

I only plan to allow my parent to child relationship go two levels deep. So I have a section and a sub section and that it.

I need to output this data into my MVC view page in an outline fashion like so...

1. Section 1
   • Sub-Section 1 of 1
   • Sub-Section 2 of 1
   • Sub-Section 3 of 1
2. Section 2
   • Sub-Section 1 of 2
   • Sub-Section 2 of 2
   • Sub-Section 3 of 2
3. Section 3

I am using Entity Framework 4.0 and MVC 2.0 and have never tried something like this with LINQ. I have a FK set up on the section table mapping the ParentID back to the SectionID hoping EF would create a complex "Section" type with the Sub-Sections as a property of type list of Sections but maybe I did not set things up correctly.

So I am guessing I can still get the end result using a LINQ query. Can someone point me to some sample code that could provide a solution or possibly a hint in the right direction?

Update:

I was able to straighten out my EDMX so that I can get the sub-sections for each section as a property of type list, but now I realize I need to sort the related entities.

var sections = from section in dataContext.Sections
                       where section.Active == true && section.ParentID == 0
                       orderby section.Number
                       select new Section
                       { 
                        SectionID = section.SectionID,
                        Title = section.Title,
                        Number = section.Number,
                        ParentID = section.ParentID,
                        Timestamp = section.Timestamp,
                        Active = section.Active,
                        Children = section.Children.OrderBy(c => c.Number)
                       };

produces the following error. Cannot implicitly convert type 'System.Linq.IOrderedEnumerable' to 'System.Data.Objects.DataClasses.EntityCollection'

Answer 1

Your model has two navigation properties Sections1 and Section1. Rename the first one to Children and the second one to Parent.

Depending on whether you have a root Section or perhaps have each top-level section parented to itself (or instead make parent nullable?), your query might look something like:-

  // assume top sections are ones where parent == self
  var topSections = context.Sections.Where(section => section.ParentId == SectionId);

  // now put them in order (might have multiple orderings depending on input, pick one)
  topSections = topSections.OrderBy(section => section.Title);

  // now get the children in order using an anonymous type for the projection
  var result = topSections.Select(section => new {top = section, children = section.Children.OrderBy(child => child.Title)});

Answer 2

For some linq examples:

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

This covers pretty much all of the linq operations, have a look in particular at GroupBy. The key is to understand the input and output of each piece in order to orchestrate several in series and there is no shortcut but to learn what they do so you know what's at hand. Linq expressions are just combinations of these operations with some syntactic sugar.