C# LINQ to group by a list of objects in the format as shown below

Question

I have a list of data in this format -

User    Role        Code
John    admin       101
John    admin       101
John    admin       100
Smith   superadmin  150
Smith   superadmin  120
Smith   superadmin  130
Smith   superadmin  140
Smith   superadmin  160
John    review      180
John    review      190
John    review      200

I would like to collapse this based on the common user like this -

 User    Role        Code
 John    admin       100,101
 Smith   superadmin  120,130,140,150,160     
 John    review      180,190,200

I've tried this -

result = userRoles.GroupBy(l => l.User).Select(u => new UserRole()
{ 
    Code = string.Join(", ", userRoles.Where(d => d.User ==  u.Key)
                                      .Select(d => d.Code)
                                      .ToArray()),
    User = u.Key,
}).ToList();

I got it to a point where I am able to show user and code in the format with the above code , just trying to figure how to fit in the Role , any help would be greatly appreciated.

var userRoles = new[]
{
    new UserRole() { User = "John", Role = "admin", Code = "101" },
    new UserRole() { User = "John", Role = "admin", Code = "101" },
    new UserRole() { User = "John", Role = "admin", Code = "100" },
    new UserRole() { User = "Smith", Role = "superadmin", Code = "150" },
    new UserRole() { User = "Smith", Role = "superadmin", Code = "120" },
    new UserRole() { User = "Smith", Role = "superadmin", Code = "130" },
    new UserRole() { User = "Smith", Role = "superadmin", Code = "140" },
    new UserRole() { User = "Smith", Role = "superadmin", Code = "160" },
    new UserRole() { User = "John", Role = "review", Code = "180" },
    new UserRole() { User = "John", Role = "review", Code = "190" },
    new UserRole() { User = "John", Role = "review", Code = "200" },
};

public class UserRole
{
    public string User;
    public string Role;
    public string Code;
}

sorry, i didn't understand what you want to get, you need to group by user and role together? — Mehrdad, Feb 27 '20 at 20:02
I was able to get user and code in the format I want , just not able to figure out how to get Role as well — learner999, Feb 27 '20 at 20:04
@learner999 - When one answer has 8 upvotes and the other 0 then you might need to consider why that's the case. — Enigmativity, Feb 27 '20 at 22:10

score 9 · Answer 1 · edited Feb 27 '20 at 20:20

9

You can group by both user and role by creating an anonymous type. Something like this:

result = userRoles.GroupBy(l => new { l.User, l.Role }).Select(u => new
{
    Code = string.Join(", ", u.Select(d => d.Code).Distinct().OrderBy(d => d)),
    User = u.Key.User,
    Role = u.Key.Role
}).ToList();

edited Feb 27 '20 at 20:20

André Sanson

440
3
11

answered Feb 27 '20 at 20:06

Matt Burland

44,552
18
99
171

Mehrdad · Accepted Answer · 2020-02-28T01:18:59.840

0

to group by role and user together, so

        result = userRoles.GroupBy(l => new {l.User,l.Role} ).Select(u => new UserRole()
        { 
            Code = string.Join(", ", u.Select(d => d.Code).Distinct().OrderBy(d => d)),
            User = u.Key.User,
            Role = u.Key.Role
        }).ToList();

edited Feb 28 '20 at 01:18

answered Feb 27 '20 at 20:08

Mehrdad

1,523
9
23

You should skip or distinct duplicated `Code` values – Pavel Anikhouski Feb 27 '20 at 20:48
you mean remove first part? – Mehrdad Feb 27 '20 at 20:50
2

`u.Select(d => d.Code)` returns all `Code` values for a given group. `John` has code values 100, 101, 101. Your code print out all of them, but OP wants to have `John admin 100,101`. Also, I'm not sure, that your first snippet works correctly, it seems that it'll group `John` with role `admin` and `John` with role `review` into single group, which is not fully correct – Pavel Anikhouski Feb 27 '20 at 20:53
2

Also `string.Join` has an overload that takes a `IEnumerable` so you can drop the `ToArray` – juharr Feb 27 '20 at 20:55
Your use of `Role = u.First().Role` is killing the votes on your answer. You have assumed that a user can't belong to two or more roles. – Enigmativity Feb 27 '20 at 22:08

C# LINQ to group by a list of objects in the format as shown below

2 Answers2