c# - EF.Core 3.x生成窗口函数而不是JOIN，导致MySQL语法错误

Question

运行以下 EF Core 查询:

var groupData = await _dbContext.Groups.AsNoTracking()
    .Where(g => g.Id == groupId)
    .Select(g => new
    {
        /* ...some other fields are queried here... */
        ActiveLab = g.ActiveLabs.FirstOrDefault(al => al.LabId == labId)
    })
    .FirstAsync(cancellationToken);

导致此错误:

MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'(PARTITION BY `l`.`GroupId` ORDER BY `l`.`GroupId`, `l`.`LabId`) AS `row`'
at line 6

检查生成的 SQL 发现 EF 由于某种原因插入了 PARTITION操作说明:

SELECT `g`.`Name`, `t0`.`GroupId`, `t0`.`LabId`, `t0`.`StartTime`
FROM `Groups` AS `g`
LEFT JOIN (
    SELECT `t`.`GroupId`, `t`.`LabId`, `t`.`StartTime`
    FROM (
        SELECT `l`.`GroupId`, `l`.`LabId`, `l`.`StartTime`, ROW_NUMBER() OVER(PARTITION BY `l`.`GroupId` ORDER BY `l`.`GroupId`, `l`.`LabId`) AS `row`
        FROM `ActiveLabs` AS `l`
        WHERE `l`.`LabId` = @__labId_1
    ) AS `t`
    WHERE `t`.`row` <= 1
) AS `t0` ON `g`.`Id` = `t0`.`GroupId`
WHERE `g`.`Id` = @__groupId_0
LIMIT 1

我宁愿期待这样的查询:

SELECT `g`.`Name`, `l`.`GroupId`, `l`.`LabId`, `l`.`StartTime`
FROM `Groups` AS `g`
LEFT JOIN `ActiveLabs` AS `l`
ON `l`.`GroupId` = `g`.`Id`
WHERE `l`.`LabId` = @__labId_1 AND `g`.`Id` = @__groupId_0
LIMIT 1

为什么EF生成如此复杂的查询，当一个简单的JOIN在 ActiveLabs就够了吗？

我正在使用 EF Core 3.1.2、Pomelo MySQL 3.1.1 和 MySQL 5.7.14 进行测试。

我的数据库如下所示: 我有两个表 Groups

+----+--------+
| Id |  Name  |
+----+--------+
|  1 | Group1 |
|  2 | Group2 |
+----+--------+

和 ActiveLabs

+---------+-------+----------------------------+
| GroupId | LabId |         StartTime          |
+---------+-------+----------------------------+
|       1 |     1 | 2020-03-01 00:00:00.000000 |
|       2 |     1 | 2020-03-01 00:00:00.000000 |
|       1 |     2 | 2020-03-08 00:00:00.000000 |
+---------+-------+----------------------------+

后者代表多对多关系，它跟踪哪个实验室对哪个组处于事件状态。因此，一个 Group对象具有导航属性 ActiveLabs ，它指向该组的活跃实验室。类/表结构和外键在所有用例中都是正确的并且运行良好。

编辑:
看起来像 PARTITION MySQL 5.7.14 完全不支持(Pomelo 的 GitHub 存储库上的 related issue)。升级到 MySQL 8.0 消除了错误消息，查询现在可以工作了；然而， 我仍然不明白为什么 EF 会生成 PARTITION (窗口函数)声明 .

Answer 1

实际上，EF Core 根据您的 LINQ 查询生成了正确的 SQL，并且它做得很好。
为了更接近预期的 SQL，您可以通过以下方式重写此查询:

var query = 
    from g in _dbContext.Groups
    from al in g.ActiveLabs
    where g.Id == groupId && al.LabId == labId
    select new 
    {
        /* ...some other fields are queried here... */
        ActiveLab = al
    };

var groupDate = await query.FirstAsync(cancellationToken);

一点解释它是如何工作的
它可以不是直接的 EF Core 翻译技术，但可以非常相似
首先 Translator 生成您定义的所有需要​​的连接 - 称之为主查询。然后，在投影生成(Select)过程中，Translator 发现您请求了 FirstOrDefault来自一些相关实体。但是主查询已经定义，我们可以做些什么来选择这个第一个 child 并且不损害主查询结果 - 使 OUTER APPLY JOIN 到有限的记录集。但是OUTER APPLY JOIN没有那么有效，所以我们可以尝试将OUTER APPLY JOIN转换为LEFT JOIN。对于这种特定情况，可以使用 Window Function 轻松完成 - 瞧。