Table of Contents

6.0 Release Notes

The release candidate of Npgsql Entity Framework Core provider version 6.0 has been released and is available on nuget. This version works with version 6.0 of Entity Framework Core, and brings new Npgsql features in addition to the general EF Core changes.

Npgsql 6.0 brings some major breaking changes and is not a simple in-place upgrade. Carefully read the breaking change notes below and upgrade with care.

New features

Timestamp rationalization and improvements

Support for timestamp with time zone and timestamp without time zone has been rationalized and simplified, and aligned with PostgreSQL best practices. In particular, the "UTC everywhere" pattern is much better supported via the PostgreSQL timestamp with time zone type, which is the recommended way to handle timestamps. A detailed explanation is available in this blog post, below is a summary of the main improvements.

  • UTC timestamps have been cleanly separated from non-UTC timestamps, aligning with the PostgreSQL types. The former are represented by timestamp with time zone and DateTime with Kind UTC, the latter by timestamp without time zone and DateTime with Kind Local or Unspecified. It is recommended to use UTC timestamps where possible.
  • Npgsql no longer performs any implicit timezone conversions when reading or writing any timestamp value - the value in the database is what you get, and the machine timezone no longer plays any role when reading/writing values.
  • Npgsql no longer supports date/time representations which cannot be fully round-tripped to the database. If it can't be fully stored as-is, you can't write it.
  • A compatibility switch enables opting out of the new behavior, to maintain backwards compatibility.

This change introduces significant breaking changes (see below), although a compatibility flag can be used to opt out and revert to the previous behavior.

Other date/time improvements include:

Other new features

The full list of issues for this release is available here.

Breaking changes

Major changes to timestamp mapping

Note

It is possible to opt out of these changes to maintain backwards compatibility, see below.

Quick summary

  • In many cases, it makes sense to store UTC timestamps in the database. To do this, migrate your timestamp without time zone columns to timestamp with time zone (see migration notes below), and always use either DateTime with Kind=Utc or DateTimeOffset with offset 0.
  • To store non-UTC timestamps, use DateTime with Kind=Unspecified and add explicit configuration to your properties to be timestamp without time zone.
    • If using NodaTime (recommended), use LocalDateTime (no explicit column configuration is required).

Detailed notes

The below notes will use the PostgreSQL aliases timestamptz to refer to timestamp with time zone, and timestamp to refer to timestamp without time zone. Note that timestamp with time zone represents a UTC timestamp and does not store a timezone in the database.

  • DateTime properties now map to timestamptz by default, instead of to timestamp; this follows the recommended practice of storing UTC timestamps by default, but will cause the first migration to change your column type.
    • If the intention is to store point-in-time or UTC timestamps, it's recommended to allow the migration to occur (see migration notes below).
    • If the column really should store non-UTC timestamps (local or unspecified), explicitly set the column type back to timestamp. This is usually discouraged, but can be a temporary solution before transitioning to timestamptz.
  • It is no longer possible to write DateTime with Kinds Local or Unspecified to timestamptz properties (which are the default for DateTime). Previously, Npgsql allowed writing those, performing timezone conversions from local to UTC. To write to timestamptz, provide a UTC DateTime. Similarly, it is no longer possible to write DateTime with Kind UTC to a timestamp column.
  • timestamptz values are now read back as DateTime with Kind=UTC, without any conversions; these were previously returned as local DateTime, converted to the local machine's timezone. When reading timestamptz values as DateTimeOffset, UTC values (offset 0) are always returned.
  • It is no longer possible to write DateTimeOffset with offsets other than 0 (UTC), since these cannot be represented in PostgreSQL. These were previously implicitly converted to UTC before sending.

See the Npgsql ADO.NET docs for additional lower-level changes to timestamp handling.

NodaTime changes

  • Properties with type Instant are now mapped to timestamptz columns, and not to timestamp, since they represent a universally agreed-upon point in time. This follows the recommended practice, but will cause the first migration to change your column type.
    • If the intention is to store point-in-time or UTC timestamps, it's recommended to allow the migration to occur (see migration notes below).
    • If the column really should store non-UTC timestamps (local or unspecified), change the property's type to LocalDateTime instead; this will maintain the mapping to timestamp. This is usually discouraged, but can be a temporary solution before transitioning to timestamptz.
  • When reading timestamptz as ZonedDateTime or OffsetDateTime, UTC values are always returned. Previously, local values based on the PostgreSQL TimeZone parameter were returned.

Migrating columns from timestamp to timestamptz

As a result of the above changes, the first migration created after upgrading to 6.0 will alter the columns for all DateTime and Instant properties from timestamp to timestamptz. If these columns are meant to store point-in-time or UTC timestamps (the recommended practice), then it's best to let this migration proceed; but care must be taken.

As a starting point, let's assume your existing timestamp column has the timestamp 2020-01-01 12:00:00:

SELECT "CreatedOn", pg_typeof("CreatedOn") AS type FROM "Blogs";

Results in:

      CreatedOn      |            type
---------------------+-----------------------------
 2020-01-01 12:00:00 | timestamp without time zone

The migration generated by version 6.0 will cause the following SQL to be generated:

ALTER TABLE "Blogs" ALTER COLUMN "CreatedOn" TYPE timestamp with time zone;

When converting the timestamp without time zone column to timestamp with time zone, PostgreSQL will assume that existing values are local timestamps, and will convert them to UTC based on the TimeZone parameter. Performing the above query will result in something like:

        CreatedOn       |           type
------------------------+--------------------------
 2020-01-01 12:00:00+02 | timestamp with time zone

This means that your new timestamptz column now contains 10:00 UTC, which is probably not what you want: if the original values were in fact UTC values, you need them to be preserved as-is, changing only the column type. To do this, edit your migration and add the following to the top of your migration's Up and Down methods:

migrationBuilder.Sql("SET TimeZone='UTC';");

This will ensure that no time zone conversions will be applied when converting the columns:

        CreatedOn       |           type
------------------------+--------------------------
 2020-01-01 14:00:00+02 | timestamp with time zone

Changing timestamp seed data

When switching from timestamp without time zone to timezone with time zone, you may have have non-UTC timestamp literals in your seeding configuration:

modelBuilder.Entity<Blog>().HasData(new Blog { Id = 1, Timestamp = new DateTime(2020, 1, 1, 0, 0, 0) });

If so, you'll have to change these to be UTC. In addition, all migrations code since the change must be modified in the same way, to only seed UTC DateTime into the column; the model snapshot should be changed as well.

Opting out of the new timestamp mapping logic

The changes described above are far-reaching, and may break applications in various ways. You can upgrade to version 6.0 but opt out of the new mapping by enabling the Npgsql.EnableLegacyTimestampBehavior AppContext switch. To do this and revert to the legacy timestamp behavior, add the following to your context's constructor, before any Npgsql or EF Core operations are invoked:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

NodaTime: tstzrange and daterange are mapped to Interval and DateInterval by default

When using NodaTime, PostgreSQL tstzrange columns are scaffolded as Interval properties instead of NpgsqlRange<Instant> (#4070), and daterange columns are scaffolded as DateInterval properties of NpgsqlRange<LocalDateTime> (#1998).

Date/time min/max values are now converted to PostgreSQL infinity values by default

PostgreSQL has special infinity and -infinity values for timestamps and dates, which are later and earlier than other value. Npgsql has supported mapping DateTime.MaxValue and MinValue to these infinity values via an Convert Infinity DateTime connection string parameter, which was disabled by default. This behavior is now on by default, since DateTime.MaxValue and MinValue are very rarely used as actual timestamps/dates, and the Convert Infinity DateTime parameter has been removed. To disable infinity conversions, add the following at the start of your application:

AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

See the date/time documentation for more details.

Value converters for array/list properties need to use a special new API

Previously, it was possible to configure value converters for array/list properties with the general EF Core API:

modelBuilder.Entity<SomeEntity>.Property(e => e.ValueConvertedArray)
    .HasConversion(w => w.Select(x => x.Value).ToArray(), v => v.Select(x => new IntWrapper(x)).ToArray());

This is no longer possible and will cause an exception to be thrown. Instead, use the new HasPostgresArrayConversion API, providing conversion lambdas for the array's elements:

modelBuilder.Entity<SomeEntity>.Property(e => e.ValueConvertedArray)
    .HasPostgresArrayConversion(w => w.Value, v => new IntWrapper(v));

Arrays/lists over ranges are mapped to PG14 multiranges

PostgreSQL 14 introduced a new multirange type, which is very similar to an array of ranges but supports various range-related operations efficiently. The provider now maps arrays and lists of NpgsqlRange to these new types by default. To map them to old-style arrays over ranges in PostgreSQL, configure the column type explicitly:

[Column(TypeName = "int4range[]")]
public NpgsqlRange<int>[] SomeArrayOverIntNpgsqlRange { get; set;}

Trigrams and FuzzyStringMatch plugins are now built-in

The Npgsql.EntityFrameworkCore.PostgreSQL.Trigrams and Npgsql.EntityFrameworkCore.PostgreSQL.FuzzyStringMatch plugins have been integrated into the main provider; as a result, there are no 6.0 versions of these nuget packages - simply remove the package references from your project when upgrading to 6.0.0.

Contributors

A big thank you to all the following people who contributed to the 6.0 release!

Milestone 6.0.8

Contributor Assigned issues
@roji 3

Milestone 6.0.7

Contributor Assigned issues
@roji 5

Milestone 6.0.6

Contributor Assigned issues
@roji 2

Milestone 6.0.5

Contributor Assigned issues
@roji 5

Milestone 6.0.4

Contributor Assigned issues
@roji 6
@kourosko 1

Milestone 6.0.3

Contributor Assigned issues
@roji 8
@rus-art 1

Milestone 6.0.2

Contributor Assigned issues
@roji 4

Milestone 6.0.1

Contributor Assigned issues
@roji 5
@Brar 1
@vonzshik 1

Milestone 6.0.0

Contributor Assigned issues
@roji 56
@vonzshik 2
@arontsang 1
@Isitar 1
@kislovs 1
@pafrench 1
@tiborfsk 1
Edit this page