EFCore.Sqlite.NodaTime

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

Installation

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')