Closed
Description
TimeOnly.FromDateTime() does not work in query filtering.
It does work in projections.
Is this expected behaviour?
EF Core version: 8.0.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8
Operating system: Windows 10
IDE: Visual Studio 2022 17.9.6
sample code
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime Time { get; set; }
}
...
var myTime = new TimeOnly(12, 0);
var list = await context.Persons.Where(p => TimeOnly.FromDateTime(p.Time) >= myTime).ToListAsync();
stack trace
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20007]
Disposing connection to database 'testdb' on server '(localdb)\MSSQLLocalDB'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20008]
Disposed connection to database '' on server '' (1ms).
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 8.0.4 initialized 'TestDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:8.0.4' with options: SensitiveDataLoggingEnabled DetailedErrorsEnabled
dbug: Microsoft.EntityFrameworkCore.Query[10111]
Compiling query expression:
'DbSet<Person>()
.Where(p => TimeOnly.FromDateTime(p.Time) >= __myTime_0)'
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The LINQ expression 'DbSet<Person>()
.Where(p => TimeOnly.FromDateTime(p.Time) >= __myTime_0)' could not be translated. Additional information: Translation of method 'System.TimeOnly.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.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
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 WebApplication1.Controllers.HomeController.NonWorkingTime() in C:\Users\ths\source\repos\WebApplication1\WebApplication1\Controllers\HomeController.cs:line 32
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
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()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_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()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
dbug: Microsoft.WebTools.BrowserLink.Net.BrowserLinkMiddleware[1]
Response markup is scheduled to include Browser Link script injection.
dbug: Microsoft.WebTools.BrowserLink.Net.BrowserLinkMiddleware[2]
Response markup was updated to include Browser Link script injection.
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
'TestDbContext' disposed.
Projection works without any problems:
var list = await context.Persons.Select(p => TimeOnly.FromDateTime(p.Time)).ToListAsync();
Note: DateOnly works without problems in filtering and projections:
var myDate = DateOnly.FromDateTime(DateTime.Today);
var list = await context.Persons.Where(p => DateOnly.FromDateTime(p.Time) >= myDate).ToListAsync();
var list = await context.Persons.Select(p => DateOnly.FromDateTime(p.Time)).ToListAsync();
Activity
ErikEJ commentedon May 4, 2024
👀
roji commentedon May 4, 2024
Duplicate of #25103
ErikEJ commentedon May 5, 2024
@roji The duplicate is for SQlite, not SQL Server !?
roji commentedon May 6, 2024
Oops, thanks @ErikEJ - acted too quickly.
dvzghr commentedon May 6, 2024
labels need to be adjusted also -> remove 'closed-duplicate'
20 remaining items