LINQ group by on a single column

Question

I have a database table that looks like this:

ID       USERID       DATE            NAME
1        1            01-01-2001      aaaa
1        2            01-02-2001      aaaa
1        3            01-03-2001      aaaa
2        5            02-02-2002      bbbb
2        6            02-02-2002      bbbb
2        7            02-02-2002      bbbb

So I want to group everything by ID, so it has multiple USERIDs for each ID. However DATE and NAME are all the same. So how do get this as a linq query?

So far I thought of doing this, and this works for the users

from table in context.table
group table.USERID by table.ID into grp
orderby grp.Key descending
select new
{
    ID = grp.Key,
    Users = grp.ToList()
}

But this does not work if I add other items like DATE because DATE is sometimes different. However if it would select the first date that would be just fine.

EDIT what I would like as result is:

ID       Users        DATE            NAME
1        1,2,3        01-01-2001      aaaa
2        5,6,7        02-02-2002      bbbb

Answer 1

You group by multiple columns:

from table in context.table
group new{table.UserID,table.Date} by new{table.ID,table.Name} into grp
orderby grp.Key.ID descending
select new
{
    ID = grp.Key.ID,
    Date=grp.FirstOrDefault().Date,
    Name=grp.Key.Name,
    Users = grp.Select(e=>e.UserID).ToList()
}