Linq to group by 2 columns in C#

Question

I have a simple Linq query, which groups by one field Team:

var qbt = db.Calls.GroupBy(x => x.team).Select(call => new
        {
            Team = call.Key,
            Number=call.Count()
        });

Which returns:

Team  Number
ta    100 
tb    98 
tc    123

How do I change the query to have an additional column "status", so that it returns:

Team  Number Status
ta    40     Open
ta    60     Closed
tb    58     Open
tb    40     Closed
tc    1      Open
tc    122    Closed

I tried adding another group:

var qbt = db.Calls.GroupBy(x => x.team).GroupBy(y => y.status).Select(call => new
        {
            Team = call.Key,
            Status = call.Key2,
            Number=call.Count()
        });

... but that won't compile.

Thank you, Mark

score 11 · Answer 1 · answered May 28 '13 at 11:43

11

You can group on an anonymous type:

.GroupBy(x => new { x.team, x.status })

With the corresponding select:

.Select(call => new
    {
        Team = call.Key.team,
        Status = call.Key.status,
        Number = call.Count()
    });

answered May 28 '13 at 11:43

Andomar

232,371
49
380
404

Jarek · Accepted Answer · 2013-05-28T11:53:48.563

11

You need to create new anonymous type inside groupping, that should do the trick.

var qbt = db.Calls
    .GroupBy(x => new { Team = x.team, Status = x.status })
    .Select(call => new
    {
        Team = call.Key.Team,
        Status = call.Key.Status,
        Number=call.Count()
    });

edited May 28 '13 at 11:53

answered May 28 '13 at 11:44

Jarek

3,359
1
27
33

2

Your syntax is wrong. That should be `Team = call.Key.Team` and `Status = call.Key.Status` (`Key2` wont exist, and `Key` will be the anonymous type created in the groupby) – Jamiec May 28 '13 at 11:45
1

Right - didn't even noticed that part, as it was copy-pasted from question; though the most important part seems to be all right. Thanks. – Jarek May 28 '13 at 11:54

Linq to group by 2 columns in C#

2 Answers2

Linked