如何用Entity Framework实现三表关联分组求和

改进LINQ实现（优雅写法） #

你之前的LINQ分组逻辑有误——只把Manufacturer对象纳入分组，导致无法访问Sale表的计算字段。正确的做法是分组时保留计算所需的Sale数据，以下是两种简洁实现：

查询语法版 #

// 先定义DTO类（推荐，避免污染数据库实体）
public class ManufacturerRevenueDto
{
    public string Name { get; set; }
    public decimal Revenue { get; set; }
}

// LINQ查询
var manufacturerRevenues = from s in _context.Sale
                           join c in _context.Car on s.CarId equals c.Id
                           join m in _context.Manufacturer on c.ManufacturerId equals m.Id
                           // 分组时携带计算需要的Sale字段
                           group new { s.Price, s.NumberOfCars } by m.Name into g
                           select new ManufacturerRevenueDto
                           {
                               Name = g.Key,
                               Revenue = g.Sum(item => item.Price * item.NumberOfCars)
                           };

方法链语法版 #

var manufacturerRevenues = _context.Sale
    .Join(_context.Car, sale => sale.CarId, car => car.Id, (sale, car) => new { sale, car })
    .Join(_context.Manufacturer, sc => sc.car.ManufacturerId, m => m.Id, (sc, m) => new { sc.sale.Price, sc.sale.NumberOfCars, m.Name })
    .GroupBy(result => result.Name)
    .Select(group => new ManufacturerRevenueDto
    {
        Name = group.Key,
        Revenue = group.Sum(item => item.Price * item.NumberOfCars)
    });

原生SQL报错的修复 #

你用FromSql时的错误，是因为直接将结果映射到Manufacturer实体，但SQL查询未返回实体必填的Id主键字段。解决方式是用DTO/匿名类接收结果，而非直接绑定数据库实体：

var sqlQuery = @"select 
                    sum(s.Price * s.NumberOfCars) as Revenue, m.Name
                 from Sale as s
                 join Car as c on c.Id = s.CarId
                 join Manufacturer as m on m.Id = c.ManufacturerId
                 group by m.Name";

var manufacturerRevenues = _context.Set<ManufacturerRevenueDto>()
    .FromSqlRaw(sqlQuery)
    .ToList();

注：EF Core 3.0+推荐使用FromSqlRaw，需保证DTO属性名与SQL返回列名完全匹配。

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