CODEWITHSUNDEEP
c#sqllinqentity-frameworklambda
fredcicles
fredcicles

Reputation: 115

How to write an linq statement to get the last of a group of records

I have 2 SQL statements that basically do the same thing, that is, retrieve the last record from a table based on a datetime field for a group of records. I am using the data-first Entity Framework model. How would I write either of these SQL statements using LINQ Lambda functions?

ie,

var u = db.AccessCodeUsage.Where(...).GroupBy(...)

rather than 

var u = from a in db.AccessCodeUsage
        where ...
        group by ...

SQL Statements:

SELECT  *
FROM    AccessCodeUsage a  
WHERE   NOT EXISTS (SELECT  1 
                    FROM    AccessCodeUsage 
                    WHERE   LocationId = a.LocationId
                    AND     Timestamp > a.Timestamp)

SELECT    a.*
FROM    AccessCodeUsage a
WHERE   a.Timestamp = 
        (SELECT MAX(Timestamp)
        FROM    AccessCodeUsage
        WHERE   a.LocationId = LocationId
        AND     a.AccessCode = AccessCode
        GROUP   By LocationId, AccessCode)

Upvotes: 2

Views: 3234

Answers (2)

fredcicles
fredcicles

Reputation: 115

I ended up using the following which corresponds to the first SQL statement.

// Retrieve only the latest (greatest value in timestamp field) record for each Access Code
var last = AccessCodeUsages.Where(u1 => !AccessCodeUsages
                           .Any(u2 => u2.LocationId == u1.LocationId &&
                                      u2.AccessCode == u1.AccessCode &&
                                      u2.Timestamp > u1.Timestamp));

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113382

If you need to have the method-call form, but are finding it tricky to work out, then use the other syntax first:

from a in db.AccessCodeUsage
  orderby a.TimeStamp descending
  group a by a.LocationId into grp
  from g in grp select g.First();

Then convert to method calls by taking each clause one at a time:

db.AccessCodeUsage
  .OrderByDescending(a => a.TimeStamp)
  .GroupBy(a => a.LocationId)
  .Select(g => g.First());

From which I can workout the second without bothering to write out the linq-syntax form first:

db.AccessCodeUsage
  .OrderByDescending(a => a.TimeStamp)
  .GroupBy(a => new {a.LocationId, a.AccessCode})
  .Select(g => g.First());

(Except it doesn't include what may be a bug, in that if timestamps aren't guaranteed unique, the SQL given in the question could include some extra inappropriate results).

I can't check on the SQL produced right now, but it should hopefully be equivalent in results (if not necessarily matching). There's cases where grouping doesn't translate to SQL well, but I certainly don't think this would be one.

Upvotes: 3

Related Questions