Linq GroupJoin生成的SQL忽略条件，如何生成含LEFT JOIN与GROUP BY的SQL？

问题 #

尝试使用LINQ to SQL查询语法统计2023年11月14日单日每小时的未关闭工单数量，结果正确但生成的SQL不符合预期。

LinqPad查询代码：

int[] hours = Enumerable.Range(0, 24).ToArray();
DateTime Nov = new DateTime(2023, 11, 14);
var q = (
            from h in hours
            join t in Tickets on h equals t.CreatedAt.Hour into ht
            from lht in ht.Where(tk => (tk.CreatedAt.Date == Nov.Date) && (tk.OrganizationId == 1) && (!tk.IsDeleted) ).DefaultIfEmpty()
            group lht by h into ftable
            select new {id = ftable.Key, count  = ftable.Count(f => f != null)}
        ).Dump();

生成的SQL却是全表查询：

SELECT `t`.`Id`, `t`.`ClosedAt`, `t`.`CreatedAt`, `t`.`CreatedBy`, `t`.`isDeleted`, `t`.`LastUpdatedAt`, `t`.`LastUpdatedBy`, `t`.`LinkedSessionId`, `t`.`OrganizationId`, `t`.`Priority`, `t`.`PropertyId`, `t`.`PublicId`, `t`.`RecipientId`, `t`.`Source`, `t`.`Status`, `t`.`Subject`, `t`.`TicketId`
FROM `tickets` AS `t`

该GroupJoin语句会拉取整张表后在内存中处理分组和过滤条件，效率极低。需要解释此行为，并生成包含LEFT JOIN、WHERE及GROUP BY的标准SQL。

行为原因解释 #

• 客户端集合触发全表加载：hours是内存中的数组（由Enumerable.Range(0,24).ToArray()生成），属于客户端集合。LINQ to SQL无法将客户端集合与数据库表的关联逻辑完全转换为SQL，只能先把tickets表的所有数据拉取到内存，再在客户端完成join、过滤和分组操作。
• 过滤逻辑位置错误：过滤条件写在ht.Where(...)中，这是在GroupJoin后的子集合上做过滤，但GroupJoin本身因为关联了客户端集合已经触发全表加载，过滤逻辑自然只能在内存中执行，无法下推到数据库。

修正后的查询代码 #

方法1：构造数据库可识别的小时范围，提前过滤工单 #

将小时集合转换为数据库可识别的查询源，同时把过滤条件提前应用到工单表上，让数据库先完成过滤和关联：

DateTime targetDate = new DateTime(2023, 11, 14);
DateTime nextDay = targetDate.AddDays(1);

// 构造数据库端可解析的小时范围查询
var hoursQuery = Enumerable.Range(0,24).Select(h => new { Hour = h }).AsQueryable();

var q = (
    from h in hoursQuery
    join t in Tickets.Where(tk => tk.CreatedAt >= targetDate 
                                  && tk.CreatedAt < nextDay
                                  && tk.OrganizationId == 1 
                                  && !tk.IsDeleted)
        on h.Hour equals t.CreatedAt.Hour into ht
    from lht in ht.DefaultIfEmpty()
    group lht by h.Hour into g
    select new {
        Hour = g.Key,
        UnclosedTicketCount = g.Count(t => t != null)
    }
).Dump();

方法2：先聚合工单数据，再关联小时范围（更高效） #

先在数据库端按小时聚合符合条件的工单，再在内存中与全24小时做匹配，减少数据库返回的数据量：

DateTime targetDate = new DateTime(2023, 11, 14);
DateTime nextDay = targetDate.AddDays(1);

// 数据库端聚合小时工单数量
var hourlyTicketCounts = (
    from t in Tickets
    where t.CreatedAt >= targetDate && t.CreatedAt < nextDay
          && t.OrganizationId == 1 && !t.IsDeleted
    group t by t.CreatedAt.Hour into g
    select new { Hour = g.Key, Count = g.Count() }
).ToDictionary(k => k.Hour, v => v.Count);

// 匹配全24小时，补全0值
var result = Enumerable.Range(0,24)
    .Select(h => new {
        Hour = h,
        UnclosedTicketCount = hourlyTicketCounts.TryGetValue(h, out var count) ? count : 0
    })
    .Dump();

生成的标准SQL示例（对应方法1） #

会生成包含LEFT JOIN、WHERE、GROUP BY的高效SQL：

SELECT `h`.`Hour`, COUNT(`t`.`Id`) AS `UnclosedTicketCount`
FROM (
    SELECT 0 AS `Hour` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL 
    SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL 
    SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS `h`
LEFT JOIN `tickets` AS `t` ON `h`.`Hour` = DATEPART(hour, `t`.`CreatedAt`)
WHERE `t`.`CreatedAt` >= '2023-11-14 00:00:00' AND `t`.`CreatedAt` < '2023-11-15 00:00:00'
  AND `t`.`OrganizationId` = 1 AND `t`.`IsDeleted` = 0
GROUP BY `h`.`Hour`

内容的提问来源于stack exchange，提问作者Yehia A.Salam