多连接查询的LINQ实现正确性验证及DDD分层建议

我计划采用DDD设计，需要把给定的SQL脚本转换成.NET Core 3的LINQ/C#实现，已经写了对应的代码，现在想请教三个问题：

• 这个实现是否正确？
• 有没有更优的实现方式？
• 这段逻辑应该放在Repository类还是应用层Service中？

原SQL脚本 #

if object_id('tempdb..#tempHistory') is not null drop table #tempHistory;

select * into #tempHistory from Table_tempVwHistory raterView
where raterView.PremiumChange != 0

select r.Id,r.Name, amt.Description as AmountType,coalesce(txn.amount,0) as BillingAmount, coalesce(rtr.amount,0) as RaterAmount from
(
    select id,Name,AmountTypeId,sum(premiumchange) amount from #tempHistory
    inner join AmountSubType ats
    on ats.id = StepAlias
    group by id,Name, ats.AmountTypeId
) rtr
full join
(
    select t.Id, ats.AmountTypeId, sum(td.Amount) amount from [Transaction] t
    inner join TransactionDetail td
    on t.id = td.TransactionId
    inner join AmountSubType ats
    on ats.id = td.AmountSubTypeId
    where AmountTypeId != 'TF'
    group by t.Id, ats.AmountTypeId
) txn
on rtr.Id = txn.Id and rtr.AmountTypeId = txn.AmountTypeId
inner join risk r
on r.id = rtr.Id or r.id = txn.Id
inner join AmountType amt
on amt.id = rtr.AmountTypeId or amt.id = txn.AmountTypeId
where coalesce(rtr.amount,0) != coalesce(txn.amount,0) and abs(coalesce(rtr.amount,0) - coalesce(txn.amount,0)) > 1

现有LINQ实现 #

public async Task<List<TransactionMismatchDetails>> GetMismatchedTransactions()
{
    var tempHistory = await _context.Table_tempVwHistory 
        .Where(x => x.PremiumChange != 0)
        .ToListAsync();
    var amountSubTypes = await _context.Set<AmountSubType>()
        .ToListAsync();

    var rtr = (from r in tempHistory
               join ats in amountSubTypes on r.StepAlias equals ats.id
               group r by new { r.Id, r.Name, ats.AmountTypeId } into g
               select new
               {
                   g.Key.id,
                   g.Key.Name,
                   g.Key.AmountTypeId,
                   amount = g.Sum(x => x.PremiumChange)
               }).ToList();

    var txn = await (from t in _context.Set<Transaction>()
               join td in _context.Set<TransactionDetail>() on t.Id equals td.TransactionId
               join ats in _context.Set<AmountSubType>() on td.AmountSubTypeId equals ats.Id
               where ats.AmountTypeId != AmountType.TransactionFee.Id
               group td by new { t.Id, ats.AmountTypeId } into g
               select new
               {
                   g.Key.Id,
                   g.Key.AmountTypeId,
                   Amount = g.Sum(x => x.Amount)
               }).ToListAsync();

    return (from r in rtr
                  join t in txn on new { r.Id, r.AmountTypeId } equals new { t.Id, t.AmountTypeId } into rt
                  from t in rt.DefaultIfEmpty()
                  join risk in _context.Set<Risk>() on r.Id equals risk.id 
                  join amt in _context.Set<AmountType>() on r.AmountTypeId equals amt.Id
                  where r.Amount != t.Amount && Math.Abs(r.Amount - t.Amount) > 1
                  select new TransactionMismatchDetails
                  {
                      RiskId = risk.Id,
                      PolicyNumber = risk.Name,
                      AmountType = amt.Description,
                      BillingAmount = t != null ? t.Amount : 0,
                      RaterAmount = r.Amount
                  }).ToList();
}

问题解答 #

一、现有实现的正确性问题 #

你的LINQ实现和原SQL逻辑有几个关键差异，会导致结果不一致：

• JOIN类型错误：原SQL用的是FULL JOIN关联rtr和txn，但你用了LEFT JOIN（from t in rt.DefaultIfEmpty()），会丢失txn中有但rtr中没有的记录。
• 关联条件缺失：原SQL关联Risk的条件是r.id = rtr.Id or r.id = txn.Id，关联AmountType的条件是amt.id = rtr.AmountTypeId or amt.id = txn.AmountTypeId，但你只关联了rtr侧的ID，会漏掉txn侧对应的Risk和AmountType数据。
• Null值处理不当：原SQL用coalesce处理null值，你的LINQ中直接用r.Amount != t.Amount，当t为null时会引发异常，而且差值计算也没处理null情况。
• 提前拉取数据到内存：你把tempHistory和amountSubTypes提前ToListAsync()，如果数据量大会占用大量内存，原SQL是在数据库端完成所有计算的。

二、最优实现方式 #

1. 还原FULL JOIN逻辑

EF Core 3.x不直接支持FULL JOIN，需要用UNION模拟：先做LEFT JOIN，再做RIGHT JOIN（只取左侧没有的记录），最后合并结果。

2. 让查询在数据库端执行

所有逻辑用IQueryable组合，最后再ToListAsync()，让EF Core生成SQL在数据库执行，性能更优。

3. 修正关联与条件判断

处理Risk和AmountType的双条件关联，用??替代SQL的coalesce处理null值。

修正后的示例代码：

public async Task<List<TransactionMismatchDetails>> GetMismatchedTransactions()
{
    // 构建rtr子查询的IQueryable
    var rtrQuery = from r in _context.Table_tempVwHistory
                   where r.PremiumChange != 0
                   join ats in _context.AmountSubType on r.StepAlias equals ats.Id
                   group r by new { r.Id, r.Name, ats.AmountTypeId } into g
                   select new
                   {
                       Id = g.Key.Id,
                       Name = g.Key.Name,
                       AmountTypeId = g.Key.AmountTypeId,
                       Amount = g.Sum(x => x.PremiumChange)
                   };

    // 构建txn子查询的IQueryable
    var txnQuery = from t in _context.Transaction
                   join td in _context.TransactionDetail on t.Id equals td.TransactionId
                   join ats in _context.AmountSubType on td.AmountSubTypeId equals ats.Id
                   where ats.AmountTypeId != AmountType.TransactionFee.Id
                   group td by new { t.Id, ats.AmountTypeId } into g
                   select new
                   {
                       Id = g.Key.Id,
                       AmountTypeId = g.Key.AmountTypeId,
                       Amount = g.Sum(x => x.Amount)
                   };

    // 模拟FULL JOIN：LEFT JOIN + RIGHT JOIN（取右侧独有记录）
    var leftJoin = from r in rtrQuery
                   join t in txnQuery on new { r.Id, r.AmountTypeId } equals new { t.Id, t.AmountTypeId } into rt
                   from t in rt.DefaultIfEmpty()
                   select new
                   {
                       RtrId = r.Id,
                       RtrAmountTypeId = r.AmountTypeId,
                       RtrAmount = r.Amount,
                       TxnId = t?.Id,
                       TxnAmountTypeId = t?.AmountTypeId,
                       TxnAmount = t?.Amount ?? 0m
                   };

    var rightJoin = from t in txnQuery
                    join r in rtrQuery on new { t.Id, t.AmountTypeId } equals new { r.Id, r.AmountTypeId } into tr
                    from r in tr.DefaultIfEmpty()
                    where r == null
                    select new
                    {
                        RtrId = (int?)null,
                        RtrAmountTypeId = (string)null,
                        RtrAmount = 0m,
                        TxnId = t.Id,
                        TxnAmountTypeId = t.AmountTypeId,
                        TxnAmount = t.Amount
                    };

    var fullJoin = leftJoin.Union(rightJoin);

    // 关联Risk、AmountType并过滤结果
    var resultQuery = from j in fullJoin
                      join risk in _context.Risk on (j.RtrId ?? j.TxnId) equals risk.Id
                      join amt in _context.AmountType on (j.RtrAmountTypeId ?? j.TxnAmountTypeId) equals amt.Id
                      where Math.Abs(j.RtrAmount - j.TxnAmount) > 1
                      select new TransactionMismatchDetails
                      {
                          RiskId = risk.Id,
                          PolicyNumber = risk.Name,
                          AmountType = amt.Description,
                          BillingAmount = j.TxnAmount,
                          RaterAmount = j.RtrAmount
                      };

    return await resultQuery.ToListAsync();
}

4. 其他优化点

• 提前定义AmountType.TransactionFee.Id常量，避免重复访问静态属性
• 配置实体导航属性，用Include/ThenInclude简化关联代码
• 给查询涉及的字段（如Table_tempVwHistory.PremiumChange、TransactionDetail.TransactionId）添加索引，提升查询速度

三、DDD下的代码分层建议 #

按照DDD原则：

• Repository层：只负责封装原子性的数据访问逻辑（比如获取单个聚合根、按基础条件查询列表），核心是解耦领域模型和数据存储，不包含业务判断。
• 应用层Service：负责协调领域对象和Repository，实现完整的业务用例。你的这段逻辑是带有业务规则（金额差值>1）的查询，属于业务用例的一部分，应该放在应用层Service中。

如果这段查询需要被多个用例复用，也可以封装成Domain Service（如果涉及核心领域规则），但一般这种业务查询放在应用层更合适，因为它直接对应业务需求。

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