How to select only the records with the highest date in LINQ

Question

I have a table, 'lasttraces', with the following fields.

Id, AccountId, Version, DownloadNo, Date

The data looks like this:

28092|15240000|1.0.7.1782|2009040004731|2009-01-20 13:10:22.000
28094|61615000|1.0.7.1782|2009040007696|2009-01-20 13:11:38.000
28095|95317000|1.0.7.1782|2009040007695|2009-01-20 13:10:18.000
28101|15240000|1.0.7.1782|2009040004740|2009-01-20 14:10:22.000
28103|61615000|1.0.7.1782|2009040007690|2009-01-20 14:11:38.000
28104|95317000|1.0.7.1782|2009040007710|2009-01-20 14:10:18.000

How can I, in LINQ to SQL, only get the last lasttrace of every AccountId (the one with the highest date)?

actually in your example all logs with the same account id, have the exact same date, so which one takes preference in that case? — BlackTigerX, Jan 22 '09 at 20:05

score 251 · Accepted Answer · edited Mar 06 '14 at 19:33

251

If you just want the last date for each account, you'd use this:

var q = from n in table
        group n by n.AccountId into g
        select new {AccountId = g.Key, Date = g.Max(t=>t.Date)};

If you want the whole record:

var q = from n in table
        group n by n.AccountId into g
        select g.OrderByDescending(t=>t.Date).FirstOrDefault();

edited Mar 06 '14 at 19:33

Ravi Ram

24,078
21
82
113

answered Jan 22 '09 at 19:35

Mehrdad Afshari

414,610
91
852
789

3

Oracle doesn't support this. If query performance is out of your consideration, you can convert table ToList before you do the query. – Will Wu Mar 08 '13 at 09:44
9

It would be great if you can post a `method-chain` solution for this. – LCJ May 28 '13 at 12:07
second query - shouldn't it be `(from n in table ...).First()`? – Jason L Aug 29 '13 at 01:53
@JasonL, no. The `First()` call should apply to the `g.Order...` expression (subquery). – Mehrdad Afshari Aug 29 '13 at 08:04
1

@Mehrdad Afshari **thats amazing**, I wanted the second one primarily but they are both now in my snippets as **so very useful**. Thank you, thank you, thank you!!!!! – Andrew Day Aug 23 '16 at 13:24
OrderByDescending(t => t.Date)' could not be translated. (the weak EF) – Mahmoud Hboubati Sep 11 '21 at 16:22

NoWar · Answer 2 · 2012-04-12T13:25:28.733

57

Here is a simple way to do it

var lastPlayerControlCommand = this.ObjectContext.PlayerControlCommands
                                .Where(c => c.PlayerID == player.ID)
                                .OrderByDescending(t=>t.CreationTime)
                                .FirstOrDefault();

Also have a look this great LINQ place - LINQ to SQL Samples

edited Apr 12 '12 at 13:25

answered Apr 12 '12 at 13:17

NoWar

36,338
80
323
498

31

This solution works fine if you were querying per account but does not do what OP stated, which is to get the most recent result for all records without providing an AccountId (in your case PlayerId) – Maciej May 03 '13 at 18:30
1

This solution inspired me to order instead to try to select the maximum. +1 – Razvan Dumitru Apr 15 '14 at 12:07
This solution may lead to "Sequence contains no elements" Error in Linq right? – xSkrappy Nov 15 '18 at 06:06

score 50 · Answer 3 · edited Mar 14 '17 at 17:17

50

If you want the whole record,here is a lambda way:

var q = _context
             .lasttraces
             .GroupBy(s => s.AccountId)
             .Select(s => s.OrderByDescending(x => x.Date).FirstOrDefault());

edited Mar 14 '17 at 17:17

Quantum

1,456
3
26
54

answered Mar 24 '16 at 03:30

Bob Zhang

841
3
10
17

1

Worked Great. Thanks. – Terry Nov 04 '19 at 19:02
This is the key here. – Jason P Sallinger Feb 20 '20 at 14:55

score 6 · Answer 4 · answered Jan 22 '09 at 19:38

6

It could be something like:

var qry = from t in db.Lasttraces
          group t by t.AccountId into g
          orderby t.Date
          select new { g.AccountId, Date = g.Max(e => e.Date) };

answered Jan 22 '09 at 19:38

bruno conde

47,767
15
98
117

score 3 · Answer 5 · answered Nov 21 '11 at 10:35

Go a simple way to do this :-

Created one class to hold following information

Level (number)
Url (Url of the site)

Go the list of sites stored on a ArrayList object. And executed following query to sort it in descending order by Level.

var query = from MyClass object in objCollection 
    orderby object.Level descending 
    select object

Once I got the collection sorted in descending order, I wrote following code to get the Object that comes as top row

MyClass topObject = query.FirstRow<MyClass>()

This worked like charm.

This gave me a better idea than my original thought. Thanks! — Paulj, Feb 12 '14 at 14:00

Kim Homann · Answer 6 · 2022-05-31T12:21:34.367

LINQ's .OrderByDescending().FirstOrDefault() approach is good. But the https://github.com/morelinq/MoreLINQ people do it better: .MaxBy()

Example:

IEnumerable<Person> data = Data.GetPersons();
Person oldestPerson = data.MaxBy(p => p.Age);

Or in the latest version of MoreLINQ, they have changed the return type:

IEnumerable<Person> data = Data.GetPersons();
IEnumerable<Person> oldestPersons = data.MaxBy(p => p.Age);
Person oldestPerson = oldestPersons.First();

So what used to be .MaxBy() is now .MaxBy().First(). This has the advantage that you can handle more than 1 result (used to be only the first person with the max age, but there may be more persons with the same age). Another advantage is that .MaxBy() can now handle an empty IEnumerable<T> without throwing an exception. Of course, the exception occurs in .First() then, but you can decide to use .FirstOrDefault() instead to avoid this.

How to select only the records with the highest date in LINQ

6 Answers6

Linked