3

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.

Community
  • 1
  • 1
Trumpster
  • 75
  • 1
  • 4
  • 1
    I think you misinterpret the SQL `COUNT` function. If `Age` is non nullable field, `COUNT(Age)` is the same as `COUNT(1)` or `COUNT(*)`. If you had in mind `COUNT(DISTINCT Age)`, that's a totally different story. – Ivan Stoev Sep 20 '16 at 19:33

2 Answers2

8

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()
    });
Arturo Menchaca
  • 15,783
  • 1
  • 29
  • 53
1

It's should be somethilg like this:

persons.GroupBy(p => new { p.FirstName, p.LastName })
.Select(p => new { 
p.Key.FirstName, 
p.Key.LastName, 
AgeCount = p.Where(p1 => p1.Age != null).Select(p1 => p1.Age).Count() });
steryd
  • 363
  • 3
  • 9