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?

score 1 · Answer 1 · answered Oct 23 '21 at 10:20

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.

Thank you for your suggestion. As indicated also here https://github.com/dotnet/efcore/issues/23932#issuecomment-764943429 the nested selects are not supported. Unfortunately it seems I cannot adopt Pomelo's package because I found other conflict with Hangfire (another package I'm using) that I don't know how to solve (I will try to manage it when I will move to .net core 5.0). For now, I'm just splitting the query in multiple linq queries, that for sure is not an efficient solution. — Leonardo Daga, Oct 31 '21 at 10:02
Hmm, I don't see anything about nested selects there. They speak about split subqueries, which is different. Also I've tried the `SelectMany` based approach and it's get translated to SQL by the Oracle provider. The only thing I can't test is whether it actually executes successfully inside database. Don't trust everything people write in these posts, always try yourself, moreover you have the full test environment which others cannot always reproduce. — Ivan Stoev, Oct 31 '21 at 10:12

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

1 Answers1