Select all columns but group by only one in linq

Question

I have been looking for a way to get multiple columns but group by only one in SQL and I found some info. However I can not came up with a way to do it in linq.

I have the following toy example table:

| Id | Message | GroupId | Date |
|-------------------------------|
| 1  | Hello   | 1       | 1:00 |
| 2  | Hello   | 1       | 1:01 |
| 3  | Hey     | 2       | 2:00 |
| 4  | Dude    | 3       | 3:00 |
| 5  | Dude    | 3       | 3:01 |

And I would like to recover all columns for the rows that have a distinct GroupId as follows (with a 'Date' desc order):

| Id | Message | GroupId | Date |
|-------------------------------|
| 1  | Hello   | 1       | 1:00 |
| 3  | Hey     | 2       | 2:00 |
| 4  | Dude    | 3       | 3:00 |

I do not really care about which row is picked from the grouped ones (first, second...) as long as is the only one given that group Id.

I have came out with the following code so far but it does not do what is supposed to:

List<XXX> messages = <MyRep>.Get(<MyWhere>)
                            .GroupBy(x => x.GroupId)
                            .Select(grp => grp.OrderBy(x => x.Date))
                            .OrderBy(y => y.First().Date)
                            .SelectMany(y => y).ToList();

Solution

This will give you one item per group:

List<dynamic> data = new List<dynamic>
{
    new {ID  = 1, Message = "Hello", GroupId = 1, Date = DateTime.Now},
    new {ID  = 2, Message = "Hello", GroupId = 1, Date = DateTime.Now},
    new {ID  = 3, Message = "Hey",   GroupId = 2, Date = DateTime.Now},
    new {ID  = 4, Message = "Dude",  GroupId = 3, Date = DateTime.Now},
    new {ID  = 5, Message = "Dude",  GroupId = 3, Date = DateTime.Now},
};

var result = data.GroupBy(item => item.GroupId)
                 .Select(grouping => grouping.FirstOrDefault())
                 .OrderByDescending(item => item.Date)
                 .ToList();

//Or you can also do like this:
var result = data.GroupBy(item => item.GroupId)
                 .SelectMany(grouping => grouping.Take(1))
                 .OrderByDescending(item => item.Date)
                 .ToList();

If you want to control OrderBy then:

var result = data.GroupBy(item => item.GroupId)
                 .SelectMany(grouping => grouping.OrderBy(item => item.Date).Take(1))
                 .OrderByDescending(item => item.Date)
                 .ToList();

Comments

[Yacoub Massad]

Note that FirstOrDefault can be replaces with First since a group is guaranteed to have at least 1 item.

[Tekerson]

@Gilad Looks like I was overcomplicating the things.... You are not only solving my problem but also providing some understanding with these examples! Thank you

[Gilad Green]

@Tekerson - you are very welcome :) Maybe also look in the SO Documentation for linq - There are nice sections about the Take SelectMany, OrderBy - One can learn a lot from it :)

[RedBottleSanitizer]

Can you give an example for VB.Net please?