LINQ queries for many-to-many with more than two tables involved

[IT]

I have forward mapped class in my application with a similar setup to the following:-

 

Class Book

-          IList<Chapter> chapters

-          IList<Change> changes

 

Class Chapter

-          Book book

-          IList<Page> pages

-          IList<Change> changes

 

Class Page

-          Chapter chapter

-          IList<Change> changes

 

Class Change

-          String description

 

To summarise the relationships:-

-          A book has zero or more chapters

-          A chapter has zero or more pages

-          A book, chapter or page may have zero or more changes (the relationship between the change class and each of the other classes is many-to-many and uses join tables)

 

Given a book, I want to be able to list all the *page level* changes (flattened)

 

If “change”s were specific to pages, I could work from the bottom up… e.g. Change.Where(c => c.Page…), but they aren’t, so I need to work from the top down.

 

I considered something like this:-

 

var result = from c in Chapter.GetList()

             join p in Page.GetList() on p.Chapter equals c

             join chg in Changes.GetList() on chg ???

             where c.BookID == 1

             select chg;

 

But get lost at the ??? part… what I’d like to say here is something like “join chg in Changes.GetList() on chg in p.Changes” or use a “where p.Changes.Contains(chg)” somewhere, but nothing seems to work.

 

I’ve found a lot of info on many-to-many when two tables (plus the join table), but not on 3+

 

In SQL I would do something similar:-

 

      SELECT

            Change.*

      FROM Book

      LEFT JOIN Chapter ON Chapter.BookID = Book.ID

      LEFT JOIN Page ON Page.ChapterID = Chapter.ID

      LEFT JOIN Change_Page ON Change_Page.PageID = Page.ID

      LEFT JOIN Change ON Change.ID = Change_Page.ChangeID

      WHERE Book.ID = 1

 

Can someone suggest the best way of accomplishing this?

 

 

Best regards,

Aleks

[Serge]

Hello Aleks,

 Let me see if I understand correctly. For a specific book, say with ID that equals 1 you need the to get all the page level changes. So if a book A has 2 chapters each with 3 pages you will only need the changes that are associated with the 6 page objects. If so, you can easily achieve it with a query such as this : 

var query = scope.Extent<Page>().Where(x => x.Chapter.Book.ID == 1)
                       .SelectMany(x => x.Changes)

Please contact us back if you face further trouble or this is not your case. I do hope this helps.

Have a great day,
Serge
the Telerik team