如何根据特定的子行有效地筛选父记录
如何根据特定的子行有效地筛选父记录
提问于 2022-07-25 13:10:28
我的Server数据库中有两个相关的表: Transaction和TransactionHistory。一对多的关系。基本上，它们的结构如下：
Transaction
TransactionID
Tries
(other fields)
TransactionHistory
TransactionHistoryID
TransactionID (a foreign key)
CreationDate
Status
ErrorType
(other fields)在我的EF核心对象模型中，我有相应的类：
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
}我需要的是从事务表中获取记录，并根据这个子行的值过滤事务记录。例如，我有一个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查询没有任何影响，因为它与生成的SQL查询基本相同(至少根据LINQPad )。另外，它返回匿名类型的记录，而不是事务类型，这不是我想要的，但是我可以简单地重新转换结果，所以这不是问题。
我的问题是:如何在LINQ查询中，有效地获取特定的子行，并根据此子行筛选父记录？
注意:正如标记中所指定的，这是针对带有实体框架核心的.NET 5应用程序的，尽管它很可能会升级到.NET 6。此外，这是一个新的系统和一个新的数据库，因此可以根据需要调整数据库结构。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-07-25 13:54:50
尝试以下查询，它应该是有效的：
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
};页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73109803
复制相关文章
相似问题
腾讯云开发者
