首页 文章

如何编写linq语句来获取最后一组记录

提问于
浏览 次
2

我有两个基本上做同样事情的SQL语句，即根据一组记录的日期时间字段从表中检索最后一条记录 . 我正在使用数据优先的Entity Framework模型 . 如何使用LINQ Lambda函数编写这些SQL语句？

即

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

而不是

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

SQL语句：

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)
c# sql linq entity-framework lambda

2 回答

  • 0

    如果你需要方法调用表单，但是发现它很难解决，那么首先使用其他语法：

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

    然后通过一次一个地使用每个子句转换为方法调用：

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

    从中我可以锻炼第二个而无需首先写出linq语法形式：

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

    （除非它不包括可能是错误的内容，因为如果时间戳不能保证唯一，则问题中给出的SQL可能包含一些额外的不适当的结果） .

    我可以't check on the SQL produced right now, but it should hopefully be equivalent in results (if not necessarily matching). There'的情况下，分组没有't translate to SQL well, but I certainly don'认为这将是一个 .

    回复于
  • 3

    我最终使用了与第一个SQL语句相对应的以下内容 .

    // 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));
    回复于

相关问题