efcore: DateTimeOffset expression could not be translated in EF.Sqlite 2.2.0

Simple LINQ query could not be translated in EF.Sqlite 2.2:

queryable.Where(e => e.CreationDate > DateTimeOffset.Now)

It worked with EF.Sqlite 2.1 and EF.SqlServer 2.2.

Exception message:
System.InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where ([e].CreationDate > DateTimeOffset.Now)' could not be translated and will be evaluated locally.'.
Stack trace:
   at Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`1.Log[TLoggerCategory](IDiagnosticsLogger`1 logger, WarningBehavior warningBehavior, TParam arg, Exception exception)
   at Microsoft.EntityFrameworkCore.Internal.RelationalLoggerExtensions.QueryClientEvaluationWarning(IDiagnosticsLogger`1 diagnostics, QueryModel queryModel, Object queryModelElement)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.WarnClientEval(QueryModel queryModel, Object queryModelElement)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass22_0`1.<CompileAsyncQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 118

Further technical details

EF Core version: 2.2.0 Database Provider: Microsoft.EntityFrameworkCore.Sqlite Operating system: Windows 10 1803 IDE: Visual Studio 2017 15.9.3

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 6
Comments: 17 (11 by maintainers)

Most upvoted comments

Neither one should work. 😏 But the second one is more likely to evaluate on the client in older versions of EF Core.

bricelam on Jul 6, 2021

@smitpatel any chance of revisiting this now that you’ve closed #13192?

If you just treat the SQLite database as a UTC DateTimeOffset (e.g. any incoming DateTimeOffset gets converted to the UTC DateTime, any outgoing gets converted back to DateTimeOffset with offset +0) then courtesy of #13192 you should also be able to handle comparisons, assignments, etc. to local DateTimeOffset values by converting them to their UTC DateTime values first before binding to the query?

mqudsi on Jul 11, 2019

So what can we do now? We are stuck on the old version because in our whole application is DateTimeOffset. Is there any workaround or solution? Will there be a fix?

sebastianfilke on Feb 10, 2019

instead of queryable.Where(e => e.CreationDate > DateTimeOffset.Now)

try this queryable.Where(e => e.CreationDate.CompareTo(DateTimeOffset.Now) > 0)

worked for me

RaduTofan on Jul 5, 2021

There are few issues here in using value conversion.

At translation time we do no infer types, we only do that during printing out SQL. So even if you have value converter on the property which query would find out from property, the other side (if constant or parameter) still calls it out DateTimeOffset and block translation from going to server. (Issue #13192)
In above case DateTimeOffset.Now being server evaluated always, SQLite does not have translation for that. So even though we try to use value converter after above issue is fixed, now we need to find translation of DateTimeOffset.Now.UtcDateTime. Which we may be able to condense to DateTime.Now (though not necessary we can do that for any value converter. This is covered by #10434

smitpatel on Dec 8, 2018

I hoped that with the conversion, a query like this would work translate:

queryable.Where(e => (DateTime)e.CreationDateTimeOffset > DateTime.UtcNow)

bricelam on Jan 2, 2019

Reopening so that we can follow on on why type conversions don’t help. The original issue is still by-design.

divega on Dec 8, 2018