SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime 9.0.0

dotnet add package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime --version 9.0.0                
NuGet\Install-Package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime -Version 9.0.0                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" Version="9.0.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 9.0.0                
#r "nuget: SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, 9.0.0"                
#r directive can be used in F# Interactive and Polyglot Notebooks. 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=9.0.0

// Install SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime as a Cake Tool
#tool nuget:?package=SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime&version=9.0.0                

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

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 (stored as time in SQL Server which limits this type to < 24 hours)

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)
  • 9.0.0 (Nov 20, 2024)
    • Release for EF Core 9
  • 9.0.0-rc.1.24451.1 (Sept 27, 2024)
    • Release candidate 1 for EF Core 9
  • 8.0.1 (April 6, 2024)
    • Added support for compiled models - #39
  • 8.0.0 (November 18, 2023)
    • Release for EF Core 8
  • 8.0.0-rc.1.23419.6 (September 15, 2023)
    • Release candidate 1 for EF Core 8
  • 7.1.0 (August 12, 2023)
    • Added support for LocalDateTime.Date property - #35
    • Added support for OffsetDateTime.Date property
  • 7.0.0 (November 9, 2022)
    • Initial release supporting EF Core 7.0.0
  • 7.0.0-rc.1.22426.7 (September 25, 2022)
    • Release candidate for EF Core 7
  • 6.0.1
    • Fixed an issue where an ArgumentNullException would throw if you called UseInternalServiceProvider - #27
  • 6.0.0
    • Support for .Net 6 - #23
  • 5.0.3
    • Fixed an issue where DateTime queries failed in some instances - #25
  • 5.0.2
    • Added design time support - #16
  • 5.0.1
    • Fix SqlDateTime overflow - #13
  • 5.0.0
    • Support for .Net 5
  • 3.1.2
    • Fixed an issue where DateTime queries failed in some instances - #25
  • 3.1.1
    • Fix SqlDateTime overflow - #13
  • 3.1.0
    • Sync version number with .Net Core 3.1.0
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (3)

Showing the top 3 NuGet packages that depend on SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime:

Package Downloads
EasyDesk.CleanArchitecture.Dal.SqlServer

Utilities for the SqlServer data access layer of the clean architecture as seen by EasyDesk.

SiamOutlet.IFP.Core

Core Library for IFP

Energinet.DataHub.ElectricityMarket.Integration

[Release Notes](https://github.com/Energinet-DataHub/geh-electricity-market/blob/master/docs/Energinet.DataHub.ElectricityMarket.Integration/release-notes/release-notes.md) [Documentation](https://github.com/Energinet-DataHub/geh-electricity-market/blob/master/docs/Energinet.DataHub.ElectricityMarket.Integration/documentation.md)

GitHub repositories (2)

Showing the top 2 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 in Visual Studio & CLI
yangzhongke/Zack.EFCore.Batch
Deleting or Updating multiple records from a LINQ Query in a SQL statement without loading entities
Version Downloads Last updated
9.0.0 4,940 11/21/2024
8.0.1 211,201 4/6/2024
8.0.0 126,536 11/18/2023
7.1.0 157,279 8/12/2023
7.0.0 133,279 11/9/2022
6.0.1 302,794 3/20/2022
6.0.0 179,046 11/8/2021
5.0.3 10,832 11/9/2021
5.0.2 34,206 4/14/2021
5.0.1 30,039 11/28/2020
5.0.0 850 11/10/2020
3.1.2 10,145 11/9/2021
3.1.1 14,594 11/28/2020
3.1.0 1,616 11/10/2020
1.3.3 785 10/20/2020
1.3.2 1,292 9/25/2020
1.3.1 1,464 9/7/2020
1.3.0 4,172 3/19/2020
1.2.0 820 3/3/2020