如何根据特定的子行有效地筛选父记录

Question

我的SQL Server数据库中有两个相关的表：Transaction和TransactionHistory。这是一种一对多的关系。它们的结构基本上是这样的：

Transaction
TransactionID
Tries
(other fields)
TransactionHistory
TransactionHistoryID
TransactionID (a foreign key)
CreationDate
Status
ErrorType
(other fields)

在我的EF Core对象模型中，我有相应的类：

public class Transaction
{
public int TransactionID { get; set; }
public int Tries {get; set; }
public ICollection<TransactionHistory> TransactionHistories{ get; set; }
/// Other fields
}
public class TransactionHistory
{
public int TransactionHistoryID { get; set; }
public int TransactionID  { get; set; }
public DateTime CreationDate { get; set; }
public string Status{ get; set; }
public string ErrorType { get; set; }
public Transaction Transaction { get; set; }
/// Other fields
}

我需要的是从具有最近TransactionHistory行的Transaction表中获取记录，并根据该子行中的值筛选事务记录。例如，我有一个LINQ查询来获取处于"；准备就绪"；状态，或在"；错误"；可以再次尝试的状态，如下所示：

var retriableErrorTypes = new List<string>(){"CONNECTION", "TECHNICAL"};
var MaxTries = 3;
DbContext.Transaction
.Include(t => t.TransactionHistories.OrderByDescending(h => h.CreationDate).Take(1))
.Where(t => t.TransactionHistories.OrderByDescending(h => h.CreationDate).First().Status == "READY"
|| (t.TransactionHistories.OrderByDescending(h => h.CreationDate).First().Status == "ERROR"
&& retriableErrorTypes.Contains(t.TransactionHistories.OrderByDescending(h => h.CreationDate).First().ErrorType)
&& t.Tries < MaxTries));

乍一看，结果似乎是正确的，尽管我还没有完全测试这一点。但是，必须不断重复子查询以获得最近的子行并不是很好。事实上，这种重复最终会出现在生成的SQL查询中(根据LINQPad(：

SELECT [t].[TransactionID], [t].[Tries], [t2].[TransactionHistoryID], [t2].[CreationDate], [t2].[Status], [t2].[ErrorType], [t2].[TransactionID]
FROM [dbo].[Transaction] AS [t]
LEFT JOIN (
SELECT [t1].[TransactionHistoryID], [t1].[CreationDate], [t1].[Status], [t1].[ErrorType], [t1].[TransactionID]
FROM (
SELECT [t0].[TransactionHistoryID], [t0].[CreationDate], [t0].[Status], [t0].[ErrorType], [t0].[TransactionID], ROW_NUMBER() OVER(PARTITION BY [t0].[TransactionID] ORDER BY [t0].[CreationDate] DESC) AS [row]
FROM [dbo].[TransactionHistory] AS [t0]
) AS [t1]
WHERE [t1].[row] <= 1
) AS [t2] ON [t].[TransactionID] = [t2].[TransactionID]
WHERE ((
SELECT TOP(1) [t3].[Status]
FROM [dbo].[TransactionHistory] AS [t3]
WHERE [t].[TransactionID] = [t3].[TransactionID]
ORDER BY [t3].[CreationDate] DESC) = N'READY') OR ((((
SELECT TOP(1) [t4].[Status]
FROM [dbo].[TransactionHistory] AS [t4]
WHERE [t].[TransactionID] = [t4].[TransactionID]
ORDER BY [t4].[CreationDate] DESC) = N'ERROR') AND (
SELECT TOP(1) [t5].[ErrorType]
FROM [dbo].[TransactionHistory] AS [t5]
WHERE [t].[TransactionID] = [t5].[TransactionID]
ORDER BY [t5].[CreationDate] DESC) IN (N'CONNECTION', N'TECHNICAL')) AND ([t].[Tries] < @__MaxTries_1))
ORDER BY [t].[TransactionID], [t2].[TransactionID], [t2].[CreationDate] DESC, [t2].[TransactionHistoryID]

这根本没有效率。WHERE子句中的这三个子查询和FROM子句中的那一个子查询最终都获取相同的行。我想找到一种编写LINQ查询的方法，尽可能减少这种重复，但到目前为止，我还没有得到更好的方法。

我试着使用投影，像这样：

DbContext.Transaction.Select(t => new {t, TransactionHistory = t.TransactionHistories.OrderByDescending(h => h.CreationDate).First()})
.Where(t => t.TransactionHistory.Status == "READY"
|| (t.TransactionHistory.Status == "ERROR"
&& retriableErrorTypes.Contains(t.TransactionHistory.ErrorType)
&& t.t.Tries < MaxTries));

然而，虽然LINQ查询可读性更强，不会重复，但它对生成的SQL查询没有影响，后者几乎完全相同(无论如何，根据LINQPad的说法(。此外，它以匿名类型而不是Transaction类型返回记录，这不是我想要的，但我可以简单地重新转换结果，所以这不是真正的问题。

我的问题是：如何在LINQ查询中有效地获取特定的子行并基于该子行筛选父记录？

注意：正如标记中所指定的，这是针对的。NET 5应用程序的实体框架核心，尽管它很可能会升级到。NET 6。此外，这是一个新系统和一个新数据库，因此如果需要，可以调整数据库结构。

Answer 1

尝试以下查询，它应该是有效的：

var retriableErrorTypes = new List<string>(){"CONNECTION", "TECHNICAL"};
var MaxTries = 3;
var query = 
from t in DbContext.Transaction
from h in t.TransactionHistories.OrderByDescending(h => h.CreationDate).Take(1)
where h.Status == "READY"
|| (h.Status == "ERROR" && retriableErrorTypes.Contains(h.ErrorType) && t.Tries < MaxTries)
select new 
{
Transaction = t,
LastHistory = h
};