LINQ: How to get the Max Id with a group by clause?

Question

I am looking for a way in LINQ to get a max Id record by using 'Group By' clause

Consider the following Sample data

  Table: ProcessAud

  ProcessSeq     ProjectSeq   ProjectValue    Active
      11              1           50000          Y
      12              1           10000          Y
      13              2           70000          Y 
      14              2           90000          Y

In which I want to get two records as a list such that is second and fourth
records (i.e) ProcessSeq 12 and 14. And I tried it like following

var ProcessAudList = ProcessAudService.FilterBy(x => x.Active == "Y"    
  ).GroupBy(x => x.ProjectSeq).Max().ToList();

It is not working properly, So how to do it in LINQ. Please anybody help.

Answer 1

You want to pick top record from each group.

var ProcessAudList = ProcessAudService.Where(x => x.Active == "Y")
.GroupBy(x => x.ProjectSeq, (key,g)=>g.OrderByDescending(e=>e.ProjectValue).First());

Check demo code

Answer 2

When you use GroupBy extension, method will return you IGrouping instance and you should query IGrouping instance like below;

var ProcessAudList = collection.Where(x => x.Active == "Y").GroupBy(x => x.ProjectSeq).Select(x => x.OrderByDescending(a => a.ProcessSeq).FirstOrDefault()).ToList();

Hope this helps