请求协助将指定SQL查询转换为LINQ查询并排查无结果问题

原SQL查询 #

SELECT EconCode.EconCodeId, 
       EconCode.EconCodeName, 
       EconCode.EconIncomeAmt, 
       EconCode.EconRecExpAmt, 
       EconCode.EconCapExpAmt, 
       FundCode.FundCodeId
FROM EconCode 
LEFT OUTER JOIN FundCode 
   ON EconCode.FundCodeId = FundCode.FundCodeId
GROUP BY EconCode.EconCodeId, 
         EconCode.EconCodeName, 
         EconCode.EconIncomeAmt, 
         EconCode.EconRecExpAmt, 
         EconCode.EconCapExpAmt, 
         FundCode.FundCodeId

用户尝试的LINQ代码 #

var query = from econ in _context.EconCode
            join fund in _context.FundCode on econ.FundCodeId equals fund.FundCodeId
            group new { econ, fund.FundCodeId } by new { fund.FundCodeName, econ.FundCodeId, econ.EconCodeId, econ.EconCodeName, econ.EconIncomeAmt, econ.EconRecExpAmt, econ.EconCapExpAmt  } into g
            select new
            {
                fundcodeId = g.Key.FundCodeId,
                fundCodeName = g.Key.FundCodeName,
                econCodeId = g.Key.EconCodeId,
                econCodeName = g.Key.EconCodeName,
                econIncomeAmt = g.Key.EconIncomeAmt,
                econRecExpAmt = g.Key.EconRecExpAmt,
                econCapExpAmt = g.Key.EconCapExpAmt,
            };

问题排查 #

1. JOIN类型错误：原SQL是LEFT OUTER JOIN，但用户写的LINQ是默认的INNER JOIN，会过滤掉EconCode中没有匹配FundCode的记录，直接导致结果缺失。
2. 分组字段不匹配：原SQL的GROUP BY中没有FundCode.FundCodeName，但用户的分组键里加入了这个字段，导致分组逻辑和原SQL完全不一致——如果FundCodeName存在不同值，会拆分原本应该合并的组，甚至因为无匹配组返回空结果。
3. 多余字段查询：原SQL的SELECT结果里没有FundCodeName，但用户的LINQ中额外查询了该字段，不符合原需求。

修正后的LINQ代码 #

完全匹配原SQL逻辑（LEFT JOIN + 对应分组） #

var query = from econ in _context.EconCode
            join fund in _context.FundCode on econ.FundCodeId equals fund.FundCodeId into fundJoin
            from fund in fundJoin.DefaultIfEmpty() // 实现LEFT OUTER JOIN
            group new 
            { 
                econ.EconCodeId, 
                econ.EconCodeName, 
                econ.EconIncomeAmt, 
                econ.EconRecExpAmt, 
                econ.EconCapExpAmt, 
                FundCodeId = fund?.FundCodeId 
            } by new 
            { 
                econ.EconCodeId, 
                econ.EconCodeName, 
                econ.EconIncomeAmt, 
                econ.EconRecExpAmt, 
                econ.EconCapExpAmt, 
                FundCodeId = fund?.FundCodeId 
            } into g
            select new
            {
                econCodeId = g.Key.EconCodeId,
                econCodeName = g.Key.EconCodeName,
                econIncomeAmt = g.Key.EconIncomeAmt,
                econRecExpAmt = g.Key.EconRecExpAmt,
                econCapExpAmt = g.Key.EconCapExpAmt,
                fundCodeId = g.Key.FundCodeId
            };

简化版（若原SQL分组仅为去重） #

如果原SQL的GROUP BY只是为了去重，且EconCode和FundCode关联后无重复记录，可以直接去掉分组，简化为：

var query = from econ in _context.EconCode
            join fund in _context.FundCode on econ.FundCodeId equals fund.FundCodeId into fundJoin
            from fund in fundJoin.DefaultIfEmpty()
            select new
            {
                econ.EconCodeId,
                econ.EconCodeName,
                econ.EconIncomeAmt,
                econ.EconRecExpAmt,
                econ.EconCapExpAmt,
                FundCodeId = fund?.FundCodeId
            };

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