c# - LINQ to Entities 从有序分组中查找最高记录

Question

我有一个问题，我知道如何在 SQL 中解决但不知道如何使用 Linq to Entities。

我的数据是这样的:

ID    GROUP   TIMESTAMP
--    -----   ---------
1     A       2011-06-20
2     A       2011-06-21
3     B       2011-06-21
4     B       2011-06-22
5     B       2011-06-23
6     C       2011-06-30

我想检索所有实体对象(而不仅仅是 ID)，以便我只从每个组中获取最新记录。 (即 ID 为 2、5、6 的记录)

在 SQL 中我会做这样的事情:

SELECT * FROM my_table a
WHERE a.timestamp = 
   (SELECT MAX(timestamp) FROM my_table b
   WHERE a.group = b.group)

(为了这个问题，您可以假设时间戳在每个组中都是唯一的)。

我想使用 Linq to Entities 针对 WCF 数据服务执行此查询，但我似乎无法像这样引用外部查询的嵌套查询。谁能帮忙？

Answer 1

可能不如手写版本干净高效，但这是我想出的

    var q = from a in db.MyEntities
            where a.Timestamp == (from b in db.MyEntities
                                  where b.Group == a.Group
                                  select b.Timestamp).Max()
            select a;

翻译成这个SQL

SELECT
[Project1].[Id] AS [Id],
[Project1].[Group] AS [Group],
[Project1].[Timestamp] AS [Timestamp]
FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[Group] AS [Group],
        [Extent1].[Timestamp] AS [Timestamp],
        [SSQTAB1].[A1] AS [C1]
        FROM [MyEntities] AS [Extent1]
         OUTER APPLY
        (SELECT
                MAX([Extent2].[Timestamp]) AS [A1]
                FROM [MyEntities] AS [Extent2]
                WHERE [Extent2].[Group] = [Extent1].[Group]) AS [SSQTAB1]
)  AS [Project1]
WHERE [Project1].[Timestamp] = [Project1].[C1]