Can I use the same code for MySql and MariaDb interchangeably ?

[rajmondburgaj]

I am developing an app which was locally using MySql and everything is working fine, no issue when running LINQ queries, but as soon as I deployed to a prod server I realized that there is MariaDb in use instead of MySql, as a result exceptions started popping up across the system, mostly not being able to translate LINQ queries to SQL syntax counterpart. Tried playing around with ServerVersion to match MariaDb type and version but still the same. Most of the queries work but some complex ones do not work. As an example this statement does not work:

 var t = await _dbContext
.Applications
.Where(x => x.ConfigurationContextTemplateId == request.Id)
.SelectMany(x => x.Versions.Select(v => new
{
    v.State,
    v.Version,
    x.ApplicationName,
    x.TeamId,
    v.ApplicationId,
    TeamName = v.Application.Team.Name,
    MaxVersion = x.Versions.Max(e => e.Version)
}))
.Where(x => x.Version == x.MaxVersion)
.Select(x => new TemplateApplicationLinkResponse
{
    State = x.State,
    Version = x.Version,
    ApplicationId = x.ApplicationId,
    ApplicationName = x.ApplicationName,
    TeamName = x.TeamName,
    TeamId = x.TeamId
})
.OrderBy(x => x.ApplicationName)
.ThenByDescending(x => x.Version)
.ToListAsync(cancellationToken);

In case of MariaDb it generates this exception:

ErrorMessage: The LINQ expression 'CROSS APPLY Projection Mapping:
(
    SELECT a2.State, a2.Version, a0.ApplicationName, a2.ApplicationId, a3.TeamId, t.Name, (Projection Mapping:
            EmptyProjectionMember -> 0
        SELECT MAX(a1.Version)
        FROM ApplicationVersions AS a1
        WHERE a0.Id == a1.ApplicationId) AS c
    FROM ApplicationVersions AS a2
    INNER JOIN Applications AS a3 ON a2.ApplicationId == a3.Id
    INNER JOIN Teams AS t ON a3.TeamId == t.Id
    WHERE a0.Id == a2.ApplicationId
) AS t0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

System.InvalidOperationException: The LINQ expression 'CROSS APPLY Projection Mapping:
(
    SELECT a2.State, a2.Version, a0.ApplicationName, a2.ApplicationId, a3.TeamId, t.Name, (Projection Mapping:
            EmptyProjectionMember -> 0
        SELECT MAX(a1.Version)
        FROM ApplicationVersions AS a1
        WHERE a0.Id == a1.ApplicationId) AS c
    FROM ApplicationVersions AS a2
    INNER JOIN Applications AS a3 ON a2.ApplicationId == a3.Id
    INNER JOIN Teams AS t ON a3.TeamId == t.Id
    WHERE a0.Id == a2.ApplicationId
) AS t0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.CheckTranslated(Expression translated, Expression original)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.CheckSupport(Expression expression, Boolean isSupported)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitCrossApply(CrossApplyExpression crossApplyExpression)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Pomelo.EntityFrameworkCore.MySql.Query.Internal.MySqlParameterBasedSqlProcessor.ProcessSqlNullability(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.Optimize(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at FeatureSwitchBackend.MediatR.QueryHandlers.ConfigurationContext.GetConfigurationContextQueryHandler.Handle(GetConfigurationContextQuery request, CancellationToken cancellationToken) in E:\Programming\Projects\featureconfigbackend\FeatureSwitchBackend\MediatR\QueryHandlers\ConfigurationContext\GetConfigurationContextQueryHandler.cs:line 44
   at MediatR.Pipeline.RequestExceptionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionActionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestExceptionActionProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestPostProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at MediatR.Pipeline.RequestPreProcessorBehavior`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next)
   at FeatureSwitchBackend.Extensions.ControllerBaseExtensions.Send[TCommand,TResponse](ControllerBase controller, TCommand request, CancellationToken cancellationToken) in E:\Programming\Projects\featureconfigbackend\FeatureSwitchBackend\Extensions\ControllerBaseExtensions.cs:line 46
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)

But when I use MySql I get the following query:

SELECT `t0`.`State`, `t0`.`Version`, `t0`.`ApplicationId`, `t0`.`ApplicationName`, `t0`.`Name` AS `TeamName`, `t0`.`TeamId`
FROM `Applications` AS `a`
JOIN LATERAL (
    SELECT `a1`.`State`, `a1`.`Version`, `a`.`ApplicationName`, `a`.`TeamId`, `a1`.`ApplicationId`, `t`.`Name`, (
        SELECT MAX(`a0`.`Version`)
        FROM `ApplicationVersions` AS `a0`
        WHERE `a`.`Id` = `a0`.`ApplicationId`) AS `c`
    FROM `ApplicationVersions` AS `a1`
    INNER JOIN `Applications` AS `a2` ON `a1`.`ApplicationId` = `a2`.`Id`
    INNER JOIN `Teams` AS `t` ON `a2`.`TeamId` = `t`.`Id`
    WHERE `a`.`Id` = `a1`.`ApplicationId`
) AS `t0` ON TRUE
WHERE (`a`.`ConfigurationContextTemplateId` = @__request_Id_0) AND (`t0`.`Version` = `t0`.`c`)
ORDER BY `t0`.`ApplicationName`, `t0`.`Version` DESC

I am using Asp.Net Core 5 with below packages:

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="5.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.11">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="5.0.0" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="5.0.2" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Microsoft" Version="5.0.2" />
</ItemGroup>

[mguinness]

There are differences in implementations, i.e. Support lateral derived tables for MariaDB.

[lauxjpn]

@rajmondburgaj Generally, you want to test your queries against MariaDB in this case, since it is still missing a couple of features necessary for full EF Core support (especially the LATERAL JOIN support).

I many cases, it is possible to build a query without depending on CROSS APPLY/LATERAL JOIN support, but those are usually a bit more complex. Once your queries are using SelectMany() and more complex nested queries, they are likely to need LATERAL JOIN support.

I remember posting some sample code showing how to do this a couple of months ago (either here or on StackOverflow).

[rajmondburgaj]

@rajmondburgaj Generally, you want to test your queries against MariaDB in this case, since it is still missing a couple of features necessary for full EF Core support (especially the LATERAL JOIN support).

I many cases, it is possible to build a query without depending on CROSS APPLY/LATERAL JOIN support, but those are usually a bit more complex. Once your queries are using SelectMany() and more complex nested queries, they are likely to need LATERAL JOIN support.

I remember posting some sample code showing how to do this a couple of months ago (either here or on StackOverflow).

@lauxjpn thanks for the reply, I will close the ticket for now since MariaDb does not support such feature. Will try to find your code sample and investigate it if its worth changing my code or just ignore supporting MariaDB for now.

[lauxjpn]

I remember posting some sample code showing how to do this a couple of months ago (either here or on StackOverflow).

I think I was referring to https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1363#issuecomment-913155200, which is not really the same thing.

However, I remember seeing some code emulating a LATERAL JOIN on StackOverflow (not by me) some time ago. Might have been CROSS/OUTER APPLY in MySQL.