Sql – Aggregate LINQ results

asp.netlinqlinq-to-sql

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.

Best Answer

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

Related Topic