Translate DateOnly.FromDateTime

[DrkWzrd]

When using DateOnly.FromDateTime in some element of a query, it breaks because is not translated to a cast function in SQL.

Include your code

This fragment of code (inside a DbContext):

public async Task<IEnumerable<AppointmentDto>> GetAppointmentsAsync(int? page, int? pageSize, DateOnly? fromInclusive = null, DateOnly? toExclusive = null)
{
    var query = Appointments.AsQueryable();
    if (fromInclusive.HasValue)
    {
        query = query.Where(a => DateOnly.FromDateTime(a.StartDateTimeUtc) >= fromInclusive.Value);
    }
    if (toExclusive.HasValue)
    {
        query = query.Where(a => toExclusive.Value > DateOnly.FromDateTime(a.StartDateTimeUtc));
    }
    if (page.HasValue)
    {
        page = Math.Max(1, page.Value);
        pageSize = pageSize ?? 10;
        query = query.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);
    }
    query = query.OrderBy(a => a.StartDateTimeUtc);

    return await query.ProjectTo<AppointmentDto>(_mapper.ConfigurationProvider).ToListAsync();
}

Include stack traces

Produces this exception:

System.InvalidOperationException: The LINQ expression 'DbSet<Appointment>()
    .Where(a => DateOnly.FromDateTime(a.StartDateTimeUtc) >= __fromInclusive_Value_0)' could not be translated. Additional information: Translation of method 'System.DateOnly.FromDateTime' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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 Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at ClinicModel.ClinicContext.GetAppointmentsAsync(Nullable`1 page, Nullable`1 pageSize, Nullable`1 fromInclusive, Nullable`1 toExclusive) in D:\repos\DrkWzrd\DentalProSuite\ClinicModel\ClinicContext.cs:line 182
   at Program.<>c.<<Main>b__4_2>d.MoveNext() in D:\repos\DrkWzrd\DentalProSuite\ApiSurface\Program.cs:line 94
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Http.RequestDelegateFactory.ExecuteTaskResult[T](Task`1 task, HttpContext httpContext)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

Include provider and version information

EF Core version: 7.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer 7.0.5
Target framework: net7.0
Operating system: Windows 10 x64 Pro
IDE: VS2022 17.5.4

[ErikEJ]

DateOnly is not supported by default with EF Core 7 - maybe you can use my package: https://www.nuget.org/packages/ErikEJ.EntityFrameworkCore.SqlServer.DateOnlyTimeOnly

[DrkWzrd]

@ErikEJ The problem is I'm using your package

This is my configuring method (the connection string is injected).

    protected override void OnConfiguring(DbContextOptionsBuilder builder)
    {
        builder.UseSqlServer(opt=> opt.UseDateOnlyTimeOnly())
            .EnableServiceProviderCaching()
#if DEBUG
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors()
            .EnableThreadSafetyChecks();
#endif
    }

[roji]

Note: we're lacking this translation in the new 8.0 EF support as well.

[ErikEJ]

@DrkWzrd ah, cool. So you found a bug, I will look into a fix.

[ErikEJ]

@DrkWzrd This has been fixed and an updated package version 7.0.3 has been published to Nuget

[roji]

@ErikEJ any interest in submitting the PR for here as well? :)

[ErikEJ]

@roji happy to, if you could just confirm that my fix makes sense...?

[roji]

Yep - LGTM (just see https://github.com/ErikEJ/EFCore.SqlServer.DateOnlyTimeOnly/pull/11/files#r1171412466).

[DrkWzrd]

@ErikEJ This weekend I will have time to test it, thanks! This should be a warning about the DateTime-> DateOnly conversion too, I don't know if that it's included.

[DrkWzrd]

@ErikEJ Works like a charm. This should be included in EF8 (with the other conversions missing)

[ErikEJ]

Thanks! "Other conversions missing" - not sure what you mean by that?

[DrkWzrd]

Nothing, my fault, I was thinking the inverse conversion (DateOnly->DateTime) was not included, but it works by default. Thank you for your effort. ;)

[jonsamwell]

Is this expected in any of the EFCore 8 previews?

[ErikEJ]

As noted above, I might make a PR some time. @jonsamwell

[jonsamwell]

That would be very much appreciated @ErikEJ