CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Bas Jansen
Bas Jansen

Reputation: 1840

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

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)?

Upvotes: 134

Views: 236594

Answers (6)

Kim Homann
Kim Homann

Reputation: 3229

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.

Upvotes: 1

Bob Zhang
Bob Zhang

Reputation: 891

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

Upvotes: 54

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421998

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

Upvotes: 252

NoWar
NoWar

Reputation: 37633

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

Upvotes: 58

Sudhir Kesharwani
Sudhir Kesharwani

Reputation: 311

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.

Upvotes: 3

bruno conde
bruno conde

Reputation: 48265

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

Upvotes: 6

Related Questions