Adds native support to EntityFrameworkCore for SQL Server for the NodaTime types.
The following types are supported:
- Instant
- OffsetDateTime
- LocalDateTime
- LocalDate
- LocalTime
- Duration
All types and their methods have unit tests written to verify that the SQL is translated as expected. See individual tests for more information.
To use, simply install the NuGet package:
Install-Package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime
Then call the UseNodaTime()
method as part of your SqlServer configuration during the UseSqlServer
method call:
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
options.UseSqlServer("your DB Connection",
x => x.UseNodaTime());
The SQL DATEADD
function is supported for the following types:
- Instant (extension methods)
- OffsetDateTime (native and some extension methods)
- LocalDateTime (native and some extension methods)
- LocalDate (native and some extension methods)
- LocalTime (native and some extension methods)
- Duration (native and some extension methods)
Note: Please add a using statement in order to use the extension methods:
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
- PlusYears
- PlusMonths
- PlusDays
- PlusHours
- PlusMinutes
- PlusSeconds
- PlusMilliseconds
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
// PlusYears
await this.Db.RaceResult
.Where(r => r.StartTime.PlusYears(1) >= Instant.FromUtc(2019, 7, 1, 1, 0))
.ToListAsync();
// Translates to:
// SELECT [r].[Id], [r].[EndTime], [r].[StartTime], [r].[StartTimeOffset]
// FROM [RaceResult] AS [r] WHERE DATEADD(year, CAST(1 AS int), [r].[StartTime]) >= '2019-07-01T01:00:00.0000000Z'
The SQL DATEPART
function is supported for the following types:
- Instant (extension methods)
- OffsetDateTime (native and some extension methods)
- LocalDateTime (native and some extension methods)
- LocalDate (native and some extension methods)
- LocalTime (native and some extension methods)
- Duration (native and some extension methods)
Note: Please add a using statement in order to use the extension methods:
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
- Year
- Quarter
- Month
- DayOfYear
- Day
- Week
- WeekDay
- Hour
- Minute
- Second
- Millisecond
- Microsecond
- Nanosecond
- TzOffset
- IsoWeek
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
// Compare the 'Year' DatePart
await this.Db.RaceResult
.Where(r => r.StartTime.Year() == 2019)
.ToListAsync();
// Translates to:
// SELECT [r].[Id], [r].[EndTime], [r].[StartTime], [r].[StartTimeOffset]
// FROM [RaceResult] AS [r] WHERE DATEPART(year, [r].[StartTime]) = 2019
The SQL DATEDIFF
function is supported for the following types:
- Instant (extension methods)
- OffsetDateTime (extension methods)
- LocalDateTime (extension methods)
- LocalDate (extension methods)
- LocalTime (extension methods)
- Duration (extension methods)
Note: Please add a using statement in order to use the extension methods:
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
- Year
- Quarter
- Month
- DayOfYear
- Day
- Week
- WeekDay
- Hour
- Minute
- Second
- Millisecond
- Microsecond
- Nanosecond
- TzOffset
- IsoWeek
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
// DateDiff based on 'day'
DbFunctions dbFunctions = null;
await this.Db.Race
.Where(r => dbFunctions.DateDiffDay(r.Date, new LocalDate(2020, 1, 1)) >= 200)
.ToListAsync();
// Translates to:
// SELECT [r].[Id], [r].[Date], [r].[ScheduledDuration], [r].[ScheduledStart], [r].[ScheduledStartTime]
// FROM [Race] AS [r]
// WHERE DATEDIFF(DAY, [r].[Date], '2020-01-01') >= 200
The SQL DATEDIFF_BIG
function is supported for the following types:
- Instant (extension methods)
- OffsetDateTime (extension methods)
- LocalDateTime (extension methods)
- LocalTime (extension methods)
- Duration (extension methods)
Note: Please add a using statement in order to use the extension methods:
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
- Second
- Millisecond
- Microsecond
- Nanosecond
using Microsoft.EntityFrameworkCore.SqlServer.NodaTime.Extensions;
// DateDiffBig based on 'second'
DbFunctions dbFunctions = null;
await this.Db.RaceResult
.Where(r => dbFunctions.DateDiffBigSecond(r.StartTime, Instant.FromUtc(2019, 7, 1, 0, 0)) >= 100000)
.ToListAsync();
// Translates to:
// SELECT [r].[Id], [r].[EndTime], [r].[OffsetFromWinner], [r].[StartTime], [r].[StartTimeOffset]
// FROM [RaceResult] AS [r]
// WHERE DATEDIFF_BIG(SECOND, [r].[StartTime], '2019-07-01T00:00:00.0000000Z') >= CAST(100000 AS bigint)