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