Search by result of group join

I make LINQ query for the Books sample database: http://www.codeproject.com/KB/linq/linqtutorial.aspx Sorry for external link, but I don't know how to provide database structure in the question. Currently I have this query:

var result = from book 开发者_运维百科in dataContext.Books
    join book_author in dataContext.BookAuthors
    on book.Id equals book_author.Book
    into book_authors
    join category in dataContext.BookCategories
    on book.Category equals category.Id
    select new
    {
        Book = book.Id,
        Title = book.Title,
        Category = book.Category,
        CategoryName = category.Name,
        BOOK_Authors = book_authors
        // , Author_Name = ???
};

This query result has BOOK_Authors subsequence: int Book, int Author. Authors database table (int Id, varchar Name) is not used in the query. I want to add author name for every member of BOOK_Authors subsequence condition: Author = Id. For example:

BOOK_Authors.Author = 1  ----- take Name form the Authors table by Id = 1
BOOK_Authors.Author = 2  ----- take Name form the Authors table by Id = 2
...

Is this possible to do with single LINQ request?

I see the problem, it's almost like you want another join, but without the multiplicity of adding another join. You may be able to achieve this with a subselect on the new book instance, for example (be warned this might not actually work as I haven't tried this with the book_authors join before...):

BOOK_Authors = dataContext.Authors.Where(a => book_authors.Contains(a.Author))

However due to the predetermined schema LINQ has set up for you, it'd be far easier to use that hierarchy and write:

var results = dataContext.Books;

foreach(var book in results) {
    foreach(var authorLink in book.BookAuthors) {
        // the author is here in:
        // authorLink.Authors.Name
    }
}
// This is the same list for the first book only
var allBookAuthors = results.First().BookAuthors.Select(a => a.Author);

Hope this helps, Tom

EDIT

After having a play with the demo project, I changed BookAuthors to being a public class, then added a BookAuthors Table reference into the BookCatalog like so:

public Table<BookAuthor> BookAuthors;

Then I removed all the Category code as that didn't compile (perhaps my example code is newer? Also category is linked via book.Category) and used BOOK_Authors = book_authors.Select(a => a.Author) and it returned all the appropriate authors. Because of the nature of join table, this work cannot easily be done in the standard from way. There probably is a way using group by and some outer join trickery, but I don't see it necessary to force this when the Entities used have their appropriate joins linked automatically through their schema.

Solution using Microsoft Entity Framework is surprisingly simple. Database wrapper classes generated by Entity Framework Wizard contain navigation fields, which allow to access all related fields without writing join queries. So, in Entity Framework project the query is:

var result = from book in dataContext.Books orderby book.Title select book;

All related fields, like category name, authors collection and each author name, are accessed using navigation fields.