How to write this in LINQ

Question

I have 3 tables in my database

Group [GroupId, GroupName]
GroupUser [GroupId, AuthorId]
Author [AuthorId, AuthorName]
Book [BookId, AuthorId, BookName]

How do I grab a list of books for a particular GroupId?

I have tried this LINQ statement:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<GroupUser>(p => p.User);
dlo.LoadWith<User>(p => p.Books);
context.LoadOptions = dlo;

List<Book> books = context.GroupUser
    .Where(p => p.GroupId == groupId)
    .Select(p => p.User.Books)
    .ToList();

However, this doesn't work because the compiler complains:

Cannot implicitly convert type System.Collections.Generic.List[System.Data.Linq.EntitySet[Book]] to System.Collections.Generic.List[Book]

As a last resort, I was thinking maybe just select the list of GroupUser, and then iterate through each of the child objects and then manually add it into my books variable, but to me, that just seems like extra work that LINQ should be doing.

Answer 1

Depending on the type of RideUser.Books (I'm assuming IList<Book>) you can simply change Select to SelectMany. Select will return IEnumerable<IList<Book>> whereas SelectMany will flatten the results into IEnumerable<Book> which you can then call ToList() on:

List<Book> books = context.GroupUser
                          .Where(p => p.GroupId == groupId)
                          .SelectMany(p => p.RideUser.Books)
                          .ToList();

Answer 2

If you want a filtered book query, write a filtered book query.

List<Book> books = context.Books
  .Where(b => b.Author.GroupUsers
     .Any(gu => gu.Group.GroupId == theGroupId))
  .ToList();