max date record in LINQ

Question

I have this table named sample with these values in MS Sql Server:

 ID    Date    Description
1    2012/01/02 5:12:43    Desc1
2    2012/01/02 5:12:48    Desc2
3    2012/01/03 5:12:41    Desc3
4    2012/01/03 5:12:43    Desc4

Now I want to write LINQ query that result will be this:

4    2012/01/03 5:12:43    Desc4

I wrote this but it doesn't work:

List<Sample> q = (from n in  Sample.Max(T=>T.Date)).ToList();

Solution 1

Starting from .NET 6 MaxBy LINQ method is available.

var result = items.MaxBy(i => i.Date);

---

Prior to .NET 6:

O(n log n):

var result = items.OrderByDescending(i => i.Date).First();

O(n) – but iterates over the sequence twice:

var max = items.Max(i => i.Date);
var result = items.First(i => i.Date == max);

Or you can use MoreLINQ which has MaxBy method which is O(n)

Solution 2

To get the maximum Sample value by date without having to sort (which is not really necessary to just get the maximum):

var maxSample  = Samples.Where(s => s.Date == Samples.Max(x => x.Date))
                        .FirstOrDefault();

Solution 3

var lastInstDate = model.Max(i=>i.ScheduleDate);

We can get max date from the model like this.

Solution 4

List<Sample> q = Sample.OrderByDescending(T=>T.Date).Take(1).ToList();

But I think you want

Sample q = Sample.OrderByDescending(T=>T.Date).FirstOrDefault();

Solution 5

Here's a single pass option:

var maxSample =
    Samples
        .Aggregate((x, y) => x.Date < y.Date ? y : x);

View more solutions

Comments

[knocte]

this has the same complexity as the accepted answer that requires ordering, as far as I understand

[BrokenGlass]

@knocte no it's not the same complexity - this is O(n) - 2 passes needed, each O(n) - sorting would be O(n log n) so more expensive

[knocte]

ok cool, could it be written in a way to have only 1 pass? the point of using LINQ is having less imperative code, but with the same or better performance

[BrokenGlass]

yes - it definitely could be written with an old school foreach loop in one pass, just got to keep the Sample instance around that has the max date so far.

[knocte]

right, so in this case using foreach(imperative) style is more efficient than LINQ? :(

[BrokenGlass]

You could still write it with Linq, e.g. using Aggregate but it would be nowhere near as readable imo, so I wouldn't consider it

[knocte]

Well, the thing is, if you're querying using LINQ over an IQueryable<> collection, you want the query to be as performant as possible in order to be converted to less SQL queries as possible by EntityFramework. So readability in this case is not as important as efficiency, especially if you have many rows in the table.

[chtenb]

This looks O(n^2) to me. The max is recomputed every iteration of the where clause.

[FrankCastle616]

Not sure why this isn't voted up more. By far the easiest and most efficient

[Nick]

@FrankCastle616 it doesn't actually answer the question. This returns a date, not the record containing that date

[Ian Kemp]

This is exactly the same as the accepted answer.

[Ian Kemp]

This is exactly the same as the accepted answer.

[Enigmativity]

@BrokenGlass - The Max should be separated out to avoid O(n^2).

[Guru Stron]

Note that MaxBy is not supported by EF Core (link)