Aggregate LINQ results

Question

If I have a Publisher table that has many Books, and each Book can have many Authors, how do I get a list of distinct Authors for a Publisher? In SQL you would just join all the tables, select the Author, and use SELECT DISTINCT. Using LINQ I end up with an IEnumerable(Of EntitySet(of Author)):

Dim temp = From p in Publishers Select (From b in p.Books Select b.Author)

and this still doesn't address duplicate Authors.

Is there a way to get a flat list of Authors, ungrouped from the Books, in a single query? I know I could loop through the sets and create one list and use Distinct on that. I was curious if it could be done in one statement.

Answer 1

Would something like this work:

In C#:

var publisherAuthors = Authors.Where(a => a.Books.Where(b => b.Publisher.Name.Equals("Some Publisher")));

Answer 2

If you are more comfortable with SQL than lambda expression, try:

from b in Books 
join ba in BookAuthorIndexes on b.BookId equals ba.BookId 
join a in Authors on ba.AuthorId equals a.AuthorId 
where b.Publisher.Name = "foo" 
select a distinct

I'm making some pretty big assumptions about your schema here, but something along those lines.

Answer 3

Here's the original query.

var authorSets = Publishers
  .Where(...)
  .Select(p => p.Books.Select(b => b.Author));

And here is the same query improved by SelectMany to flatten the hierarchy.

var authors = Publishers
  .Where(...)
  .SelectMany(p => p.Books.Select(b => b.Author))
  .Distinct();

For more information: MSDN