Setting DateTimeKind for SQLite database in ASP.NET Core with Entity Framework

Question

I have an ASP.NET Core application which connects to a sqlite database. Although I use all DateTime values as UTCs (with DateTime.UtcNow and value.ToUniversalTime()), I still get datetime value DateTimeKind.Unspecified from the database.

I tried to set DateTimeKind=Utc in the connection string, but then I get the

System.ArgumentException: Keyword not supported: 'datetimekind'

for the code

var context = services.GetRequiredService<ApplicationDbContext>();
context.Database.Migrate();

I also already tried this solution with:

services.AddDbContext<ApplicationDbContext>(options =>
{
    var connectionString = Configuration.GetConnectionString("DatabaseConnection");

    var conn = new SQLiteConnection(connectionString);
    conn.Open();

    var x = options.UseSqlite(
        conn
    );
});

but it still produces the same error while migration.

I got the feeling that the Migrate function creates it's own connection, because the stacktrace looks like:

at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(String keyword)  
at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.set_Item(String keyword, Object value)  
at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)  
at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder..ctor(String connectionString)  
at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteDatabaseCreator.Exists()  
at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()  
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)  
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)

One notices that the stacktraces goes through

Microsoft.Data.Sqlite.SqliteConnectionStringBuilder

But I don't know why it does.

The call to the migration looks like this:

var context = services.GetRequiredService<ApplicationDbContext>();
context.Database.Migrate();

so as to my understanding the context should include my SQLite connection based on the SQLiteConnection class.

How about using value converters like here https://stackoverflow.com/questions/50727860/ef-core-2-1-hasconversion-on-all-properties-of-type-datetime/50728577#50728577 — Ivan Stoev, Jun 16 '20 at 16:40

score 1 · Answer 1 · answered Jun 13 '21 at 13:32

My solution Declare this class

public static class DateTimeExtensions
{
    public static DateTime ToUtc(this DateTime value)
    {
        if (value.Kind == DateTimeKind.Unspecified)
        {
            value = DateTime.SpecifyKind(value, DateTimeKind.Utc);
        }
        else if (value.Kind == DateTimeKind.Local)
        {
            value = value.ToUniversalTime();
        }
        return value;
    }
    public static DateTime? ToUtc(this DateTime? value)
    {
        if (value.HasValue)
        {
            return ToUtc(value.Value);
        }
        return value;
    }
}

Then declare the DateTime properties like

   private DateTime __Fecha;
   public DateTime Fecha { get => __Fecha; set => __Fecha = value.ToUtc(); }

Warning Read this article, dont use _Fecha or the EF reading dont use the set method. Backing Fields

Setting DateTimeKind for SQLite database in ASP.NET Core with Entity Framework

1 Answers1