How to group the linq query by two id's

Question

How can i use group by two id's; I have data like

empid: 1, usrid: 1, profid: 1
empid: 1, usrid: 1, profid: 2
empid: 2, usrid: 1, profid: 5

I have written query like

var query = (from i in data
         group i by new { i.empid, i.usrid} into g
         select new {                         
         profileid = g.Select(z=>z.profileid).ToArray()                         
         }).ToArray();

Now for the empid:1 and usrid:1 i want to get profile id as an array of 1,2 and for empid:2 and usrid:1 i want to get prof id as 5

But i am getting all the three values in each record like 1,2,5

How can i get that using linq

http://stackoverflow.com/questions/847066/group-by-multiple-columns — Mitch Wheat, Dec 21 '12 at 09:34
It must something in your data. There's nothing wrong with what you show. Maybe you can show a full running code snippet that displays the unexpected behaviour. — Gert Arnold, Dec 21 '12 at 18:18
@MitchWheat that Q&A shows how to group by multiple columns but that's not the problem here. — Gert Arnold, Dec 21 '12 at 18:20

score 1 · Accepted Answer · answered Dec 25 '12 at 16:06

I find it easier to see what is going on when I do it in steps.

First group the data

var grouped = data.GroupBy(d => new { d.empid, d.userid });

Then select the results

var results = grouped.Select(g => g.Select(d => d.profid).ToArray());

Each g is a group keyed by a pair of empid and userid. That group is a collection of instances your class. So the above statement says to make an array of the profids from each group (each empid userid pair). The results turn out to be

[1, 2]
[5]

That gets you the arrays you want, but you lose the keys that way. I don't know what you want to do with the results so that might be okay, but probably not. Either way the groups do contain the keys and the values you want.

Another way to approach the problem is to use a different overload of GroupBy to get just the profid as a value.

var grouped2 = data.GroupBy(d => new { d.empid, d.userid }, d => d.profid);

This will give you a group with a key being the pair of empid and userid and the collection being just the profids. This is more likely to be closer to what you want.

How to group the linq query by two id's

1 Answers1