Repository navigation
DateTimeOffset expression could not be translated in EF.Sqlite 2.2.0 #14082
Description
Activity
- changed the title
[-]DateTime expression could not be translated in EF.Sqlite 2.2.0[/-][+]DateTimeOffset expression could not be translated in EF.Sqlite 2.2.0[/+]on Dec 5, 2018 SQLite does not have any type mapping or operations involving DateTimeOffset on server.
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.
@bricelam Thank you for the details. To make queries on Sqlite I have to store them in DateTime?
@YZahringer yes, that is the best approach with SQLite.
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?
if (IsSqlite) { modelBuilder.Entity<MyEntity>().Property(e => e.CreationDateTimeOffset).HasConversion( dateTimeOffset => dateTimeOffset.UtcDateTime, dateTime => new DateTimeOffset(dateTime)); }
Reopening so that we can follow on on why type conversions don’t help. The original issue is still by-design.
Reacted by Yann Zahringer FerrandoThere 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 Query: Infer TypeMapping for Literals & Parameters correctly #13192)
- In above case
DateTimeOffset.Nowbeing 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 ofDateTimeOffset.Now.UtcDateTime. Which we may be able to condense toDateTime.Now(though not necessary we can do that for any value converter. This is covered by Support (custom) member/method translations on value-converted types #10434
Reacted by Yann Zahringer Ferrando and Arthur Vickers@bricelam I tried a variety of different "casts" but could not get anything to work. Do you want to give it a try?
I hoped that with the conversion, a query like this would work translate:
queryable.Where(e => (DateTime)e.CreationDateTimeOffset > DateTime.UtcNow)
Reacted by malik masisSo 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?
Reacted by Andrew Beaven, Pavel Voronin and malik masis@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 incomingDateTimeOffsetgets converted to the UTCDateTime, any outgoing gets converted back toDateTimeOffsetwith offset +0) then courtesy of #13192 you should also be able to handle comparisons, assignments, etc. to localDateTimeOffsetvalues by converting them to their UTCDateTimevalues first before binding to the query?Reacted by Yann Zahringer Ferrando, Joshua Grant and Arad AlvandI 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.
instead of
queryable.Where(e => e.CreationDate > DateTimeOffset.Now)try this
queryable.Where(e => e.CreationDate.CompareTo(DateTimeOffset.Now) > 0)worked for me
Reacted by g$, inzanty, Marco Ribeiro, Brian Dukes and Alina SedokinaReacted by malik masis@bricelam should there be a difference between the two in the example @RaduTofan posted?
Neither one should work. 😏 But the second one is more likely to evaluate on the client in older versions of EF Core.
Reacted by Smit Patel, Mahmoud Al-Qudsi and Taylor White- marked DateTimeOffset? is evaluted locally since EFCore 2.2.0 #14333 as a duplicate of this issue
on Jun 16, 2025
Simple LINQ query could not be translated in EF.Sqlite 2.2:
It worked with EF.Sqlite 2.1 and EF.SqlServer 2.2.
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