SQLite ignored conversion when using ExecuteUpdateAsync

[universorum]

Trying update DateTimeOffset field, get translate exception when using ExecuteUpdateAsync.

I known SQLite provider does not supported DateTimeOffset value, so I set conversoin in OnModelCreating(). But EF will ignored conversion and fallback to client evaluation.

Update with Change Tracking is work, but ExecuteUpdateAsync is not.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

var connection = new SqliteConnection("Filename=:memory:");
connection.Open();

var contextOptions = new DbContextOptionsBuilder<AppDbContext>().UseSqlite(connection).Options;
var context        = new AppDbContext(contextOptions);
var sql            = context.Database.GenerateCreateScript();
await using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = sql;
    command.ExecuteNonQuery();
}

context.Entities.Add(new Entity { Id = 1, UpdateTime = DateTimeOffset.UtcNow });
await context.SaveChangesAsync();

// Using Change Tracking to update entity, it is work.
var entity = await context.Entities.FirstAsync(x => x.Id == 1);
entity.UpdateTime = DateTimeOffset.UtcNow;
await context.SaveChangesAsync();

// It will throw expception
await context.Entities.Where(x => x.Id == 1)
    .ExecuteUpdateAsync(setter => setter.SetProperty(e => e.UpdateTime, DateTimeOffset.UtcNow));

class Entity
{
    public int            Id         { get; set; }
    public DateTimeOffset UpdateTime { get; set; }
}

class AppDbContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Entity>().Property(e => e.UpdateTime).HasConversion<long>();
    }
}

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Entity>()
    .Where(e => e.Id == 1)
    .ExecuteUpdate(setter => setter.SetProperty<DateTimeOffset>(
        propertyExpression: e => e.UpdateTime, 
        valueExpression: DateTimeOffset.UtcNow))' could not be translated. Additional information: The following 'SetProperty' failed to translate: 'SetProperty(e => e.UpdateTime, DateTimeOffset.UtcNow)'.  See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

[roji]

This fails because ExecuteUpdate accepts an arbitrary expression as the value to be updated - not a simple value - and translates that to SQL; but the SQLite provider does not know how to translate DateTimeOffset.UtcNow to SQL. You'll get the same failure when trying to use it in a query, e.g. .Where(e => e.UpdateTime == DateTimeOffset.UtcNow).

To work around this, extract the expression out to a variable as follows:

var dto = DateTimeOffset.UtcNow;
await ctx.Blogs.ExecuteUpdateAsync(setter => setter.SetProperty(e => e.DateTimeOffset, dto));

Note: if we change ExecuteUpdate to accept a non-Expression list of property setters #32018), then the value for this overload of SetProperty would just be a simple, non-Expression object, and then this would translate.