Efcore: ValueConversion for DateTimeOffset is not working

[christianheld]

I am using following value conversion for DateTimeOffset with SQLite:

var dateTimeOffsetConverter = new ValueConverter<DateTimeOffset, long>(
    dateTimeOffset => dateTimeOffset.ToUnixTimeMilliseconds(),
    unixTime => DateTimeOffset.FromUnixTimeMilliseconds(unixTime).ToLocalTime());

With version 2.2.2 I get following warning:

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where ([dde].Timestamp <= Convert(__toDate_1))' could not be translated and will be evaluated locally.

The conversion is working as excepted with version 2.1.8.

Further technical details

EF Core version: 2.2.2
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 7 SP1
IDE: Visual Studio 2017 Version 15.9.7

[ajcvickers]

@christianheld Can you post the shape of your entity types and the LINQ query you are executing?

[christianheld]

Here is a minimal repro program:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;

namespace DateTimeOffsetSample
{
    public class Entity
    {
        public int Id { get; set; }
        public DateTimeOffset? Timestamp { get; set; }
    }

    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<Entity> Entities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var converter = new ValueConverter<DateTimeOffset, long>(
                dateTimeOffset => dateTimeOffset.ToUnixTimeMilliseconds(),
                unixTime => DateTimeOffset.FromUnixTimeMilliseconds(unixTime).ToLocalTime());

            modelBuilder.Entity<Entity>()
                .Property(e => e.Timestamp)
                .HasColumnType("BIGINT")
                .HasConversion(converter);
        }
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            var loggerFactory = new LoggerFactory();
            loggerFactory.AddConsole();

            var options = new DbContextOptionsBuilder<DataContext>()
                .UseSqlite("Data Source=test.db")
                .UseLoggerFactory(loggerFactory)
                .EnableSensitiveDataLogging()
                .Options;

            using (var context = new DataContext(options))
            {
                context.Database.EnsureCreated();
                DateTimeOffset? now = DateTimeOffset.Now;
                var result = context.Entities
                    .Where(e => e.Timestamp <= now)
                    .ToList();
            }
        }
    }
}

[ajcvickers]

Note for triage. This is likely due to the intentional change we made to stop server-evaluating for DatetTimeOffset on SQLite since it gave the wrong results--see #14082. However, in this case the value is being converted to a long, which would make this correct on the server, but is now falling back to client evaluation anyway.

[ajcvickers]

@smitpatel to find related issue where we will determine translation based on store type.

[smitpatel]

Related #10434

[ajcvickers]

Verified fixed in current master.

Executed DbCommand (1ms) [Parameters=[@__now_0='1590437610521' (Nullable = true) (DbType = String)], CommandType='Text', CommandTimeout='30']
      SELECT "e"."Id", "e"."Timestamp"
      FROM "Entities" AS "e"
      WHERE "e"."Timestamp" <= @__now_0