EF Core MySQL uses inconsistent time zone when using DateTimeOffset

Question

I'm using EF Core 3.1.1 with MySql.EntityFrameworkCore 8.0.20. As the service is already launched, I can't update the package for the time being.

When adding a row like the following, DateTimeOffset.Now is evaluated before the SQL query is generated, i.e., the current time in the system's time zone is pushed to the database.

dbContext.Set<MyTable>().Add(new MyTable
{
    ...
    RegisteredAt = DateTimeOffset.Now
    ...
});

However, when retrieving rows by queries with comparisons between DateTimeOffset values, DateTimeOffset.Now is replaced withUTC_TIMESTAMP(), which indicates the current UTC time. I think this should be CURRENT_TIMESTAMP()(or NOW()) as DateTimeOffset.Now in other contexts is considered as the current local time.

var myTable = from m in dbContext.Set<MyTable>()
              where m.RegisteredAt < DateTimeOffset.Now
              select m;

The generated SQL query is as follows:

SELECT ..., `i`.`RegisteredAt`, ...
FROM `Item` AS `i`
WHERE (`i`.`BeginsAt` < UTC_TIMESTAMP())

I found that moving DateTimeOffset.Now to a separate variable solves this problem, but this requires me to find and edit every LINQ queries using DateTimeOffset.Now.

var currentTime = DateTimeOffset.Now;
var myTable = from m in dbContext.Set<MyTable>()
              where m.RegisteredAt < currentTime
              select m;

Is there any feature in EF Core 3.1.1 so I can intercept the generated SQL and replace UTC_TIMESTAMP() to CURRENT_TIMESTAMP()?

Answer 1

I found that EF Core 3 has a feature called interceptors, which can be used to mutate all SQL queries before being executed. Override both DbCommandInterceptor.ReaderExecuting and DbCommandInterceptor.ReaderExecutingAsync and edit SQL queries as follows:

public override InterceptionResult<DbDataReader>, ReaderExecuting(
    DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
    command.CommandText = command.CommandText.Replace("UTC_TIMESTAMP()", "CURRENT_TIMESTAMP()");
    return result;
}

// Do similarly in ReaderExecutingAsync

But I'm not sure whether this will make only limited performance impacts.