根据最新相关实体的值选择实体集合

本文关键字：实体 选择 集合 最新 | 更新日期: 2023-09-27 18:37:18

在我目前的项目中，我在几个地方不断遇到同样的困难。

最简单的实例如下：

我有两个相关的实体，请求和请求操作。每个请求操作都有一个状态和一个时间戳，并指向一个请求。

我需要根据最新相关请求操作的状态查询请求集合。

示例：获取最新请求操作的状态为"打开"的所有请求。

非规范化数据库以使最新状态成为请求实体的属性不是一个选项。

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

我可以想到两个解决方案，但都不是特别吸引人。

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);
    }
}

虽然这会为我提供我想要的结果，但它非常无效，并且浪费了资源和执行时间。 我查询的数据库非常大，遍历每条记录确实不可行。

有没有一种有效的方法来使用实体来做到这一点？ 我发现很难想象我是唯一一个需要这种类型的功能的人。

根据最新相关实体的值选择实体集合

用于识别任何打开请求操作的最新时间戳的 SQL 端不会只是：

;WITH MostRecentActions AS 
(
  SELECT RequestID, ActionTimestamp,
    /* other relevant RequestAction columns, */
    rn = ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY ActionTimestamp DESC)
  FROM dbo.RequestActions
  WHERE Status = 'Open'
)
SELECT RequestID, ActionTimestamp /* , other columns */ 
  FROM MostRecentActions
  WHERE rn = 1;

您可以使用 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。

相关文章：