After upgrading from 7.0.0 to 8.0.2, SQL translation results are changed and this version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

[karuakun]

Steps to reproduce

After updating Pomelo.EntityFrameworkCore.MySql from 7.0.0 to 8.0.2, the following code now exits abnormally
The reason is that the SQL that used to use the EXISTS clause has been translated as an IN clause, and now uses the LIMIT clause in the IN clause.
Is there an option to revert to the previous (7.0.0) behavior?

docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.0

public class Book
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
}

public class AppDataContext(DbContextOptions<AppDataContext> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
}

public class Runner(AppDataContext appDataContext)
{
    public async Task<int> RunAsync()
    {
        var books = await appDataContext
            .Books
            .Where(b => 
                appDataContext
                    .Books
                    .Where(bb => bb.Name.Contains("C#"))
                    .Select(bb => b.Id)
                    .OrderBy(bb => b.Id)
                    .Skip(0)
                    .Take(100)
                    .Contains(b.Id))
            .ToArrayAsync();
        foreach (var book in books)
        {
            Console.WriteLine($"{book.Id}:{book.Name}");
        }
        return 0;
    }
}

The issue

Pomelo.EntityFrameworkCore.MySql 8.0.2

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `b`.`Id`, `b`.`Name`
      FROM `Books` AS `b`
      WHERE `b`.`Id` IN (
          SELECT `b`.`Id`
          FROM `Books` AS `b0`
          WHERE `b0`.`Name` LIKE '%C#%'
          ORDER BY `b`.`Id`
          LIMIT 100 OFFSET 0
      )
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'AppDataContext'.
      MySqlConnector.MySqlException (0x80004005): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
         at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894

Pomelo.EntityFrameworkCore.MySql 7.0.0

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (81ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `b`.`Id`, `b`.`Name`
      FROM `Books` AS `b`
      WHERE EXISTS (
          SELECT 1
          FROM (
              SELECT `b`.`Id`, `b0`.`Id` AS `Id0`
              FROM `Books` AS `b0`
              WHERE `b0`.`Name` LIKE '%C#%'
              ORDER BY `b`.`Id`
              LIMIT 100 OFFSET 0
          ) AS `t`)

Further technical details

MySQL version: 8.0
Operating system: docker / windows 11 10.0.22631
Pomelo.EntityFrameworkCore.MySql version: 7.0.0 / 8.0.2
Microsoft.AspNetCore.App version:

Other details about my project setup:

[karuakun]

Ah, the effects of this disruptive change.
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#better-use-of-in-queries

But I think I need another workaround, since MySQL does not allow the use of LIMIT clauses in subqueries of IN clauses

[roji]

@lauxjpn if there's a MySQL limitation around LIMIT within IN subqueries, it should be possible to write a post-processing visitor to convert such subqueries to EXISTS ones... The EF query pipeline may have some issues making this more difficult than it should be - let me know if you need any assistance.

[RichardArling]

This issue also effects the openiddict package when using the PruneAsync function. ( The Pomelo provider is recommended by the author)
https://github.com/openiddict/openiddict-core

[HubTonight]

@lauxjpn if there's a MySQL limitation around LIMIT within IN subqueries, it should be possible to write a post-processing visitor to convert such subqueries to EXISTS ones... The EF query pipeline may have some issues making this more difficult than it should be - let me know if you need any assistance.

@roji So you can give me a code example to solve this problem, thank you very much.