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

2 posts, 0 answers
  1. IT
    IT avatar
    41 posts
    Member since:
    Jan 2010

    Posted 13 Sep 2010 Link to this post

    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

  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 15 Sep 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
Back to Top