SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime
11.0.1
See the version list below for details.
dotnet add package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime --version 11.0.1
NuGet\Install-Package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime -Version 11.0.1
<PackageReference Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" Version="11.0.1" />
<PackageVersion Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" Version="11.0.1" />
<PackageReference Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" />
paket add SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime --version 11.0.1
#r "nuget: SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, 11.0.1"
#:package SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime@11.0.1
#addin nuget:?package=SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime&version=11.0.1
#tool nuget:?package=SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime&version=11.0.1
EntityFrameworkCore.SqlServer.NodaTime
Adds native support to EntityFrameworkCore for SQL Server for the NodaTime types.
Read more at Learn Entity Framework Core
Storing a Duration
A Duration is stored as time by default, which only holds 0 to just under 24 hours. To store longer durations, or negative ones, give the property one of these column types:
| Column type | Stores | Range | Precision |
|---|---|---|---|
time (default) |
the duration as a time of day | 0 to under 24 hours | 100 ns |
bigint |
nanoseconds | about ±292 years | 1 ns, NodaTime's own |
int |
whole seconds | about ±68 years | 1 s; a fraction of a second is truncated toward zero |
datetime2 |
the offset from 1900-01-01, so 25 hours is 1900-01-02 01:00 | about -1899 to +8099 years | 100 ns |
nvarchar(28), or varchar, nchar or char of at least 28 characters |
fixed-width text, such as 00000001:01:00:00.000000000 for 25 hours |
all of NodaTime's | 1 ns |
// One property:
modelBuilder.Entity<Race>().Property(r => r.Length).HasColumnType("bigint");
// Every Duration in the model:
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
=> configurationBuilder.Properties<Duration>().HaveColumnType("bigint");
The same queries work with all of them except text:
- comparisons, ordering and
Contains; Days,Hours,Minutes,Seconds,Milliseconds,SubsecondNanosecondsandMicroseconds();PlusHourstoPlusNanoseconds,+,-,Plus,Minus, unary-, and*or/by a whole number;EF.Functions.DateDiffHourand the otherDateDifffunctions;ExecuteUpdate.
Arithmetic is done in nanoseconds, so a result can pass 24 hours or go negative even when the column is time. Two Durations stored differently, such as a time column and a bigint column, can be compared or combined: both are converted to nanoseconds first, or, for time and datetime2, the time to a datetime2.
Things to know:
int: a value is truncated to whole seconds when it's saved. So is a parameter or constant that a query compares with anintcolumn, so that an index on the column can still be used. A query that does this logs the warningSqlServerNodaTimeEventId.DurationValueTruncatedToSeconds, whichoptions.ConfigureWarnings(w => w.Ignore(SqlServerNodaTimeEventId.DurationValueTruncatedToSeconds))turns off. The values in aContainslist are truncated the same way, without a warning.- Text: only
==,!=,EqualsandContainsare translated, andExecuteUpdatewith a value. Anything else, such as<,HoursorPlusHours, can't be computed from text in SQL and isn't translated. Sorting,MinandMaxare only right for values that aren't negative: every value has the same width, so text order is duration order until a minus sign comes in. time: a value of 24 hours or more, or a negative one, can't be stored. Saving one fails, and so does anExecuteUpdatethat would write one, or comparing atimecolumn with one in a parameter.- Range: a result outside about ±292 years fails with an arithmetic overflow error from SQL Server. A
datetime2can hold more than that. Storing it, itsDays,Hoursand so on, and comparing it with atime,intordatetime2work for its whole range. Arithmetic on it, and comparing it with abigint, are limited to ±292 years. - Not translated, so evaluated in .NET where EF Core allows it: a Duration multiplied or divided by a
double, and one Duration divided by another. - Not converted: Durations stored differently are not converted to match in join keys,
UnionorConcat, or global query filters. Keep those to columns stored the same way. Text is never converted to or from another storage, so it can't be compared or combined with one.
Changing an existing time column
To datetime2, the migration EF Core generates just works: SQL Server converts a time to that time on 1900-01-01, which is how a datetime2 stores a Duration.
To bigint, int or text, it doesn't: SQL Server can't convert time to bigint, and the text it makes from a time isn't the stored format. Replace the AlterColumn that EF Core generates for the change with:
migrationBuilder.AddColumn<long>(name: "LengthNanoseconds", table: "Races", type: "bigint", nullable: false, defaultValue: 0L);
migrationBuilder.Sql("UPDATE [Races] SET [LengthNanoseconds] = DATEDIFF_BIG(nanosecond, '00:00:00', [Length])");
migrationBuilder.DropColumn(name: "Length", table: "Races");
migrationBuilder.RenameColumn(name: "LengthNanoseconds", table: "Races", newName: "Length");
For int, use AddColumn<int>, type: "int", defaultValue: 0 and DATEDIFF(second, '00:00:00', [Length]). For text, use AddColumn<string>, type: "nvarchar(28)", defaultValue: "" and N'00000000:' + CONVERT(nvarchar(16), CAST([Length] AS time(7))) + N'00'. For a nullable property, use nullable: true and leave out defaultValue.
ZonedDateTime, DateTimeZone and Offset
A ZonedDateTime property needs no configuration. It is stored in two columns, named the way EF Core names a complex type's:
| Column | Type | Holds |
|---|---|---|
Start |
datetimeoffset |
the local date and time with its offset, so the exact instant |
Start_Zone |
nvarchar(50) |
the time zone ID, such as Europe/Paris |
A ZonedDateTime? gets nullable columns, and one inside your own complex type gets its path, such as Schedule_Reminder and Schedule_Reminder_Zone. A DateTimeZone property is stored as its ID in nvarchar(50), or in another string type set with HasColumnType. An Offset is stored as its seconds in an int.
To store a ZonedDateTime's zone in another string type (tzdb IDs are at most 32 characters), use HasColumnType, or HasMaxLength and IsUnicode(false):
modelBuilder.Entity<Event>().ComplexProperty(x => x.Start, b => b.Property(z => z.Zone).HasColumnType("varchar(32)"));
Time zone IDs are looked up in the tzdb provider. To use another one, such as the operating system's zones:
options.UseSqlServer(connectionString, x => x.UseNodaTime(n => n.UseDateTimeZoneProvider(DateTimeZoneProviders.Bcl)));
Pass the same provider instance every time, as DateTimeZoneProviders.Tzdb and .Bcl are: EF Core builds its internal services for each instance. With UseInternalServiceProvider, every context using that service provider must use the same time zone provider.
Queries can use:
==(the same instant in the same zone) and== null;ToInstant(), in comparisons and ordering. A comparison with anInstantcompares thedatetimeoffsetcolumn itself, so an index on it can be used. NodaTime has no<or>forZonedDateTime, so compareToInstant();ToOffsetDateTime(),LocalDateTime,Offset,Zone,Zone.Id, andYear,Hour,Dateand the other parts of the local date and time.
ToInstant(), LocalDateTime and Offset now translate for an OffsetDateTime property too.
Things to know:
- Precision: 100 ns, as for any
datetimeoffset. Only the ISO calendar is kept, as for the other NodaTime types. - Time zone rules: if a zone's rules change after a value was saved (a new tzdb release, for example), the value read back keeps the offset and instant it was saved with.
- Zone IDs: values must be read with the provider they were saved with. A zone ID the provider doesn't know fails when the value is read. A compiled model only supports the tzdb and BCL providers.
- JSON columns: in a JSON column, a
ZonedDateTimeis an object with itsOffsetDateTimeandZone, and the same queries work. A list ofZonedDateTimevalues isn't supported. - Seeding:
HasDatacan't seed aZonedDateTime, since EF Core can't seed complex types. - Methods in a projection: EF Core can't run a method on a
ZonedDateTime(or any struct complex type) at the end of a query, such asSelect(x => x.Start.WithZone(zone)). Select theZonedDateTimeand call the method afterwards. - A default
ZonedDateTime(0001-01-01, with no zone set) is saved and read back as UTC, the zone NodaTime gives it. - Owned types: EF Core can't query a complex type on an owned entity type, so a
ZonedDateTimethere fails when the model is built, with a message saying what to do: make the owned type a complex type instead (ComplexPropertyrather thanOwnsOne), or map the property yourself. - How it's mapped: through
ZonedDateTime's two private fields, itsOffsetDateTimeand its zone. A property you've already mapped yourself keeps your mapping: one with a value converter, on the property or for everyZonedDateTimewithProperties<ZonedDateTime>().HaveConversion(...)inConfigureConventions, or one ignored, including withIgnoreAny<ZonedDateTime>().
Period
A Period property needs no configuration. NodaTime keeps each of a period's units as it is, so PT90M isn't the same period as PT1H30M, and P1D isn't PT24H. So the column keeps all ten of them, signs included, as JSON:
{"Years":0,"Months":0,"Weeks":0,"Days":1,"Hours":-1,"Minutes":90,"Seconds":0,"Milliseconds":0,"Ticks":0,"Nanoseconds":0}
The column is named after the property and is nvarchar(max). A Period inside a JSON column already is an object there. A Period inside a complex type of your own that's stored in the table's columns gets a column for each unit (Rule_Every_Years and so on) instead, as EF Core can't put a JSON column there.
Queries can use:
==,!=and== null, which compare unit by unit, as NodaTime does;- each unit (
Years,Months, ...Nanoseconds), andHasDateComponentandHasTimeComponent; - adding a period to a
LocalDateTime,LocalDateorLocalTime:+,-,Plus,Minus,AddandSubtract, whether the period is a column, a parameter or a constant. Like NodaTime, this adds the years, then the months (from the 31st, a shorter month ends on its last day), then the weeks and days, then the time. ALocalTimewraps around midnight; ToDuration(), counting a week as 7 days and a day as 24 hours;- one unit between two values, such as an age:
Period.Between(x.BirthDate, today, PeriodUnits.Years).Years, for any single unit, forLocalDate,LocalDateTimeandLocalTimevalues; andPeriod.DaysBetween.
A null period gives NULL, as a null value does elsewhere in a query, and has no date or time units: its HasDateComponent and HasTimeComponent are false. Where NodaTime throws for a period, so does the query, rather than give another answer. That covers adding time units to a LocalDate, adding date units to a LocalTime, and ToDuration() with years or months. The SQL Server error includes NodaTime's reason, for example "Conversion failed when converting the nvarchar value 'A Period with time units can't be added to a LocalDate.' to data type int".
Things to know:
- Precision and range: added to a value, the time units are kept to 100 ns, as a
datetime2ortimekeeps them.ToDuration()goes up to about 292 years, asDurationarithmetic in a query does. - Not supported:
Containsover a list of periods, comparing a period in a JSON column with one in columns of its own, and periods computed in a query, such asPeriod.Between(a, b)with several units. As forZonedDateTime,HasDatacan't seed aPeriod, and it fails on an owned entity type, saying what to do instead. - How it's mapped: as a complex type over its ten units. EF Core creates a
Periodwith its internal constructor, which takes them. As forZonedDateTime, a property you've mapped yourself keeps your mapping, whether with a value converter orIgnoreAny<Period>().
Sponsors
Entity Framework Extensions and Dapper Plus are major sponsors and proud to contribute to the development of EFCore.SqlServer.NodaTime.
| Product | Versions 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 was computed. 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. net9.0 was computed. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 was computed. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. net11.0 is compatible. |
| .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. |
-
net11.0
- Microsoft.EntityFrameworkCore.SqlServer (>= 11.0.0)
- NodaTime (>= 3.3.7)
NuGet packages (2)
Showing the top 2 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 |
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 |
|---|---|---|
| 11.0.2 | 605 | 11/14/2026 |
| 11.0.1 | 191 | 11/13/2026 |
| 11.0.0 | 636 | 11/12/2026 |
| 10.0.1 | 277,727 | 5/8/2026 |
| 10.0.0 | 278,587 | 11/13/2025 |
| 9.1.1 | 236,934 | 7/5/2025 |
| 9.1.0 | 141,985 | 3/14/2025 |
| 9.0.0 | 116,011 | 11/21/2024 |
| 8.0.1 | 731,588 | 4/6/2024 |
| 8.0.0 | 223,854 | 11/18/2023 |
| 7.1.0 | 292,773 | 8/12/2023 |
| 7.0.0 | 145,191 | 11/9/2022 |
| 6.0.1 | 364,896 | 3/20/2022 |
| 6.0.0 | 205,704 | 11/8/2021 |
| 5.0.3 | 11,453 | 11/9/2021 |
| 3.1.2 | 10,442 | 11/9/2021 |

