Select values with max date for each ID [duplicate]

Question

This is the table "history"

id  value   date
1   1   01/01/2017 20:20:20
1   2   02/01/2017 20:20:20
1   3   03/01/2017 20:20:20
2   5   01/01/2017 20:20:20
2   6   02/01/2017 20:20:20

How with linq select max values for each id

context.History
    .GroupBy(x => x.id) ??
    .SelectOnlyWithMax(z => z.date) ??

In result only two objects

id  value   date
1   3   03/01/2017 20:20:20
2   6   02/01/2017 20:20:20

Answer 1

If you want the entire row with the highest date for each Id, you can use the following code (written with LinqPad). If you just want the Id, you can use @BurnsBA's answer, as it will be slightly more efficient.

void Main()
{
    var data = new List<Record>
    {
        new Record(){Id=1, Value=1, Date=new DateTime(2017,1,1)},
        new Record(){Id=1, Value=2, Date=new DateTime(2017,2,1)},
        new Record(){Id=1, Value=3, Date=new DateTime(2017,3,1)},
        new Record(){Id=2, Value=5, Date=new DateTime(2017,1,1)},
        new Record(){Id=2, Value=6, Date=new DateTime(2017,2,1)},
    };


    var query = data.GroupBy(d => d.Id)
                    .SelectMany(g => g.OrderByDescending(d => d.Date)
                                      .Take(1));
    query.Dump();
}

public class Record
{
    public int Id { get; set; }
    public int Value { get; set; }
    public DateTime Date { get; set; }
}

Results:

First it groups by Id, then sorts the items within the group by Date in descending order, and returns the first one, SelectMany then flattens the list.

Answer 2

public class History
{
    public int id { get; set; }
    public int value { get; set; }
    public DateTime date { get; set; }
}

// setup:
var values = new List<History>();
values.Add(new History() { id = 1, value = 1, date = DateTime.Parse("01/01/2017 20:20:20") });
values.Add(new History() { id = 1, value = 2, date = DateTime.Parse("02/01/2017 20:20:20") });
values.Add(new History() { id = 1, value = 3, date = DateTime.Parse("03/01/2017 20:20:20") });
values.Add(new History() { id = 2, value = 5, date = DateTime.Parse("01/01/2017 20:20:20") });
values.Add(new History() { id = 2, value = 6, date = DateTime.Parse("02/01/2017 20:20:20") });

// result :
values.GroupBy(
    x => x.id, 
    y => y.date,
    // Below, dates will be enumerable
    (id, dates) => new { id = id, date = dates.Max() }
)

// returns enumerable collection of anonymous type:
{
    { id = 1, date = [3/1/2017 8:20:20 PM] }, 
    { id = 2, date = [2/1/2017 8:20:20 PM] }
}