Trumpster Trumpster - 2 months ago 6
SQL Question

How to group by multiple fields and get the count of another field

So let's say I have a simple class like

class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}


and I have an
IQueryable<Person>
called
persons
. What I'm trying to do is the equivalent of

SELECT
FirstName,
LastName,
COUNT(Age) As AgeCount
FROM dbo.Persons
GROUP BY FirstName, LastName


if we were in SQL land. How do I write this in LINQ?

I've looked at posts like linq with groupby and count and none of them are exactly like my scenario.

Answer

You can group by an anonymous object, then project each group to select the desired properties:

var result = persons
    .GroupBy(p => new { p.FirstName, p.LastName })
    .Select(g => new 
    {
        FirstName = g.Key.FirstName,
        LastName = g.Key.LastName,
        AgeCount = g.Count()
    });