如何用Entity Framework方法语法重写PostgreSQL分组求和查询？

原PostgreSQL查询 #

select id, Sum(yellow_hour) "yellow_hour"
from my_table
where date_part('day', date::timestamptz - '2022-10-30 00:00:00.000 +0200'::timestamptz) > 0
group by id
order by yellow_hour desc 
limit 20

我的尝试代码 #

var sensors = dbContext.mytable
                       .Where(x => x.Date >= instantStartDate && x.Date <= instantEndDate)
                       .GroupBy(x => x.id)
                       .ToList();

报错信息 #

无法转换给定的'GroupBy'模式。请在'GroupBy'之前调用'AsEnumerable'以在客户端评估它。

解决方案 #

报错原因是EF无法直接将未做投影处理的GroupBy集合转换为SQL，需要在分组后明确提取聚合结果，同时对齐原SQL的逻辑：

1. 原SQL的date_part条件等价于筛选date晚于指定时间的数据，直接用时间比较更易被EF正确翻译
2. 分组后必须通过Select提取分组键和聚合值，再执行排序、分页操作

正确代码如下：

// 定义原SQL中的目标时间
var targetDateTime = DateTimeOffset.Parse("2022-10-30 00:00:00.000 +0200");

var sensors = dbContext.mytable
    .Where(x => x.Date > targetDateTime) // 匹配原SQL的where条件
    .GroupBy(x => x.id)
    .Select(group => new 
    {
        id = group.Key,
        yellow_hour = group.Sum(item => item.yellow_hour)
    })
    .OrderByDescending(result => result.yellow_hour) // 按聚合值降序
    .Take(20) // 对应limit 20
    .ToList();

说明：

• 避免直接对GroupBy结果调用ToList，这会强制在客户端分组，效率远低于数据库端分组
• 通过Select明确聚合逻辑，EF能正确将整个查询翻译成对应的PostgreSQL语句，保证操作在数据库端完成

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