c# - 根据最近相关实体的值选择实体集合

Question

我在当前项目的多个地方不断遇到相同类型的困难。

最简单的例子如下:

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

我需要根据最近相关的 RequestAction 的状态查询一组请求。

示例:获取所有最近的 RequestAction 状态为“打开”的请求。

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

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

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

1) 我可以手动编写 SQL。这个问题的原始 SQL 答案涉及通过外部连接将表连接到自身，表的一个实例的时间戳大于另一个，然后查找第二个表为空的记录，表明没有更大的时间戳可以被发现。这就是我过去处理这种情况的方式。它在数据库中是高效的，因为它使用索引，这与其中包含最大值的子查询不同。但是，我的开发团队被强烈要求使用实体来与数据库对话，因此强烈建议不要使用此解决方案。

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

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

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

Answer 1

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;