Using .NET Core nested LINQ instruction in migration from SQL Server to MySQL

Question

In the web application I'm developing, I'm using .NET Core 3.1 and SQL Server in a Windows environment.

In the support code of a cshtml page, I've created a LINQ query to get information from multiple tables of the database.
Here is the code snippet, that has worked fine as far as I used SQL Server.

var cupEditions = await _context.CupEditions
   .Where(c => c.CupID == nationalCupId)
   .OrderBy(c => c.Season)
   .Select(c => new {
      c.ID,
      c.Season,
      Final = c.CupDays.Single(cd => cd.Turn == 0).Matches.First()
   })
   .ToListAsync();

To pass from developer to a production environment, being this a hobby application, I've decided to move the database to a cheaper MySQL database, moving (with some headache) from the SQL Server EF Core to the MySQL EF Core.

The instruction above, after the transition to MySQL EF Core package (I don't know, but I can verify if needed, if it was the same with the SQL Server EF Core package), generates the following query:

FROM `CupEditions` AS `c`
      OUTER APPLY (
          SELECT `c0`.`ID`, `c0`.`AwayTeamID`, `c0`.`CupDayID`, `c0`.`Data`, `c0`.`HomeTeamID`, `c0`.`gridID`
          FROM `CupMatches` AS `c0`
          WHERE (
              SELECT `c1`.`ID`
              FROM `CupDays` AS `c1`
              WHERE (`c`.`ID` = `c1`.`CupEditionID`) AND (`c1`.`Turn` = 0)
              LIMIT 1) IS NOT NULL AND (((
              SELECT `c2`.`ID`
              FROM `CupDays` AS `c2`
              WHERE (`c`.`ID` = `c2`.`CupEditionID`) AND (`c2`.`Turn` = 0)
              LIMIT 1) = `c0`.`CupDayID`) OR ((
              SELECT `c2`.`ID`
              FROM `CupDays` AS `c2`
              WHERE (`c`.`ID` = `c2`.`CupEditionID`) AND (`c2`.`Turn` = 0)
              LIMIT 1) IS NULL AND `c0`.`CupDayID` IS NULL))
          LIMIT 1
      ) AS `t`
      WHERE `c`.`CupID` = @__nationalCupId_0
      ORDER BY `c`.`Season`

I cannot judge if there are better and more efficient ways to make this query. Anyway, MySQL seems not to support the OUTER APPLY instruction, so the query fails generating an exception. The way I found to solve this issue is to make multiple requests to the database instead of a single LINQ query, a solution that seems quite inelegant.

Does anyone know how to design better the LINQ code to increase efficiency and readability, making it to work with MySQL?

Answer 1

First off, there are two MySQL database providers and looks like you are using MySql.EntityFrameworkCore. In many cases the other one Pomelo.EntityFrameworkCore.MySql works better and just switching to it could solve the issue (unfortunately the LINQ idea is dead from the introduction of IQueryable<T> - the query compiles, but does not execute at run time).

However, there are some rules for writing LINQ queries in order to avoid (in most of the cases) problems like this. One is the avoid constructs like First, FirstOrDefault, Single etc. followed by member access (like .Matches in your query). Instead, use appropriate Select / SelectMany methods first and leave the row limiting operator last.

Applying it to your query would be something like this

Final = c.CupDays
    .Where(cd => cd.Turn == 0)
    .SelectMany(cd => cd.Matches)
    .Single()

but both providers (and in general all providers) should be able to translate it since behind the scenes it is based on simple left outer joins.