MySqlException with EF Core Code First and LINQ nested query

[DotCat1985]

Hi,
I developed the DB context for MySQL 5.7.33 and entity models using Entity Framework Core 3.1.1 Code First

When I execute the following method with nested LINQ queries:

public async Task<ModuleDto> GetByAssignmentID(int assignmentID)
{
	var result = await Context.Set<Module>()
		.Join(Context.Set<Assignment>(), f => f.DocumentID, ass => ass.DocumentID, (f, ass) => new { Module = f, Assignment = ass })
		.Where(x => x.Assignment.ID == assignmentID)
		.Select(x => new ModuleDto
		{
			ID = x.Module.ID,
			DocumentID = x.Module.DocumentID,
			Organizations = x.Module.Organizations.Select(y => new OrganizationQuestionDto
				{
					ID = y.ID,
					OrganizationQuestion = Context.Set<Question>().AsQueryable()
						.Where(d => d.Name.StartsWith("F_"))
						.SelectMany(d => d.OrganizationQuestion.Where(po => po.OrganizationID == y.ID).DefaultIfEmpty(), (d, id) => new OrganizationQuestionDto
						{
							QuestionID = d.ID,
							QuestionName = d.Name,
							QuestionLabel = d.Label,
							Response = (id != null) ? id.Response : (bool?)null
						})
						.ToList(),
					OrganizationPeople = y.OrganizationPeople.Select(z => new OrganizationPeopleDto
						{
							ID = z.ID,
							Index = z.Index,
							Name = z.Name,
							Surname = z.Surname
						})
						.ToList()
				})
				.ToList()
		})
		.SingleOrDefaultAsync();

	return result;
}

I receive the following exception and stacktrace:

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 'OUTER APPLY ( SELECT d.ID, d.Name, d.Label, t0.Response' at line 12

Inspections.Controllers.HomeController.CompileInspection(int id) in HomeController.cs
+
                throw e;
lambda_method(Closure , object )
Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable+Awaiter.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask<IActionResult> actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
SixLabors.ImageSharp.Web.Middleware.ImageSharpMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
OrchardCore.Diagnostics.DiagnosticsStartupFilter+<>c__DisplayClass3_0+<<Configure>b__1>d.MoveNext() in DiagnosticsStartupFilter.cs
Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
OrchardCore.ContentPreview.PreviewStartupFilter+<>c+<<Configure>b__1_1>d.MoveNext() in PreviewStartupFilter.cs
OrchardCore.Modules.ModularTenantRouterMiddleware.Invoke(HttpContext httpContext) in ModularTenantRouterMiddleware.cs
OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func<ShellScope, Task> execute) in ShellScope.cs
OrchardCore.Modules.ModularTenantContainerMiddleware.Invoke(HttpContext httpContext) in ModularTenantContainerMiddleware.cs
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

EF Core version: 3.1.1
Database: MySQL 5.7.33
Database provider: MySql.Data.EntityFrameworkCore 8.0.22
Target framework: .NET Core 3.1.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.30907.101

I also verified that Exception does not occur when I use the SQL Server 2019 and Microsoft.EntityFrameworkCore.SqlServer.
Is it a bug? If no, how can I fix the issue above?
I need a feedback soon.
Thank you for attention.

[smitpatel]

@lauxjpn - Thoughts?

[lauxjpn]

@tokyo1985 You are using Oracle's EF Core provider, so I can't comment specifically here, since I am the maintainer of Pomelo.EntityFrameworkCore.MySql.

That being said, it looks like Oracle's provider does not translate OUTER APPLY to anything meaningful at all (the OUTER APPLY clause is not valid SQL for MySQL and is just what EF Core generates by default, if a provider does not handle it specifically).

In Pomelo.EntityFrameworkCore.MySql, OUTER APPLY is being translated to LATERAL for MySQL databases, which is supported since MySQL 8.0.14. With Pomelo, you can explicitly set your database server version, and you would not get a syntax error, but an exception telling you, that your database version is not compatible with the OUTER APPLY statement.
But most importantly, with Pomelo and MySQL 8.0.14+, your query would run.

If you cannot upgrade your database server, you have the following options:

• Rewrite your query, that it does not have to use an OUTER APPLY / LATERAL statement. This can usually be achieved by using includes where possible, instead of subqueries.
• Manually split the query into parts, which you then query individually, before combining their results into your DTO.

If you can switch to Pomelo, but cannot upgrade your database server, you have the additional option to:

• Use Pomelo 5.0.0-alpha.2, rewrite your query to use includes (and filters) and use the new Split Queries of EF Core 5.

[smitpatel]

@lauxjpn - Sorry, my mistake, I did not see it was different MySql provider.

[anranruye]

@tokyo1985 @lauxjpn Notice that ef core 5 split query does not support subqueries. Thus I don't think split query can help.

[anranruye]

@tokyo1985
To use an available ef core query, as @lauxjpn said, dto objects should be built on the client side.
Another choice is to use raw sql, to query for a dto which is not an entity type, see #1862 (comment)

[DotCat1985]

@tokyo1985 You are using Oracle's EF Core provider, so I can't comment specifically here, since I am the maintainer of Pomelo.EntityFrameworkCore.MySql.

That being said, it looks like Oracle's provider does not translate OUTER APPLY to anything meaningful at all (the OUTER APPLY clause is not valid SQL for MySQL and is just what EF Core generates by default, if a provider does not handle it specifically).

In Pomelo.EntityFrameworkCore.MySql, OUTER APPLY is being translated to LATERAL for MySQL databases, which is supported since MySQL 8.0.14. With Pomelo, you can explicitly set your database server version, and you would not get a syntax error, but an exception telling you, that your database version is not compatible with the OUTER APPLY statement.
But most importantly, with Pomelo and MySQL 8.0.14+, your query would run.

If you cannot upgrade your database server, you have the following options:

* Rewrite your query, that it does not have to use an `OUTER APPLY` / `LATERAL` statement. This can usually be achieved by using includes where possible, instead of subqueries.

* Manually split the query into parts, which you then query individually, before combining their results into your DTO.

If you can switch to Pomelo, but cannot upgrade your database server, you have the additional option to:

* Use Pomelo `5.0.0-alpha.2`, rewrite your query to use includes (and filters) and use the new [Split Queries](https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries#split-queries-1) of EF Core 5.

Would I get exception with Pomelo and MySQL 5.7?

[roji]

Am closing this as an external issue that isn't related to EF Core itself (but feel free to continue the conversation, of course!).

[lauxjpn]

Would I get exception with Pomelo and MySQL 5.7?

@tokyo1985 Yes, you would get an exception (though a different one telling you what the problem is). It's because:

[...] OUTER APPLY is being translated to LATERAL for MySQL databases, which is supported since MySQL 8.0.14
[...] with Pomelo and MySQL 8.0.14+, your query would run.