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