Getting last records of every distinct value of a column

user_6166703 0
Viewed 324

I have a message table like this:

Id     Message     Column1
--------------------------
1      asdasdd     1
2      dsfsfsf     1
3      safsafs     2
4      afewfff     3
5      adw qwd     2

I want to get last records of every distinct value on Column1, which means rows 2,4 and 5 via this code:

Messages.OrderByDescending(a => a.Id).GroupBy(a => a.Column1).Select(a => a.Key).ToList();

I only needs ids of rows, but nothing returns. I've done it with sql, but couldn't convert to EF lambda.

select max(Id) as Id from [Messages]
group by Column1
order by Id DESC
entity-framework c#
1 Answers

You are selecting key which in group by means column1 on which you are grouping the values. One way to get the values as you expected is to try something like below:

var query = Messages.GroupBy(item => item.Column1).Select(group =>   group.Last().Id)
        .OrderBy(item => item.Id)
        .Select(a => a.Id)
        .ToList();

Or to get complete rows:

var query = Messages.GroupBy(item => item.Column1)
        .Select(group => group.Last())
        .OrderBy(item => item.Id)
        .Select(a => a)
        .ToList();
user_102603990
Related