如何优化C# Linq中双表关联计算账户余额的慢查询

原查询的核心问题是分组后每个账户都要单独查询两次数据库（CashVoucherDetails和SaleDetails各一次），属于典型的N+1查询，数据量越大耗时越久。优化思路是提前汇总所有账户的余额数据，再和主查询结果关联，避免重复查询。

步骤1：预汇总全量余额数据 #

先一次性计算所有符合条件的账户在两个表中的总余额，避免每个分组重复计算：

// 预计算 CashVoucherDetails 中的账户余额
var cashBalances = _dbContext.CashVoucherDetails
    .Where(x => x.CreationDate.Date <= To.Date)
    .GroupBy(x => new { x.AccountCode, x.CompanyCode })
    .Select(g => new
    {
        g.Key.AccountCode,
        g.Key.CompanyCode,
        Balance = g.Sum(x => x.DebitAmount - x.CreditAmount)
    });

// 预计算 SaleDetails 中的账户余额
var saleBalances = _dbContext.SaleDetails
    .Where(x => x.CreationDate.Date <= To.Date)
    .GroupBy(x => new { x.AccountCode, x.CompanyCode })
    .Select(g => new
    {
        g.Key.AccountCode,
        g.Key.CompanyCode,
        Balance = g.Sum(x => x.DebitAmount - x.CreditAmount)
    });

// 合并两个表的余额，按账户和公司分组求和得到总余额
var totalBalances = cashBalances
    .Concat(saleBalances)
    .GroupBy(x => new { x.AccountCode, x.CompanyCode })
    .Select(g => new
    {
        g.Key.AccountCode,
        g.Key.CompanyCode,
        TotalBalance = g.Sum(x => x.Balance)
    });

步骤2：关联主查询与预计算的余额 #

将主查询的分组结果和预计算的总余额表关联，直接获取对应账户的余额（注：原代码中x.outer.DebitAmount应为笔误，修正为x.DebitAmount）：

var result = _dbContext.CashVoucherDetails
    .Where(x => x.CreationDate.Date >= From.Date && x.CreationDate.Date <= To.Date 
                && x.Type == Common.TransactionType.CPV && x.CompanyCode == BranchCode)
    .GroupBy(v => new { v.AccountCode, v.AccountDescription, v.CompanyCode })
    .Select(g => new
    {
        AccountDescription = g.Key.AccountDescription,
        AccountCode = g.Key.AccountCode,
        Payment = g.Sum(x => x.DebitAmount),
        g.Key.CompanyCode
    })
    // 左连接预计算的总余额，确保无余额的账户也能正常显示
    .Join(totalBalances,
          main => new { main.AccountCode, main.CompanyCode },
          balance => new { balance.AccountCode, balance.CompanyCode },
          (main, balance) => new
          {
              main.AccountDescription,
              main.AccountCode,
              main.Payment,
              Balance = balance?.TotalBalance ?? 0 // 无匹配余额时显示0
          })
    .ToList();

额外优化：添加数据库索引 #

针对频繁过滤和分组的字段，创建复合索引可大幅提升查询速度：

• 给CashVoucherDetails创建索引：CREATE INDEX IX_CashVoucherDetails_CompanyAccountDate ON CashVoucherDetails (CompanyCode, AccountCode, CreationDate) INCLUDE (DebitAmount, CreditAmount, Type)
• 给SaleDetails创建索引：CREATE INDEX IX_SaleDetails_CompanyAccountDate ON SaleDetails (CompanyCode, AccountCode, CreationDate) INCLUDE (DebitAmount, CreditAmount)

这些索引能让数据库快速定位符合条件的记录，避免全表扫描。

内容的提问来源于stack exchange，提问作者Ãfsħål Ãtħår