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

C#.NetLinqLinq to-Sql

C# Problem Overview

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

Id, AccountId, Version, DownloadNo, Date

The data looks like this:

28092|15240000||2009040004731|2009-01-20 13:10:22.000
28094|61615000||2009040007696|2009-01-20 13:11:38.000
28095|95317000||2009040007695|2009-01-20 13:10:18.000
28101|15240000||2009040004740|2009-01-20 14:10:22.000
28103|61615000||2009040007690|2009-01-20 14:11:38.000
28104|95317000||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)?

C# Solutions

Solution 1 - C#

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();

Solution 2 - C#

Here is a simple way to do it

var lastPlayerControlCommand = this.ObjectContext.PlayerControlCommands
                                .Where(c => c.PlayerID == player.ID)

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

Solution 3 - C#

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

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

Solution 4 - C#

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) };

Solution 5 - C#

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.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBas JansenView Question on Stackoverflow
Solution 1 - C#mmxView Answer on Stackoverflow
Solution 2 - C#NoWarView Answer on Stackoverflow
Solution 3 - C#Bob ZhangView Answer on Stackoverflow
Solution 4 - C#bruno condeView Answer on Stackoverflow
Solution 5 - C#Sudhir KesharwaniView Answer on Stackoverflow