如何高效基于子行筛选EF Core中的父记录？

针对重复子查询导致SQL低效的问题，以下几种方案可以优化查询性能，同时保持代码可读性：

方案1：先获取最新子记录再关联父表 #

通过一次子查询获取每个Transaction对应的最新TransactionHistory，再与父表关联筛选，避免重复的排序和取首行操作：

var retriableErrorTypes = new List<string>{"CONNECTION", "TECHNICAL"};
const int MaxTries = 3;

// 一次性获取每个Transaction的最新历史记录
var latestHistories = DbContext.TransactionHistory
    .GroupBy(h => h.TransactionID)
    .Select(g => g.OrderByDescending(h => h.CreationDate).First());

// 关联父表并应用筛选条件
var targetTransactions = DbContext.Transaction
    .Join(
        latestHistories,
        transaction => transaction.TransactionID,
        latestHistory => latestHistory.TransactionID,
        (transaction, latestHistory) => new { Transaction = transaction, LatestHistory = latestHistory }
    )
    .Where(x => 
        x.LatestHistory.Status == "READY" 
        || (x.LatestHistory.Status == "ERROR" 
            && retriableErrorTypes.Contains(x.LatestHistory.ErrorType) 
            && x.Transaction.Tries < MaxTries))
    // 如需包含最新的历史记录，用Include过滤匹配
    .Select(x => x.Transaction)
    .Include(t => t.TransactionHistories.Where(h => h.TransactionHistoryID == x.LatestHistory.TransactionHistoryID))
    .ToList();

优势：仅执行一次获取最新子记录的子查询，生成的SQL结构简洁，避免重复计算。

方案2：使用投影封装最新子记录 #

通过投影将父实体和最新子记录打包，再基于投影结果筛选，适合需要直接操作关联数据的场景：

var retriableErrorTypes = new List<string>{"CONNECTION", "TECHNICAL"};
const int MaxTries = 3;

var targetTransactions = DbContext.Transaction
    .Select(t => new 
    {
        Transaction = t,
        LatestHistory = t.TransactionHistories.OrderByDescending(h => h.CreationDate).FirstOrDefault()
    })
    .Where(x => 
        x.LatestHistory != null 
        && (x.LatestHistory.Status == "READY" 
            || (x.LatestHistory.Status == "ERROR" 
                && retriableErrorTypes.Contains(x.LatestHistory.ErrorType) 
                && x.Transaction.Tries < MaxTries)))
    .Select(x => x.Transaction)
    .Include(t => t.TransactionHistories.Where(h => h.CreationDate == x.LatestHistory.CreationDate && h.TransactionID == t.TransactionID))
    .ToList();

注意：如果CreationDate可能存在重复，建议改用TransactionHistoryID（自增主键）排序，确保唯一获取最新记录。

方案3：利用窗口函数（.NET 6+推荐） #

SQL Server的窗口函数ROW_NUMBER()可以高效筛选每个分组的首行，EF Core 6+支持直接在LINQ中使用该函数，适合大数据量场景：

var retriableErrorTypes = new List<string>{"CONNECTION", "TECHNICAL"};
const int MaxTries = 3;

var targetTransactions = DbContext.TransactionHistory
    .Select(h => new 
    {
        History = h,
        RowNum = EF.Functions.RowNumber().Over(PartitionBy(h.TransactionID).OrderByDescending(h.CreationDate))
    })
    .Where(x => x.RowNum == 1) // 筛选每个Transaction的最新历史记录
    .Join(
        DbContext.Transaction,
        x => x.History.TransactionID,
        t => t.TransactionID,
        (x, t) => new { Transaction = t, LatestHistory = x.History }
    )
    .Where(x => 
        x.LatestHistory.Status == "READY" 
        || (x.LatestHistory.Status == "ERROR" 
            && retriableErrorTypes.Contains(x.LatestHistory.ErrorType) 
            && x.Transaction.Tries < MaxTries))
    .Select(x => x.Transaction)
    .Include(t => t.TransactionHistories.Where(h => h.TransactionHistoryID == x.LatestHistory.TransactionHistoryID))
    .ToList();

优势：仅扫描一次TransactionHistory表即可完成最新记录筛选，性能最优，尤其适合数据量较大的场景。

数据库层面优化建议 #

配合以下索引可以进一步提升查询效率：

• 给TransactionHistory创建复合索引：

  CREATE NONCLUSTERED INDEX IX_TransactionHistory_TransactionID_CreationDate 
  ON TransactionHistory (TransactionID, CreationDate DESC) 
  INCLUDE (Status, ErrorType);
  

  该索引可以直接支持按TransactionID分组并按CreationDate倒序取首行的操作，避免额外排序。
• 如果Transaction表的Tries字段筛选频率高，可以单独创建索引：

  CREATE NONCLUSTERED INDEX IX_Transaction_Tries ON Transaction (Tries);
  

内容的提问来源于stack exchange，提问作者Dantre