c# - 在 linq 查询中选择 max 或重写为方法链语法

Question

我设法打开了这个 SQL 查询：

SELECT c.carId, c.Codename, count(c.CarId) as [CarCount],
  FROM [DbEfTesting].[dbo].[Cars] c
  left join Accessories a on c.CarId = a.CarId
  left join CarsPeople cp on cp.CarId = c.CarId
  left join People p on cp.PersonId = p.PersonId
  group by c.CarId, c.Codename

进入 LINQ 查询：

var x = from c in _context.Cars
    join a in _context.Accessories on c.CarId equals a.Car.CarId
    join j in _context.CarsPeople on c.CarId equals j.CarId
    join p in _context.People on j.PersonId equals p.PersonId
    group c by new { c.CarId, c.Codename } into g
    select new VMCarAggregate()
    {
        CarId = g.Key.CarId,
        Codename = g.Key.Codename,
        CarCount = g.Count()
    };

但现在我迷失了试图包含一个最大值，例如 SQL：

SELECT c.carId, c.Codename, count(c.CarId) as [CarCount], max(a.AccessoryId) ...

我用谷歌搜索了很多方法语法的答案。如果我使用方法链语法，我知道我可以这样做：

_context.Accessories.Max(a => a.AccessoryId);

但我不知道如何执行group byin 方法链语法，所以：

如何将该查询转换为方法语法？

或者

如何以 LINQ 查询格式在最大 a.AccessoryId 上注入选择？

标签： c#entity-frameworklinq

Answer 1

尝试以下代码一次：

var x = from c in _context.Cars
                    join a in _context.Accessories equals a.Car.CarId
                    join j in _context.CarsPeople on c.CarId equals j.CarId
                    join p in _context.People on j.PersonId equals p.PersonId
                    group new { c.CarId, c.Codename, a.AccesoryId } by new { c.CarId, c.Codename } into g
                    select new
                    {
                        CarId = g.Key.CarId,
                        Codename = g.Key.Codename,
                        CarCount = g.Count(),
                        MaxAccesory = g.Max(z => z.AccesoryId)
                    };