LINQ with Subquery/Group By/Join

Question

Newbie to LINQ, and trying to write the following query...

select 
  f.Section_ID, 
  f.Page_ID, 
  f.SortOrder, 
  f.Type
from 
(
  select 
    Section_ID, 
    min(SortOrder) as minSortOrder
  from 
    ContentPages 
  group by 
    Section_ID
) as x 
inner join 
  ContentPages as f on 
    f.Section_ID = x.Section_ID and 
    f.SortOrder = x.minSortOrder;

Notes:

• 'Section' has many 'ContentPages'
• Sections are ordered by a 'SortOrder' field
• ContentPages are also ordered by a 'SortOrder' field

Table: Section
Section_ID....Name.......SortOrder
....1.........One..........1......
....2.........Two..........3......
....3.........Three........2......

Table: ContentPage
Page_ID.......Section_ID.......Title..............SortOrder
....11.............1.......... Page One.............1......
....12.............1...........Page Two.............3......
....13.............2...........Page Three...........2......
....16.............2.......... Page Four............4......
....17.............2...........Page Eight...........5......
....18.............1...........Page Ten.............6......

The above query could possibly be written another way, so here's what I'm trying to do:

• I need to return a list of the first ContentPage within each Section (when sorted by ContentPage.SortOrder)
• Sort results by Section.SortOrder
• Show Section.Name (join on Section_ID?) in the result as well

Last 2 points are not covered by the sql query above and are more of a 'nice to have'...

Desired Result
Page_ID.......Section_ID...SectionName.....Title..............SortOrder
....11.............1.........One......... Page One.............1......
....13.............2.........Two..........Page Three...........2......

Any help is appreciated. Thanks!

Answer 1

Here's my first attempt at it:

from sectionPage in pages
group sectionPage by sectionPage.Section_ID into sectionGroup
join page in pages on sectionGroup.Key equals page.Section_ID
where page.SortOrder == sectionGroup.Min(p => p.SortOrder)
orderby page.SortOrder
select page;

What happens is first we create a group on the section id so that we can get the minimum sort order later. Next, we join a new reference to pages in on the section id, and filter by SortOrder being the minimum from the section group. Note, for simple expressions like the Min() call, I prefer the inline lambda expression over another query.

Finally, we add an orderby to order the pages, and we return the page (note you can change this to certain fields if you prefer).

Answer 2

I think this is what you're looking for...

    internal class Section
    {
        public int SectionId { get; set; }
        public string Name { get; set; }
        public int SortOrder { get; set; }
    }

    internal class ContentPage
    {
        public int PageId { get; set; }
        public int SectionId { get; set; }
        public string Title { get; set; }
        public int SortOrder { get; set; }
    }

    static void Main(string[] args)
    {
        List<Section> sections = new List<Section>();
        sections.Add(new Section() { SectionId = 1, Name = "One", SortOrder = 1 });
        sections.Add(new Section() { SectionId = 2, Name = "Two", SortOrder = 3 });
        sections.Add(new Section() { SectionId = 3, Name = "Three", SortOrder = 2 });

        List<ContentPage> contentPages = new List<ContentPage>();
        contentPages.Add(new ContentPage() { PageId = 11, SectionId = 1, Title = "Page One",   SortOrder = 1 });
        contentPages.Add(new ContentPage() { PageId = 12, SectionId = 1, Title = "Page Two",   SortOrder = 3 });
        contentPages.Add(new ContentPage() { PageId = 13, SectionId = 2, Title = "Page Three", SortOrder = 2 });
        contentPages.Add(new ContentPage() { PageId = 16, SectionId = 2, Title = "Page Four",  SortOrder = 4 });
        contentPages.Add(new ContentPage() { PageId = 17, SectionId = 2, Title = "Page Eight", SortOrder = 5 });
        contentPages.Add(new ContentPage() { PageId = 18, SectionId = 1, Title = "Page Ten",   SortOrder = 6 });

        var items = from section in sections
                     orderby section.SortOrder
                     join contentPage in
                         (from contentPage in contentPages
                          orderby contentPage.SortOrder
                          group contentPage by contentPage.SectionId into grp
                          select grp.FirstOrDefault())
                     on section.SectionId equals contentPage.SectionId
                     select new
                     {
                         PageId = contentPage.PageId,
                         SectionId = section.SectionId,
                         SectionName = section.Name,
                         Title = contentPage.Title,
                         SortOrder = section.SortOrder
                     };

        foreach (var newItem in items)
        {
            Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}", newItem.PageId, newItem.SectionId, newItem.SectionName, newItem.Title, newItem.SortOrder));
        }
    }

Note that the sample data you provided shows a sort order of 3 for section 2, but your sample results list its sort order as 2.