Skip to content

Adds support for NodaTime types when using SQLite with Entity Framework Core.

License

Notifications You must be signed in to change notification settings

khellang/EFCore.Sqlite.NodaTime

Repository files navigation

EFCore.Sqlite.NodaTime

Build

Adds support for NodaTime types when using SQLite with Entity Framework Core.

Installation

NuGet

Install the latest package from NuGet.

Getting Started

If you're using Entity Framework Core without Dependency Injection, you can call UseNodaTime inside the OnConfiguring method in your DbContext class:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("<connection-string>", x => x.UseNodaTime());
    }
}

Otherwise, you should call UseNodaTime when adding the DbContext to your service collection:

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<MyDbContext>(options =>
            options.UseSqlite("<connection-string>", x => x.UseNodaTime()));
    }
}

And that's it. You can now use NodaTime types in your entities and perform server-side queries on them! ✨

Supported Types

The following NodaTime types are currently supported:

NodaTime Type SQLite Type SQLite Format
Instant TEXT YYYY-MM-DD HH:MM:SS.SSS
LocalDateTime TEXT YYYY-MM-DD HH:MM:SS.SSS
LocalDate TEXT YYYY-MM-DD
LocalTime TEXT HH:MM:SS.SSS

Supported Properties

NodaTime Property Generated SQL Notes
Year strftime('%Y', <column>) The value is cast to INTEGER for comparison.
Month strftime('%m', <column>) The value is cast to INTEGER for comparison.
Day strftime('%d', <column>) The value is cast to INTEGER for comparison.
Hour strftime('%H', <column>) The value is cast to INTEGER for comparison.
Minute strftime('%M', <column>) The value is cast to INTEGER for comparison.
Second strftime('%S', <column>) The value is cast to INTEGER for comparison.
DayOfYear strftime('%j', <column>) The value is cast to INTEGER for comparison.
DayOfWeek strftime('%w', <column>) The value is cast to INTEGER for comparison. As NodaTime's IsoDayOfWeek enum doesn't match SQLite's day of week, additional SQL is emitted to convert Sunday to the correct value.
Date date(<column>)
TimeOfDay strftime('%H:%M:%f', <column>) In order to support fractional seconds to fully roundtrip LocalTime, a custom format string is used instead of using time(<column>).

Supported Methods

NodaTime Method Generated SQL
SystemClock.Instance.GetCurrentInstant() strftime('%Y-%m-%d %H:%M:%f', 'now')
LocalDate.PlusYears date(<column>, '+n years')
LocalDate.PlusMonths date(<column>, '+n months')
LocalDate.PlusWeeks date(<column>, '+n*7 days')
LocalDate.PlusDays date(<column>, '+n days')
LocalDate.ToDateOnly date(<column>)
LocalDate.ToDateTimeUnspecified date(<column>)
LocalTime.PlusHours strftime('%H:%M:%f', <column>, '+n hours')
LocalTime.PlusMinutes strftime('%H:%M:%f', <column>, '+n minutes')
LocalTime.PlusSeconds strftime('%H:%M:%f', <column>, '+n seconds')
LocalTime.PlusMilliseconds strftime('%H:%M:%f', <column>, '+0.n seconds')
LocalTime.ToTimeOnly strftime('%H:%M:%f', <column>)
LocalDateTime.PlusYears strftime('%Y-%m-%d %H:%M:%f',<column>, '+n years')
LocalDateTime.PlusMonths strftime('%Y-%m-%d %H:%M:%f',<column>, '+n months')
LocalDateTime.PlusWeeks strftime('%Y-%m-%d %H:%M:%f',<column>, '+n*7 days')
LocalDateTime.PlusDays strftime('%Y-%m-%d %H:%M:%f',<column>, '+n days')
LocalDateTime.PlusHours strftime('%Y-%m-%d %H:%M:%f', <column>, '+n hours')
LocalDateTime.PlusMinutes strftime('%Y-%m-%d %H:%M:%f', <column>, '+n minutes')
LocalDateTime.PlusSeconds strftime('%Y-%m-%d %H:%M:%f', <column>, '+n seconds')
LocalDateTime.PlusMilliseconds strftime('%Y-%m-%d %H:%M:%f', <column>, '+0.n seconds')
LocalDateTime.ToDateTimeUnspecified strftime('%Y-%m-%d %H:%M:%f', <column>)

When these methods are chained, all modifiers will be added to the same function call, like this:

context.NodaTime.Select(x => x.LocalDateTime.PlusMonths(2).PlusDays(2).PlusHours(2).PlusSeconds(2))

Results in the following SQL:

SELECT strftime('%Y-%m-%d %H:%M:%f', "n"."LocalDateTime", '+2 months', '+2 days', '+2 hours', '+2 seconds')