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 Solution

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 Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

Best Solution

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – LINQ query on a DataTable

Related Question