SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime 6.0.0-rc.1.21452.0

This is a prerelease version of SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime.
There is a newer prerelease version of this package available.
See the version list below for details.
Install-Package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime -Version 6.0.0-rc.1.21452.0
dotnet add package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime --version 6.0.0-rc.1.21452.0
<PackageReference Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" Version="6.0.0-rc.1.21452.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime --version 6.0.0-rc.1.21452.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, 6.0.0-rc.1.21452.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime as a Cake Addin
#addin nuget:?package=SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime&version=6.0.0-rc.1.21452.0&prerelease

// Install SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime as a Cake Tool
#tool nuget:?package=SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime&version=6.0.0-rc.1.21452.0&prerelease
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

.NET Core NuGet version (EFCore.SqlServer.NodaTime)

EFCore.SqlServer.NodaTime

Adds native support to EntityFrameworkCore for SQL Server for the NodaTime types.

When modelling, usage of the following NodaTime types are supported:

  • Instant
  • OffsetDateTime
  • LocalDateTime
  • LocalDate
  • LocalTime
  • Duration

When querying, standard operators are supported as well as a range of additional mappings from NodaTime properties/function to their SQL Server equivalents.

Unit Tests

All types and their methods have unit tests written to verify that the SQL is translated as expected. See individual tests for more information.

Note: To run the unit tests for the first time, you will need to uncomment the lines here. This ensures that the test DB is created locally.

Usage

To use, simply install the NuGet package:

Install-Package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime

Note: Versioning follows the [major.minor] of EF Core so that it is easy to know which version to install. Ie, if you are using EF Core v5.x then you would install v5.x of this package. Build and revision numbers are not guaranteed to follow the same numbers.

Then call the UseNodaTime() method as part of your SqlServer configuration during the UseSqlServer method call:

using Microsoft.EntityFrameworkCore;

options.UseSqlServer("your DB Connection",
                    x => x.UseNodaTime());

Reverse Engineering (Scaffolding)

Support for reverse engineering has been added starting in v5.0.2.

The SQL Server types map as follows:

  • smalldatetimeInstant
  • datetimeInstant
  • datetime2Instant
  • dateLocalDate
  • timeLocalTime
  • datetimeoffsetOffsetDateTime

Additional property / function mappings

DATEADD Support

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;
Supported Methods
  • 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'

DATEPART Support

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;
Supported Parts
  • 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

DATEDIFF Support

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;
Supported Parts
  • 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

DATEDIFF_BIG Support

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;
Supported Parts
  • 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)

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories (1)

Showing the top 1 popular GitHub repositories that depend on SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime:

Repository Stars
ErikEJ/EFCorePowerTools
Entity Framework Core Power Tools - reverse engineering, migrations and model visualization for EF Core
Version Downloads Last updated
6.0.0-rc.2.21480.0 122 10/12/2021
6.0.0-rc.1.21452.0 79 9/22/2021
5.0.2 14,090 4/14/2021
5.0.1 6,727 11/28/2020
5.0.0 493 11/10/2020
3.1.1 5,244 11/28/2020
3.1.0 1,334 11/10/2020
1.3.3 359 10/20/2020
1.3.2 959 9/25/2020
1.3.1 1,169 9/7/2020
1.3.0 3,723 3/19/2020
1.2.0 496 3/3/2020