How to get in LINQ one row for each key

Question

I have list with:

EMP_ID | UPDATED_DATE | MARK
------ | ------------ | ----
111    | 01/01/2015   | 99  
111    | 01/01/2013   | 85  
111    | 01/01/2017   | 80  
222    | 01/01/2011   | 70  
222    | 01/01/2015   | 55  
222    | 01/01/2002   | 60  

I have to select one row for each ID, with the latest UPDATED_DATE, In our etc:

EMP_ID | UPDATED_DATE | MARK
------ | ------------ | ----
111    | 01/01/2017   | 80  
222    | 01/01/2015   | 55  

This is the code for order:

empMarksList.OrderBy(x=>x.EMP_ID).ThenBy(y=>y.UPDATED_DATE)

Answer 1

Use GroupBy:

var items = empMarksList
                   .GroupBy(e => e.EMP_ID)
                   .Select(grp => grp.OrderByDescending(v => v.UPDATED_DATE).First());

Or if you want a Dictionary:

var dict = empMarksList
              .GroupBy(e => e.EMP_ID)
              .ToDictionary(grp => grp.Key,
                            grp => grp.OrderByDescending(v => v.UPDATED_DATE).First());

Answer 2

I prefer this variant, but it's the same thing as Amir's answer:

var query =
    empMarksList
        .GroupBy(x => x.EMP_ID)
        .SelectMany(x => x.OrderByDescending(y => y.UPDATED_DATE).Take(1));

Answer 3

Another option is:

var items = context.EmpMarks
    .GroupBy(e => e.EMP_ID, (k, g) => g
        .FirstOrDefault(e => g.Max(v => v.UPDATED_DATE) == e.UPDATED_DATE));

Which actually should generate GROUP BY in SQL.