how can i select last row in a group

Question

I have a problem getting last row in a each group. I am using Linq query to retrieve the groups. Here is my LINQ query.

return View(db.tblMsgs.OrderByDescending(a => a.Id)
    .GroupBy(a => new { a.Sender, a.Receiver }).Select(x => x.FirstOrDefault())
    .Where(a => a.Receiver == username).ToList());

using FirstOfDefault() I am getting first row in a group. Using LastOrDefault() I am getting run time exception.

Possible duplicate of [Last and LastOrDefault not supported](http://stackoverflow.com/questions/7253529/last-and-lastordefault-not-supported) — Tolga Evcimen, Dec 08 '16 at 06:03
LINQ to Entities does not recognize the method 'TicketGeneration.Models.tblMsg LastOrDefault[tblMsg](System.Collections.Generic.IEnumerable`1[TicketGeneration.Models.tblMsg])' method, and this method cannot be translated into a store expression. — Rajneesh Pant, Dec 08 '16 at 06:39

Tolga Evcimen · Accepted Answer · 2016-12-08T06:24:31.927

That's what I run into too, in some time now.

After a little bit of research I found out that only way that works as it must is to reverse the list that you want the get the last item of and get the first item.

The reason behind this is that SQL languages does not have a statement as SELECT BOTTOM but SELECT TOP. Hence our LastOrDefault query could not be translated into SQL. The possible way of doing so is to OrderByDescending method.

return View(db.tblMsgs.OrderByDescending(a => a.Id)
    .GroupBy(a => new { a.Sender, a.Receiver }).Select(x => x.OrderByDescending(y => y.SomeAttribute).FirstOrDefault())
    .Where(a => a.Receiver == username).ToList());

Edit:

Only thing you should be choosy about is the column to order by. It can be the id field if it is an auto incremented number value, or an add date of the row(better be generated by server or it can cause problems).

Glad that it's working. Nice to see by just sparing some time we can help each others over the world! I would be more glad if you could accept the answer too. — Tolga Evcimen, Dec 08 '16 at 06:49

how can i select last row in a group

1 Answers1