# 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:

- Support for the new .NET DateOnly and TimeOnly types.
- Most DateTimeOffset members and methods are now translated.
- Many NodaTime translations have been added for ZonedDateTime, Period, DateInterval and others.
- PostgreSQL `tstzrange` is now mapped to NodaTime Interval, and PostgreSQL `daterange` is now mapped to NodaTime DateInterval. Most methods on these types are translated (#1998, #2059).

### Other new features

- The provider is now fully annotated for nullable reference types.
- Full support for the PostgreSQL 14 multirange type, mapped to arrays or lists of `NpgsqlRange<T>` (#1963). Includes translation of all major operators and functions, see the updated docs.
- Support for PostgreSQL 14 column compression methods (#2062).
- Support for the PostgreSQL ltree type, which represents labels of data stored in a hierarchical tree-like structure. Requires PostgreSQL 13 and above.
- Multiple spatial translations have been added for NetTopologySuite (DistanceKnn, <->, ST_Force2D, ST_Distance and ST_DWithin with spheriod).
- More translations and better type inference for arrays (#2026).

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.
  - If using NodaTime (recommended), use either Instant or ZonedDateTime with time zone UTC.
- 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:

- Data Annotations
- Fluent API

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Blog>()
            .Property(e => e.SomeArrayOverIntNpgsqlRange)
            .HasColumnType("int4range[]");
    }

### 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