Linq to SQL迁移EF Core 2.0分组查询空引用异常求助

从你的异常栈信息就能精准定位问题根源：左连接后出现null对象，访问其属性时触发了空引用异常。

你用j7.DefaultIfEmpty()实现左连接，这意味着当tblemployee_incidents的dnCategory3在tbl_config_event_categories中找不到匹配记录时，c3会是null。Linq to SQL在处理这种场景下的Sum时，会自动把null的属性值当作0来计算，但EF Core 2.0并没有做这个自动兼容——当ity为null时，直接访问ity.tnMobileWeight就抛出了NullReferenceException。

修复方案 #

只需要在Sum方法里添加null判断，用null合并运算符（??）把null值替换为0即可：

int lnScore1 = 0; int lnScore2 = 0; int lnScore3 = 0;
try {
    var loqs = from r in _context.tblemployee_incidents
               join c in _context.tblemployees on r.diEmployeeID equals c.diID
               join c3 in _context.tbl_config_event_categories on r.dnCategory3 equals c3.tiID into j7
               from c3 in j7.DefaultIfEmpty()
               where c.dbDeleted == false
               where r.diAppID == 1 && r.dbDeleted == false
               group c3 by new { c.diID } into g
               select new {
                   dnEmployee = g.Key.diID,
                   // 添加null判断，null时取0避免空引用
                   dnWeight = g.Sum(ity => ity?.tnMobileWeight ?? 0)
               };

    lnScore1 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight > 0 && x.dnWeight < 30));
    lnScore2 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 30 && x.dnWeight < 40));
    lnScore3 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 40));
} catch (Exception ex) { }

额外优化建议 #

你当前的代码会触发三次数据库查询（每次Count都会执行一次），可以提前把查询结果缓存到内存中，既提升性能也减少数据库连接开销：

// 先把查询结果加载到内存
var loqsList = loqs.ToList();
lnScore1 = loqsList.Count(x => x.dnEmployee > 0 && (x.dnWeight > 0 && x.dnWeight < 30));
lnScore2 = loqsList.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 30 && x.dnWeight < 40));
lnScore3 = loqsList.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 40));

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