如何在LINQ中使用类似SQL EXISTS的功能实现指定账户余额计算

问题场景 #

现有两段LINQ代码，第一段用于筛选符合条件的特定账户：

var _query = _dbContext.CashVoucherDetails
             .Where(x => x.CreationDate.Date >= From.Date && x.CreationDate.Date <= To.Date && (x.Type == Common.TransactionType.CPV || x.Type == Common.TransactionType.BPV) && x.CompanyCode == BranchCode && x.DebitAmount > 0)
             .GroupBy(v => new { v.AccountCode})
             .Select(g => new
              {
                 AccountCode = g.Key.AccountCode,
              });

var balances = _dbContext.CashVoucherDetails
               .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode)
             //.Exist(_query.Account) (I want only account which exist in upper query)
               .GroupBy(v => new { v.AccountCode})
               .Select(g => new
               {
                   AccountCode = g.Key.AccountCode,
                   Balance = g.Sum(x => x.DebitAmount - x.CreditAmount),
                });

需求是在第二段查询中用类似SQL EXISTS 的功能，只计算第一段查询中存在的账户的余额。同时需要将以下SQL查询转换为LINQ实现：

SELECT `c`.`AccountCode`, `c`.`CompanyCode` AS `LocationCode`, COALESCE(SUM(`c`.`DebitAmount` - `c`.`CreditAmount`), 0) AS `Balance`
    FROM `CashVoucherDetails` AS `c`
    WHERE (CONVERT(`c`.`CreationDate`, date) <= '2022-12-20') AND (`c`.`CompanyCode` = '002') and `c`.`AccountCode` IN 
    (
        SELECT `c`.`AccountCode`
        FROM `CashVoucherDetails` AS `c`
        WHERE ((((CONVERT(`c`.`CreationDate`, date) >= '2022-12-20') AND (CONVERT(`c`.`CreationDate`, date) <= '2022-12-20')) AND `c`.`Type`     IN ('CPV', 'BPV')) AND (`c`.`CompanyCode` = '002')) AND (`c`.`DebitAmount` > 0)
        GROUP BY `c`.`AccountCode` 
    )
    GROUP BY `c`.`AccountCode`, `c`.`CompanyCode`;

一、LINQ中实现类似EXISTS的逻辑 #

在LINQ里，用Any()方法即可实现SQL的EXISTS效果。修改第二段查询的Where条件，加入对第一段查询的存在性判断：

var balances = _dbContext.CashVoucherDetails
               .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode 
                           // 用Any实现EXISTS逻辑，判断当前账户是否在_query中存在
                           && _query.Any(q => q.AccountCode == x.AccountCode))
               .GroupBy(v => new { v.AccountCode})
               .Select(g => new
               {
                   AccountCode = g.Key.AccountCode,
                   Balance = g.Sum(x => x.DebitAmount - x.CreditAmount),
                });

也可以先提取第一段查询的账户Code集合，用Contains实现类似IN的效果（EF会自动转换为对应的SQL，性能和Any差异不大）：

// 先提取符合条件的账户Code集合
var validAccountCodes = _query.Select(q => q.AccountCode).ToList();

var balances = _dbContext.CashVoucherDetails
               .Where(x => x.CreationDate.Date <= To.Date && x.CompanyCode == BranchCode 
                           && validAccountCodes.Contains(x.AccountCode))
               .GroupBy(v => new { v.AccountCode})
               .Select(g => new
               {
                   AccountCode = g.Key.AccountCode,
                   Balance = g.Sum(x => x.DebitAmount - x.CreditAmount),
                });

二、对应SQL的LINQ实现 #

要把给出的SQL转成LINQ，核心是实现子查询过滤，同时处理COALESCE（LINQ中用??结合Sum()，或DefaultIfEmpty().Sum()）：

// 替换为实际业务中的变量
var targetDate = new DateTime(2022, 12, 20);
var companyCode = "002";

// 子查询：获取符合条件的账户Code
var subQuery = _dbContext.CashVoucherDetails
    .Where(c => c.CreationDate.Date >= targetDate.Date 
                && c.CreationDate.Date <= targetDate.Date 
                && (c.Type == "CPV" || c.Type == "BPV") // 若为枚举类型，改用Common.TransactionType.CPV等
                && c.CompanyCode == companyCode 
                && c.DebitAmount > 0)
    .GroupBy(c => c.AccountCode)
    .Select(g => g.Key);

// 主查询：计算符合条件账户的余额
var result = _dbContext.CashVoucherDetails
    .Where(c => c.CreationDate.Date <= targetDate.Date 
                && c.CompanyCode == companyCode 
                && subQuery.Any(s => s == c.AccountCode)) // 也可改用subQuery.Contains(c.AccountCode)
    .GroupBy(c => new { c.AccountCode, c.CompanyCode })
    .Select(g => new
    {
        AccountCode = g.Key.AccountCode,
        LocationCode = g.Key.CompanyCode,
        // 用??处理SUM为null的情况，等效于SQL的COALESCE
        Balance = g.Sum(c => c.DebitAmount - c.CreditAmount) ?? 0
    });

如果EF版本不支持Sum()直接返回null，可改用g.Select(c => c.DebitAmount - c.CreditAmount).DefaultIfEmpty(0).Sum()实现COALESCE的效果。

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