Linq与SQL的Distinct结果不一致，求Linq代码问题排查建议

原始SQL语句 #

select distinct         substring(MFSOAM_Grp, 3, 3) a, MFSGRP_Description
from                    MFSOAM
                        left join MFCOM on MFCOM_Code = substring(MFSOAM_Grp, 3, 3)
                        left join MFSGRP on substring(MFSGRP_Code, 3, 3) = substring(MFSOAM_Grp, 3, 3)
where                   left(MFSOAM_Grp,2)= '03'
                        and MFCOM_Code is null
order by                substring(MFSOAM_Grp, 3, 3)

问题Linq代码 #

var query = (from a in _context.Mfsoams
                          join b in _context.Mfcoms on a.MfsoamGrp.Substring(3, 3) equals b.MfcomCode into ab
                          from bresult in ab.DefaultIfEmpty()
                          join c in _context.Mfsgrps on a.MfsoamGrp.Substring(3, 3) equals c.MfsgrpCode.Substring(3, 3) into bc
                          from cresult in bc.DefaultIfEmpty()
                          where a.MfsoamGrp.Substring(0, 2) == "03" && bresult.MfcomCode == null
                          orderby a.MfsoamGrp.Substring(3, 3)
                          select new
                          {
                              Group = a.MfsoamGrp.Substring(3, 3)
                          }).Distinct().ToList();

核心问题分析 #

1. 查询结果结构不匹配：SQL返回substring(MFSOAM_Grp,3,3)和MFSGRP_Description两个字段，但Linq仅选择了Group字段，丢失了描述信息。
2. Distinct逻辑不一致：SQL是对两个字段的组合去重，而Linq仅对单个Group字段去重，会导致重复的Group+Description组合被保留，结果数量和内容与SQL不符。
3. 空引用风险与过滤逻辑偏差：原Linq中bresult.MfcomCode == null的写法存在问题——当左连接MFCOM无匹配结果时，bresult本身为null，访问其属性会触发NullReferenceException，且和SQL中MFCOM_Code is null的逻辑不完全等价。

修正后的Linq代码 #

var query = (from a in _context.Mfsoams
             join b in _context.Mfcoms on a.MfsoamGrp.Substring(3, 3) equals b.MfcomCode into ab
             from bresult in ab.DefaultIfEmpty()
             join c in _context.Mfsgrps on a.MfsoamGrp.Substring(3, 3) equals c.MfsgrpCode.Substring(3, 3) into bc
             from cresult in bc.DefaultIfEmpty()
             where a.MfsoamGrp.Substring(0, 2) == "03" && bresult == null
             orderby a.MfsoamGrp.Substring(3, 3)
             select new
             {
                 Group = a.MfsoamGrp.Substring(3, 3),
                 Description = cresult?.MfsgrpDescription // 用空传播运算符处理无匹配的情况
             }).Distinct().ToList();

修正说明 #

• 修复过滤条件：将bresult.MfcomCode == null改为bresult == null，既避免空引用异常，又完全匹配SQL中MFCOM_Code is null的逻辑。
• 补充查询字段：添加Description字段对应SQL中的MFSGRP_Description，确保结果结构一致。
• 对齐去重逻辑：对Group和Description的组合执行Distinct，和SQL的去重逻辑完全一致，保证结果数量和内容匹配。

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