Join tables -> the right query RRS feed

  • Question

  • I have three tables: Author; Book and Author_Book with the relationships Author 1 -> n Author_Book  n -> 1 Book.

    I have one screen to list the books which is based on the Books query. Now in the same screen I need to have all the Author that are related with the select book.

    So for my preprocessQuery I pass the BookID

    Author_PreprocessQuery (int? _bookID, ref IQueryable<Author> query)

    How can I return the Authors that I intend to?

    I need:

    Select Author

    from Author

    inner join Author_Book on Author.Id=Author_Book.AuthorID

    WHERE Author_Book.BookID= _bookID


    Thanks and regards

    Monday, January 2, 2012 6:00 PM


  • Hi mwamoreira. You can use Any like so. This works even if a book has multiple authors.

            partial void AuthorsByBook_PreprocessQuery(int? pBookId, ref IQueryable<Author> query)
                if (pBookId.HasValue)
                    query = query.Where(a => a.BookAuthors.Any(ba=>ba.Book.Id==pBookId));

    Monday, January 2, 2012 6:26 PM