sub queries with OR filters does not work

[Ahmedrz89]

Steps to reproduce

A console application is attached that you can use to reproduce the issue

SubQueriesTest.zip

The issue

this sub query works well

var query = context.Mains.Select(t => new
{
    Test = t,
    Test2 = context.Subs.FirstOrDefault(t2 => t2.Id == t.Id && t2.Name == t.Name),
});

this sub query does not work

var query2 = context.Mains.Select(t => new
{
    Test = t,
    Test2 = context.Subs.FirstOrDefault(t2 => t2.Id == t.Id || t2.Name == t.Name),
});

Exception message:

The LINQ expression 'OUTER APPLY 
(
    SELECT TOP(1) s.Id, s.Name
    FROM Subs AS s
    WHERE (s.Id == m.Id) || (s.Name == m.Name)
) AS t' 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.

Stack trace:

at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.CheckTranslated(Expression translated, Expression original)
   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>g__VisitList|124_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass124_0& )
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Pomelo.EntityFrameworkCore.MySql.Query.Internal.MySqlParameterBasedSqlProcessor.Optimize(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.CreateDbCommand()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.ToQueryString()
   at Program.<<Main>$>d__0.MoveNext() in C:\Users\ASUS\source\repos\SubQueriesTest\SubQueriesTest\Program.cs:line 49

Further technical details

MariaDB version: 10.5.9 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 6.0.2 Microsoft.AspNetCore.App version: None

[mguinness]

See similar issue reported in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1548. If you run this .NET Fiddle it works for MySQL.

Changing MySqlServerVersion.LatestSupportedServerVersion to MariaDbServerVersion.LatestSupportedServerVersion will replicate your exception.