SQLite : Datetime equality comparison not working (in memory)

[anna-git]

When comparing exact dates in memory with the sqlite provider, it returns no result:

 var targets =  (from i in context.Whatever
                        where i.Date == myDate
                        select new { i.Id  }).ToList();

targets.Count is zero.
But this works and returns one item:

 var targets =  (from i in context.Whatever
                        where i.Date.CompareTo(myDate)==0
                        select new { i.Id  }).ToList();

I use Microsoft.EntityFrameworkCore.Sqlite 1.1.2.

[smitpatel]

Probably the second query is being evaluated on client. There seem to be some mismatch between the format of how values being stored and how parameters are being created.

This may be fixed in latest dev due to self-contained type mapping.

[bricelam]

Was the data inserted using EF Core? If not, it may be in an incompatible format.

[anna-git]

No, the data was inserted by reading a sql script before like this:
await context.Database.ExecuteSqlCommandAsync(strCommand);

[anna-git]

The date is inserted this way : '2017-08-19 00:00' . When I query the item without the equal clause on the date, and then I looked at my item specifically, the date is correctly resolved by EF in memory, the day is 19, month 8, year 2017 and the kind is unspecified.
When I compare the ticks it is working fine, like the CompareTo method, but as Smitpatel said, it might be because it is resolved in memory too..

[smitpatel]

DateTime maps to TEXT in SQLite.
EF uses following format to convert date into text @"{0:yyyy\-MM\-dd HH\:mm\:ss.FFFFFFF}";
It is likely that if EF is applying any operation on datetime on server, then it would generate literal/parameter in above format. But if the stored values are in different format it will not match (text matching). Anytime the operation is done on client, it will work because we would convert text value to datetime using parse function. Since compareTo or ticks are not translated to server they work.

[anna-git]

Ok, I tried setting in the sql script '2017-08-19 00:00:00.000000' but it's still not finding it when comparing through ==.. Am I doing it wrong ?

[bricelam]

Yep, this is by design. You need to normalize coerced values (e.g. Guid, DateTime, TimeSpan, etc.) for equality comparisons to work with EF.

[bricelam]

Hmm, manually updating the value should have worked...

[anna-git]

No this is not working, I thought I missed a zero at first, but here's the new test I made to be sure:

 using (var context = new Context(options))
 {
     var date = new DateTime(2017, 08, 21, 0, 0, 0);//script.sql format : '2017-08-21 00:00:00.0000000'
     var result= context.Whatever.FirstOrDefault(w=> w.Date == date); //result null                
     var result2= context.Whatever.ToList().FirstOrDefault(w=> w.Date == date); //result2 ok
  }

Maybe I'm missing something...

[bricelam]

Ah, .FFFFFFF won't pad with 0s. You need to specify just 2017-08-21 00:00:00 in the SQL script.

[bricelam]

Now the question is: Do we want to switch to padding in 2.0?

The built-in SQLite function strftime('%Y-%m-%d %H:%M:%f', ...) does pad with up to three 0s. (e.g. 2017-08-21 00:00:00.000)

[bricelam]

On the other hand, the built-in function datetime(...) doesn't use fractional seconds, so keeping it like it is probably makes the values easier to work with overall.

[anna-git]

Thank you, it's working with the 2017-08-21 00:00:00 format, the == comparison retrieves the item now.

[smitpatel]

@bricelam - Is there anything actionable on our side here? or should we close it as question?

[bricelam]

I've thought about it, and I think the format we have is probably ideal. If you don't have fractional seconds, the format is compatible with datetime(). If you do, the format is lossless.

Unless someone disagrees, we can close it.

[anna-git]

It's fine for me as long as I know the right format for my scripts :) many thanks to you two !!

[jjxtra]

Why was text chosen instead of long for sqlite dates?

[bricelam]

@jjxtra Precision and readability

[jjxtra]

@jjxtra Precision and readability

Precision I don't get, it still takes 8 bytes for a DateTime object in memory. Converting to text converts those 8 bytes to text and does not add any precision, just wasted chars, separators, etc.

[bricelam]

My testing in #15019 showed that milliseconds would be lost (and that was just TimeSpan, not DateTime). You are free to add a value conversion if you want:

modelBuilder.Entity<MyEntity>().Property(e => e.DateTimeProperty)
    .HasConversion(ToJulianDay, FromJulianDay);

[bricelam]

Ah, just noticed you asked about long, not double. Answer for that: SQLite only has helper functions for working with datetime values in text and Julian day floating-point values, not long/ticks