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

[YZahringer]

Simple LINQ query could not be translated in EF.Sqlite 2.2:
```c#
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.EventDefinition1.Log[TLoggerCategory](IDiagnosticsLogger1 logger, WarningBehavior warningBehavior, TParam arg, Exception exception)
at Microsoft.EntityFrameworkCore.Internal.RelationalLoggerExtensions.QueryClientEvaluationWarning(IDiagnosticsLogger1 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(ObservableCollection1 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.CreateAsyncQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileAsyncQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass22_01.<CompileAsyncQuery>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func1 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.EntityQueryable1.System.Collections.Generic.IAsyncEnumerable.GetEnumerator()
at System.Linq.AsyncEnumerable.Aggregate_TSource,TAccumulate,TResult 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

[smitpatel]

SQLite does not have any type mapping or operations involving DateTimeOffset on server.

[bricelam]

It was broken in 2.1--values with different offsets were compared incorrectly. We fixed it in 2.2 to compare the values on the client.

[YZahringer]

@bricelam Thank you for the details. To make queries on Sqlite I have to store them in DateTime?

[divega]

@YZahringer yes, that is the best approach with SQLite.

[YZahringer]

I use the same model on different SQL Providers, which support DateTimeOffset.

I was thinking of adding a conversion to DateTime when it's Sqlite, but it does not work, the Query is still evaluated client side. Something is wrong?
csharp if (IsSqlite) { modelBuilder.Entity<MyEntity>().Property(e => e.CreationDateTimeOffset).HasConversion( dateTimeOffset => dateTimeOffset.UtcDateTime, dateTime => new DateTimeOffset(dateTime)); }

[divega]

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

[smitpatel]

@divega
Duplicate of #13192

[smitpatel]

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

[ajcvickers]

@bricelam I tried a variety of different "casts" but could not get anything to work. Do you want to give it a try?

[bricelam]

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

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

[sebastianfilke]

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?

[mqudsi]

@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?

[smitpatel]

Deferring to @bricelam. It may not be just about offset.

[bricelam]

I think we have an issue about allowing translations after a value conversion. We should consider this scenario.

Note that you lose the original time zone information when you do this so we wouldn’t do it by default.