4

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
Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Alexandr Sulimov
  • 1,894
  • 2
  • 24
  • 48

3 Answers3

11

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.

Bradley Uffner
  • 16,641
  • 3
  • 39
  • 76
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] }
}
BurnsBA
  • 4,347
  • 27
  • 39
1

I suggest MoreLINQ's MaxBy function, that is:

context.History.GroupBy( x => x.id ).Select( x => x.MaxBy( y => y.date) )
Haukinger
  • 10,420
  • 2
  • 15
  • 28
  • Is this Entity Framework compatible? The question wasn't tagged with EF, but from the way they are querying through a variable named `context`, and the fact that they mention "tables", makes me think that they need something that supports Entity Framework. Based on a quick scan of the source for `MaxBy`, it may not be. – Bradley Uffner Aug 29 '17 at 14:48