在我当前的项目中,我经常在几个地方遇到相同类型的困难.

最简单的实例如下：

我有两个相关的实体,Request和RequestAction.
每个RequestAction都有一个状态和一个时间戳,并指向一个Request.

我需要根据最近相关的RequestAction的状态查询请求的集合.

示例：获取所有最近RequestAction状态为“ Open”的请求.

对数据库进行非规范化以使最新状态成为Request实体的属性不是一种选择.

在我的应用程序的许多其他地方,我需要相同类型的过滤.我会跟踪许多版本的项目以进行历史记录和审核,但是我通常只想查看每个项目的最新版本.

我可以想到两种解决方案,但都不是特别有吸引力.

1)我可以手动编写SQL.对于此问题的原始SQL答案包括：在表的一个实例的时间戳大于另一个实例的时间戳的情况下,通过外部联接将表与其自身连接,然后在第二个表为null的位置查找记录,表明没有更大的时间戳可以被发现.这就是我过去处理场景的方式.由于它使用索引,因此在数据库中非常有效,这与其中带有最大值的子查询不同.但是,强烈建议我的开发团队使用实体与数据库进行对话,因此强烈建议不要使用此解决方案.

2)我可以使用实体将所有值加载到内存中,并在我的代码中手动循环查找要查找的值.代码看起来像这样：

var openRequests = new List<Request>();
var allRequests = context.Requests;
foreach (var request in allRequests)
{
    var recentAction = request.RequestActions
        .OrderByDescending(c => c.ActionTimestamp)
        .First();
    if (recentAction.Status == "Open")
    {
        openRequests.Add(request);
    }
}

虽然这可以为我提供所需的结果,但效率极低,并且浪费大量资源和执行时间.我要查询的数据库很大,并且遍历每条记录确实是不可行的.

是否有使用实体执行此操作的有效方法？我很难想象我是唯一需要这种功能的人.

解决方法:

您可以在单个数据库查询中使用LINQ to Entities查询此：

var openRequests = context.Requests
    .Where(r => r.RequestActions
        .OrderByDescending(ra => ra.ActionTimestamp)
        .Select(ra => ra.Status)
        .FirstOrDefault() == "Open")
    .ToList();

生成的SQL如下所示：

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[SomeProp1] AS [SomeProp1], 
[Extent1].[SomeProp2] AS [SomeProp2], -- ...etc.
FROM  [dbo].[Requests] AS [Extent1]
CROSS APPLY  (SELECT TOP (1) [Project1].[Status] AS [Status]
    FROM ( SELECT 
           [Extent2].[Status] AS [Status], 
           [Extent2].[ActionTimestamp] AS [ActionTimestamp]
           FROM [dbo].[RequestActions] AS [Extent2]
           WHERE [Extent1].[Id] = [Extent2].[RequestId]
    ) AS [Project1]
    ORDER BY [Project1].[ActionTimestamp] DESC ) AS [Limit1]
WHERE N'Open' = [Limit1].[Status]

我不知道这是否是一个“好”的高性能SQL.