Create lists for separate columns after grouping with GroupBy

Question

Referring to this question - Using Linq to group a list of objects into a new grouped list of list of objects.

Here is the input:

List<User> userList = new List<User>();
userList.Add( new User { UserID = 1, UserName = "UserOne", GroupID = 1 } );
userList.Add( new User { UserID = 2, UserName = "UserTwo", GroupID = 1 } );
userList.Add( new User { UserID = 3, UserName = "UserThree", GroupID = 2 } );
userList.Add( new User { UserID = 4, UserName = "UserFour", GroupID = 1 } );
userList.Add( new User { UserID = 5, UserName = "UserFive", GroupID = 3 } );
userList.Add( new User { UserID = 6, UserName = "UserSix", GroupID = 3 } );

I want to have the result to be in this format :

            GroupedUserList
  GroupID    UserIdList     UsersList
    1 ,        [1,2,4],     ["User1", "UserTwo", "UserFour"]
    2 ,          [3],       ["UserThree"]
    3 ,         [5,6],      ["UserFive", "UserSix"]

What would be the linq query?

Ehsan Sajjad · Answer 1 · 2018-02-08T09:06:51.727

You can do the group on the GroupID and then project the resultant group this way:

var result = userList.GroupBy(user=>user.GroupID)
                     .Select(group=> new 
                              {
                                GroupID = group.Key,
                                UserIDs = String.Join(",", group.Select(x=>x.UserID)),
                                UserNames = String.Join(",", group.Select(x=>x.UserName))
                              });

The result would contain each GroupID with UserID and UserName as comma seperated string.

EDIT:

If you need List instead of comma seperated values, then just removed the String.Join call and materialize the result using ToList():

var result = userList.GroupBy(user=>user.GroupID)
                     .Select(group=> new 
                              {
                                GroupID = group.Key,
                                UserIDs = group.Select(x=>x.UserID).ToList(),
                                UserNames = group.Select(x=>x.UserName).ToList()
                              });

Can't it be converted into list directly instead of comma separated string? — Nameless, Feb 08 '18 at 04:14

score 1 · Answer 2 · answered Feb 07 '18 at 14:49

1

Are you sure you don't want each tuple of {UserId, UserName} together?

GroupId     {UserId, UserName}
   1        {1, "User1}, {2, "UserTwo"}, {4, "UserFour"},
   2        {3, "UserThree"}
  ...

Well if you sure you don't want that, you need a select after the GroupBy:

var result = userList.GroupBy(user => user.UserId)
    .Select(group => new
    {
        UserIdList = group.Select(user => user.UserId).ToList(),
        UsersList = group.Select(user => user.UserName).ToList(),
    });

answered Feb 07 '18 at 14:49

Harald Coppoolse

28,834
7
67
116

I want it to be separate lists.. And you are grouping bu UserId.. is it typo? – Nameless Feb 08 '18 at 04:15
No, because in your "I want to have the result in this format" you clearly showed one table where each row has a GroupId, a UserIdList and a UsersList. If that is not what you want, state your requirements better next time – Harald Coppoolse Feb 08 '18 at 07:30
Hey! Sorry about that :) What I meant was UserId and UserName are to be in a separate list :) – Nameless Feb 08 '18 at 12:56
1

The result of one linq statement is exactly one (possibly empty) sequence, not two, not zero, exactly one. I'm sure you're smart enough to convert this one result into two lists – Harald Coppoolse Feb 09 '18 at 07:31

Create lists for separate columns after grouping with GroupBy

2 Answers2