EF Core can not handle DateTimeOffset when using an SQLite connection

Question

I can not generate a SQLite query from EF Core 7.0.4 when the query contains a DateTimeOffset.

E.g., the following is not working:

var startDate1 = DateTimeOffset.UtcNow;
var query = ctx.Runs
    .Where(r => r.StartDate > startDate1);

var sql = query.ToQueryString();

This is the error message:

The LINQ expression 'DbSet() .Where(r => r.StartDate > __startDate1_0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

When I remove the DateTimeOffset from the where clause, everything is working fine:

var id = 1;
var query = ctx.Runs
    .Where(r => r.Id > id);

var sql = query.ToQueryString();

The entity looks like this:

public class Run : CarApiModel.Entities.Run
{
    public DateTimeOffset StartDate { get; set; }
    public DateTimeOffset EndDate { get; set; }
    public int Test { get; set; }

    public new Session Session { get; set; }
    public new Driver Driver { get; set; }
    public new RunningClock StartClock { get; set; }
    public new RunningClock? EndClock { get; set; }
}

Note: Querying for the Test property works without issues as well:

var query = _uiContext.Runs
    .Where(r => r.Test < 0);
var sql = query.ToQueryString();

The CarApiModel.Entities.Run looks like this:

public class Run
{
    public long Id { get; set; }
    public long StartSecondsSinceClockStart { get; set; }
    public int StartNanoseconds { get; set; }
    public long? EndSecondsSinceClockStart { get; set; }
    public int? EndNanoseconds { get; set; }
    public string? Description { get; set; }

    public long SessionId { get; set; }
    public long DriverId { get; set; }
    public long StartClockId { get; set; }
    public long? EndClockId { get; set; }

    public virtual Session Session { get; set; }
    public virtual Driver Driver { get; set; }
    public virtual RunningClock StartClock { get; set; }
    public virtual RunningClock? EndClock { get; set; }
}

There is no relevant fluent API configuration for the entities.

I already set up a new project with (in my opinion) the exact same setup, with just one entity having an ID and a DateTimeOffset property. Everything is working fine there, even if I query for the date.

What could cause this problem, or where could I look for issues? Is there any way to deeper debug that query generation?

Answer 1

Using a DateTimeOffsetToBinaryConverter in case of using an SQLite connection resolves the issue. I just added this piece of code to the OnModelCreating(ModelBuilder modelBuilder) method in my EFCore context:

if (Database.IsSqlite())
{
    // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
    // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
    // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
    // use the DateTimeOffsetToBinaryConverter
    // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
    // This only supports millisecond precision, but should be sufficient for most use cases.
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)
                                                                       || p.PropertyType == typeof(DateTimeOffset?));
        foreach (var property in properties)
        {
            modelBuilder
                .Entity(entityType.Name)
                .Property(property.Name)
                .HasConversion(new DateTimeOffsetToBinaryConverter());
        }
    }
}

Source: https://blog.dangl.me/archive/handling-datetimeoffset-in-sqlite-with-entity-framework-core/

Note: This only works down to millisecond precision.