EF.Core 3.x generates window function instead of JOIN, leading to MySQL syntax error

Question

Running the following EF Core query:

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);

results in this error:

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

Inspecting the generated SQL yields that EF for some reason inserts a PARTITION instruction:

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

I would rather have expected a query like this:

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

Why does EF generate such a complex query, when a simple JOIN on ActiveLabs would suffice?

I am testing with EF Core 3.1.2, Pomelo MySQL 3.1.1 and MySQL 5.7.14.

---

My database looks as follows: I have two tables Groups

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

and 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 |
+---------+-------+----------------------------+

The latter represents a many-to-many relationship, which keeps track which lab is active for which group. Thus, a Group object has a navigation property ActiveLabs, which points to the active labs of that group. The class/table structure and foreign keys are correct and work well in all use cases.

---

Edit: Looks like PARTITION is not supported by MySQL 5.7.14 at all (related issue on Pomelo's GitHub repository). Upgrading to MySQL 8.0 gets rid of the error message, the query works now; however, I still don't get why EF generates the PARTITION (window function) statement.

Answer 1

Actually EF Core generated correct SQL based on your LINQ query and it is doing that very well.

To be more closer to the expected SQL, you can rewrite this query in the following way:

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);

A little explanation how it works

It can be not direct EF Core translation technique, but it can be very similar

At first Translator generates all needed joins which you have defined - call it Main Query. Then, during projection generation (Select), Translator found that you have requested FirstOrDefault from some related entities. But Main Query is already defined, what we can do to select this first child and do not harm Main Query result - make OUTER APPLY JOIN to limited recordset. But OUTER APPLY JOIN is not so effective, so we can try to convert OUTER APPLY JOIN to LEFT JOIN. For this specific case it can be done easily with Window Function - voila.