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 stackoverflow.com/questions/50727860/… — Ivan Stoev
– Ivan Stoev, Commented Jun 16, 2020 at 16:40
This question is similar to: EF CORE 2.1 HasConversion on all properties of type datetime. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — user4157124
– user4157124, Commented Oct 24 at 8:23

FRL · Accepted Answer · 2021-06-13 13:32:44Z

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

Anton Tropashko · Accepted Answer · 2025-10-23 12:41:34Z

Drawing on FRLs answer

    public static class DateTimeExtensions
    {
        public static readonly DateTime UnixEpoch =
            new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

        public static long ToEpochSeconds(this DateTime value)
        {
            return (long)(value.ToUtc() - UnixEpoch).TotalSeconds;
        }

        public static DateTime FromEpochSeconds(long epochSeconds)
        {
            return UnixEpoch.AddSeconds(epochSeconds);
        }

        
        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 you'll store data as time since epoch for example

ChangedAtEpoch INTEGER NOT NULL

`// Stored as UTC seconds since Unix epoch

    public long ChangedAtEpoch
    {
        get;
        set;
    }`

and convert back and froth

ChangedAtEpoch = DateTime.UtcNow.ToEpochSeconds()

var localTime = DateTimeExtensions.FromEpochSeconds(epochSeconds).ToLocalTime();

unix epoch is chosen solely because lazy bums could use online calculators https://www.freeformatter.com/epoch-timestamp-to-date-converter.html instead of creating a python/bash/whatever script to do it offline and because of my extensive UNIX background that led me to the dark side of Windows programming ;^)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related