score:10

Accepted answer

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:

enter image description here

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.

score:1

I suggest MoreLINQ's MaxBy function, that is:

context.History.GroupBy( x => x.id ).Select( x => x.MaxBy( y => y.date) )

score: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] }
}

Related Articles