NodaTime Integration

This document explains how to use NodaTime date and time types with the Npgsql Entity Framework Core provider. NodaTime provides a more comprehensive and accurate date/time API for .NET applications compared to the built-in types, with better support for time zones, date ranges, and various date/time concepts.

Overview

The Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime extension enables Entity Framework Core to work with NodaTime's date and time types when using PostgreSQL databases. It provides type mappings between NodaTime types and PostgreSQL types, as well as LINQ query translations for NodaTime operations.

The integration is implemented via a plugin architecture, extending the core provider's type mapping and translation capabilities.

Title: NodaTime Integration Component Flow

Sources: src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs13-43 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs12-28

Getting Started

Configuration

Add the NodaTime extension when configuring your DbContext. This registers the necessary plugins for type mapping and query translation.

Type Mappings

The integration maps NodaTime types to corresponding PostgreSQL types via the NpgsqlNodaTimeTypeMappingSourcePlugin src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs13

Title: NodaTime to PostgreSQL Type Entity Mapping

Sources: src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs47-61 src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs93-121 src/EFCore.PG.NodaTime/Storage/Internal/TimestampLocalDateTimeMapping.cs16-44 src/EFCore.PG.NodaTime/Storage/Internal/TimestampTzInstantMapping.cs15-34

Range and Multirange Mappings

PostgreSQL range and multirange types (introduced in PG 14) are supported for several NodaTime types src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs63-72

NodaTime Type	PostgreSQL Range Type	Mapping Class
NpgsqlRange<LocalDateTime>	tsrange	NpgsqlRangeTypeMapping
NpgsqlRange<Instant>	tstzrange	NpgsqlRangeTypeMapping
DateInterval	daterange	DateIntervalRangeMapping
Interval	tstzrange	IntervalRangeMapping

Sources: src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs80-91 src/EFCore.PG.NodaTime/Storage/Internal/DateIntervalRangeMapping.cs11 src/EFCore.PG.NodaTime/Storage/Internal/IntervalRangeMapping.cs11

Legacy Timestamp Behavior

If the Npgsql.EnableLegacyTimestampBehavior AppContext switch is enabled, Instant is mapped to timestamp without time zone instead of timestamptz src/EFCore.PG.NodaTime/Storage/Internal/NpgsqlNodaTimeTypeMappingSourcePlugin.cs23-27 This is handled by LegacyTimestampInstantMapping src/EFCore.PG.NodaTime/Storage/Internal/LegacyTimestampInstantMapping.cs16-35

Query Translation

The integration provides comprehensive translation of NodaTime members and methods into PostgreSQL-specific SQL functions.

Member Translation

NpgsqlNodaTimeMemberTranslator handles properties like Year, Month, Day, etc., typically translating them to the PostgreSQL date_part function src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs42-60

• LocalDateTime/LocalDate: Members like Year, Month, Day are translated using date_part src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs289-300
• Duration: Properties like TotalSeconds are translated to date_part('epoch', ...) src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs117
• Interval: Start and End are translated to PostgreSQL lower() and upper() range functions src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs135-136

Method Translation

NpgsqlNodaTimeMethodCallTranslator handles method translations src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs47-53

• SystemClock.GetCurrentInstant(): Translated to the PostgreSQL NOW() function src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs95-112
• InZone(): Uses the PendingZonedDateTimeExpression to eventually generate an AT TIME ZONE SQL expression src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs121-122
• Distance: Translates EF.Functions.Distance() to the PostgreSQL distance operator <-> for supported NodaTime types src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs130-132

Title: Translation Logic Flow

Sources: src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs24-27 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs26-30 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs89-135

Evaluatable Expression Filter

To ensure that server-side functions like NOW() are not evaluated locally on the client, the NpgsqlNodaTimeEvaluatableExpressionFilterPlugin marks specific NodaTime members (like SystemClock.GetCurrentInstant) as non-evaluatable src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeEvaluatableExpressionFilterPlugin.cs9-47

Special Handling

Infinity Values

PostgreSQL supports infinity and -infinity for timestamp types. The NodaTime plugin maps these to Instant.MaxValue/MinValue and LocalDateTime.MaxIsoValue/MinIsoValue respectively, unless DisableDateTimeInfinityConversions is set via AppContext switch src/EFCore.PG.NodaTime/Storage/Internal/TimestampLocalDateTimeMapping.cs106-117 src/EFCore.PG.NodaTime/Storage/Internal/TimestampTzInstantMapping.cs96-107

Time Zone Provider

The plugin supports translating DateTimeZoneProviders.Tzdb["Area/Location"]. This is captured as a PendingDateTimeZoneProviderExpression src/EFCore.PG.NodaTime/Query/Internal/PendingDateTimeZoneProviderExpression.cs8 and allows the provider to extract the string ID for use in SQL AT TIME ZONE expressions src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs79-84

Sources: src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs67-70 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeEvaluatableExpressionFilterPlugin.cs37-40