Wednesday, January 11, 2012

Less Than Simple Group By LINQ Queries

Lately I have been going through LINQ. Most of it has been review, however I ran across something that I really didn’t understand. That area: Grouping.

Suppose I wanted to write a LINQ query which would do the following:

SELECT a.city, COUNT(*) AS Count
FROM authors a
GROUP BY a.city
I can easily render this into LINQ as follows (LINQPad C# Expression):
from a in Authors 
group a by a.City into c
select new
{
City = c.Key,
Count = c.Count()
}

When things got just a little bit more complicated, I got lost. Suppose that I wanted to render this query:

SELECT a.au_lname, a.au_fname, a.phone, COUNT(*) AS Count
FROM authors AS a
GROUP BY a.au_lname, a.au_fname, a.phone

I figured out that the Key didn’t need to be a simple type. The type of the Key is determined by the type between by and into in the group clause. So here I create an anonymous type for the key and refer to the properties of Key in the select clause. So I can render it as follows (LINQPad C# Expression):

from a in Authors 
group a by new
{
a.Au_lname,
a.Au_fname,
a.Phone
}
into c
select new
{
Au_lname = c.Key.Au_lname,
Au_fname = c.Key.Au_fname,
Phone = c.Key.Phone,
Count = c.Count()
}

GroupBy() (the extension method that group .. by is translated into) returns an IQueryable of IGrouping(s), a IGrouping is an IEnumerable with a generic Key property tacked on. Since Key is generic, I can pass in a complex type.

NOTE: I haven’t been able to get it to work with a non-anonymous type In LINQ to SQL.

I didn’t use LINQ grouping in my production code. I don’t know if it is because I didn’t understand it or just didn’t need it. If I really needed it I would think I would have pushed harder and learned it earlier…