Cannot convert Queries with DataTime Comparation cannot be translated

[ahmadalli]

Steps to reproduce

this is my code sample. This part of code results in exception:

https://github.com/ahmadalli/PomeloMysqlFKTest/blob/23a0bbf21c7a69e198c31fc5402eee0cca9a68c7/FKTest/Services/HostedServices/StartupHostedService.cs#L37-L43

The issue

If I comment the last Where (which compares date times) everything works just fine.

Exception message:

The LINQ expression 'DbSet<Child>()
    .Where(c => c.Name != null && "David" != null && c.Name.StartsWith("David"))
    .Where(c => c.Code != null && "74" != null && c.Code.EndsWith("74"))
    .Join(
        inner: DbSet<Parent>(), 
        outerKeySelector: c => EF.Property<Nullable<Guid>>(c, "ParentId"), 
        innerKeySelector: p => EF.Property<Nullable<Guid>>(p, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<Child, Parent>(
            Outer = o, 
            Inner = i
        ))
    .Where(c => c.Inner.Code != null && "74" != null && c.Inner.Code.EndsWith("74"))
    .Where(c => c.Inner.Created > c.Outer.Created - 00:30:00)' 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 Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at FKTest.Services.HostedServices.StartupHostedService.StartAsync(CancellationToken cancellationToken) in E:\tmp\FKTest\FKTest\Services\HostedServices\StartupHostedService.cs:line 37
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at FKTest.Program.Main(String[] args) in E:\tmp\FKTest\FKTest\Program.cs:line 15

Further technical details

MySQL version: 8.0.22 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2 (also tested with the latest stable version alongside efcore 3) Microsoft.AspNetCore.App version: 5.0.0

[mguinness]

See https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1147 and also look into AddX() method translators.

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/3c8e65b02faaef135631896ee2c6113a4bc10bb0/test/EFCore.MySql.IntegrationTests/Tests/Models/ExpressionTest.cs#L82-L97