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

Stack Overflow用户

提问于 2022-07-25 05:10:28

回答 1查看 82关注 0票数 0

我的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。此外，这是一个新的系统和一个新的数据库，因此可以根据需要调整数据库结构。

entity-framework-core

.net-5

sql-server

linq

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-07-25 05: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
    };