Linq to SQL join then order by

Question

I have the following two classes:

book_page

• chapter_title
• order within chapter
• (other fields)

chapter

• chapter_title
• order

I have a collection of book_pages with their chapter titles. I want to be able to get the book_page collection sorted by the order that their "chapter_title" comes up in the "chapters" collection and then sort by the "order within chapter" field.

I tried writing a join on the chapter_title column then ordering by chapter.order, then by page.order_within_page, but no luck. Any suggestions?

My Code:

var ordered_pages= from chapter in chapters
                        join page in book_pages
                        on chapter.chapter_title equals page.chapter_title
                        select new{order = chapter.order,page = page}

var finalList = from row in ordered_pages.OrderBy(c => c.order).ThenBy(p =>p.page.order_within_chapter)
                select row.page;

This doesn't really work and is ugly even if it did. Am I missing some way of using LINQ to do this?

Answer 1

I beleive this is what it should look like.

var ordered_pages =
        from chapter in chapters
        join page in book_pages
            on chapter.chapter_title equals page.chapter_title
        orderby page.order_within_chapter
        orderby chapter.order
        select page;

the page.order_within_chapter will order first, then the chapter.order will run.

Answer 2

var a = db.Chapters;
var b = db.Pages
.Select(p => new { page = p, chapter = a.FirstOrDefault(q => q.Title == p.Caption) })
.OrderBy(p=>p.chapter.order).ThenBy(p=>p.page.order).Select(p=>p.page).ToList();

Or

var b=db.Pages.Join(a, p => p.Caption, p => p.Title, 
(p, q) => new { page = p, chapter = q }).OrderBy(p => p.page.Code)
.ThenBy(p => p.chapter.t1).Select(p=>p.page).ToList();