如何编写LINQ实现CaseProductLinks与Products关联分组统计查询

错误原因 #

• 关联表匹配错误：原SQL逻辑是CaseProductLinks关联Products表，现有代码错误关联了Cases表，关联字段也不符合业务需求
• 缺失分组逻辑：原SQL存在GROUP BY p.ProductID分组规则，现有代码未执行分组操作直接计数，会导致EF无法翻译查询，抛出System.InvalidOperationException异常

修正后的代码 #

查询语法（和你原有写法风格一致） #

var objs = from c in _db.CaseProductLinks
           join p in _db.Products on c.ProductID equals p.ProductID
           group c by new { p.ProductID, p.ProductName } into g
           select new S_Link
           {
               ProductID = g.Key.ProductID,
               ProductName = g.Key.ProductName,
               Frequency = g.Count()
           };

方法语法参考 #

var objs = _db.CaseProductLinks
    .Join(
        _db.Products,
        link => link.ProductID,
        product => product.ProductID,
        (link, product) => new { link.CaseID, product.ProductID, product.ProductName }
    )
    .GroupBy(item => new { item.ProductID, item.ProductName })
    .Select(group => new S_Link
    {
        ProductID = group.Key.ProductID,
        ProductName = group.Key.ProductName,
        Frequency = group.Count()
    });

补充说明 #

如果你的实体已经配置了Products到CaseProductLinks的导航属性，可以用更简洁的写法，EF会自动生成关联和统计逻辑：

var objs = _db.Products
    .Select(p => new S_Link
    {
        ProductID = p.ProductID,
        ProductName = p.ProductName,
        Frequency = p.CaseProductLinks.Count()
    });

如果CaseProductLinks表中CaseID允许为NULL，可将计数逻辑改为g.Count(x => x.CaseID != null)和原SQL的COUNT(c.CaseID)逻辑完全一致

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