Inconsistent results when querying DateTime stored as integer

[hultqvist]

The following two samples return different results.

    Console.WriteLine("Test 3: < UtcNow.AddMinutes(-60)");
    foreach (var row in db.MyRows.Where(r => r.Date < DateTime.UtcNow.AddMinutes(-60)))
        Console.WriteLine(row.Id + " " + row.Date);

    Console.WriteLine("Test 4: < now");
    var now = DateTime.UtcNow.AddMinutes(-60);
    foreach (var row in db.MyRows.Where(r => r.Date < now))
        Console.WriteLine(row.Id + " " + row.Date);

The date is stored in an integer.

I've tested with two different configurations, the outcome is different with bot but the inconsistency is there in both situations.

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        //configurationBuilder.Properties<DateTime>().HaveConversion<DateTimeToTicksConverter>();
        configurationBuilder.Properties<DateTime>().HaveConversion<long>();
        base.ConfigureConventions(configurationBuilder);
    }

Results

With HaveConversion<DateTimeToTicksConverter>();

Test 3: < UtcNow.AddMinutes(-60)
1 2023-04-10 20:09:17
2 2023-04-10 18:09:17
Test 4: < now

With HaveConversion<long>();

Test 3: < UtcNow.AddMinutes(-60)
1 2023-04-10 20:10:07
2 2023-04-10 18:10:07
Test 4: < now
1 2023-04-10 20:10:07

Sample project

https://github.com/hultqvist/ef-core-date

Include version information

Microsoft.Data.Sqlite version: Microsoft.EntityFrameworkCore.Sqlite Version 7.0.4
Target framework: .NET 7.0
Operating system: Windows 11

[ajcvickers]

@hultqvist This requires #10434, since the SQL translation for DateTime.UtcNow needs to change to take into account the value conversion--see the logs below.

In addition, the default converter for DateTime to long is an encoder that preserves DateTime.Kind, and not juist a straight conversion to ticks.

Test All rows
info: 4/11/2023 12:15:50.317 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m"."Id", "m"."Date"
      FROM "MyRows" AS "m"
1 4/11/2023 12:15:48 PM
2 4/11/2023 11:15:48 AM
Test < DateTime.UtcNow
info: 4/11/2023 12:15:50.388 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m"."Id", "m"."Date"
      FROM "MyRows" AS "m"
      WHERE "m"."Date" < rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now'), '0'), '.')
1 4/11/2023 12:15:48 PM
2 4/11/2023 11:15:48 AM
Test < UtcNow.AddMinutes(-60)
info: 4/11/2023 12:15:50.404 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m"."Id", "m"."Date"
      FROM "MyRows" AS "m"
      WHERE "m"."Date" < rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now', CAST(-60.0 AS TEXT) || ' minutes'), '0'), '.')
1 4/11/2023 12:15:48 PM
2 4/11/2023 11:15:48 AM
Test < now
info: 4/11/2023 12:15:50.412 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__now_0='638168049504087657'], CommandType='Text', CommandTimeout='30']
      SELECT "m"."Id", "m"."Date"
      FROM "MyRows" AS "m"
      WHERE "m"."Date" < @__now_0

Process finished with exit code 0.