[BUG]This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[EthanZqh]

The MySQL server version is community-based mysql-8.0.30-winx64 or community-based mysql-9.0.1-winx64
Limit&IN/ALL/ANY/OME subqueries are not yet supported
Solution problem: 1. Which MySQL version supports the "Limit&IN/ALL/ANY/OME subquery" query?
2. It is to modify the nested source code query, but I cannot modify the Elsa source code. I am referencing the Elsa Dll dynamic library.

[sfmskywalker]

I tried with Docker container mysql:9.1.0, which works well.
If you need to support an older version of MySql, you can always generate your own EF Core migration files. Basically, copy the Elsa.EntityFrameworkCore.MySql project, rename the project and its classes and methods to be your own, and update the MySqlDesignTimeDbContextFactory class to use the version of MySQL that you want to use.

[sfmskywalker]

I just noticed you had already posted this exact same issue here: #6091
I will close that one, since this issue now contains some feedback.

Please refrain from posting the same issues multiple times. Thank you.

[EthanZqh]

I tried with Docker container mysql:9.1.0, which works well. If you need to support an older version of MySql, you can always generate your own EF Core migration files. Basically, copy the Elsa.EntityFrameworkCore.MySql project, rename the project and its classes and methods to be your own, and update the MySqlDesignTimeDbContextFactory class to use the version of MySQL that you want to use.

Upgrading MySQL to the community version MySQL-9.0.1-winx64 does not solve the problem and supports' Limit&IN/ALL/ANY/OME subqueries'. It may be necessary to change the source code nesting to solve the problem,
The code is as follows: DELETE w
FROM WorkflowInboxMessages AS w
WHERE w.Id IN (
SELECT w0.Id
FROM (select w1.Id from WorkflowInboxMessages AS w1
WHERE w1.ExpiresAt <= @__now_0
LIMIT 3 OFFSET 2) AS w0
)
I am unable to modify the source code. I hope the teacher can modify the source code to solve this problem. Thank you!

[sfmskywalker]

Hi @EthanZqh , you don't need to change the source code. Instead, you can add your own class library project that contains the necessary migrations.

[EthanZqh]

Hi @EthanZqh , you don't need to change the source code. Instead, you can add your own class library project that contains the necessary migrations.

I copied Elsa Entity Framework Core. MySQL project, and update MySqlDesignTimeVNet Factory class to use the MySQL version I want to use. How to generate EF Core migration files?