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…