Table of Contents

10.0 Release Notes

Npgsql.EntityFrameworkCore.PostgreSQL version 10.0 is now in development, preview versions are available on nuget.org.

Full support for EF 10 JSON complex types

EF 10 introduced support for mapping .NET types as JSON complex types, resolving several issues that existed with the previous JSON mapping via owned entities (see release notes for more information). The PG provider providers full support for this feature full support for this as well:

modelBuilder.Entity<Customer>(b =>
{
    b.ComplexProperty(c => c.ShippingAddress, c => c.ToJson());
    b.ComplexProperty(c => c.BillingAddress, c => c.ToJson());
});

This configuration causes the following table to be created for your customers:

CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    "BillingAddress" jsonb NOT NULL,
    "ShippingAddress" jsonb NOT NULL,
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);

This is now the preferred way to perform strongly-typed JSON mapping of arbitrary .NET types, and replaces owned entities and legacy POCO mapping.

The provider now also supports performing partial updates within JSON documents using ExecuteUpdate. For example, the following efficiently copies overwrites all Customers' shipping address streets with their billing address streets:

await context.Customers.ExecuteUpdateAsync(s =>
    s.SetProperty(b => b.ShippingAddress.Street, b => b.BillingAddress.Street));

This produces the following SQL:

UPDATE "Customers" AS c
SET "ShippingAddress" = jsonb_set(c."ShippingAddress", '{Street}', c."BillingAddress" -> 'Street')

Better support for JSON scalar (primitive) collections

In most relational databases, scalar collections are mapped to a JSON column, the the collection is serialized to a JSON array in the database. PostgreSQL, however, is unique in providing a 1st-class array type, so the EF provider maps scalar collections to array instead. For example, given the following type:

public class Customer
{
    public int Id { get; set; }
    public string[] Tags { get; set; }
}

... the PostgreSQL provider will create the following table (note that text[] array column):

CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Tags" text[] NOT NULL,
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);

However, when scalar collections are nested within a JSON document, they must be mapped to JSON arrays, as in other databases:

public class Customer
{
    public int Id { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    // ...

    public string[] Tags { get; set; }
}

Version 10 of the provider now produces much better SQL when querying such nested scalar collections. For example, when querying using Contains:

var customers = await context.Customers.Where(b => b.ShippingAddress.Tags.Contains("foo")).ToListAsync();

... previous versions of the provider generated the following complicated (and inefficient) SQL:

SELECT c."Id", c."Name", c."ShippingAddress"
FROM "Customers" AS c
WHERE 'foo' = ANY ((ARRAY(SELECT CAST(element AS text) FROM jsonb_array_elements_text(c."ShippingAddress" -> 'Tags') WITH ORDINALITY AS t(element) ORDER BY ordinality)))

Version 10, in contrast, produces the following cleaner SQL, which can also benefit from indexes:

SELECT c."Id", c."Name", c."ShippingAddress"
FROM "Customers" AS c
WHERE (c."ShippingAddress" -> 'Tags') @> to_jsonb('foo'::text)

Finally, version 10 of the provider also allows you to map a non-nested scalar collection to a JSON column, instead of to an array column, and provides fully querying capabilities:

public class Customer
{
    // ...

    [Column(TypeName = "jsonb")]
    public string[] Tags { get; set; }
}

Support for PostgreSQL 18 virtual generated columns

Before PostgreSQL 18, generated (or "computed") columns could only be stored, meaning they were computed when a row is inserted or updated, and take up space on disk just like regular columns. PostgreSQL 18 introduced support for virtual generated columns, which are instead calculated when read, and take up no space on disk. Virtual columns can be defined with version 10 of the PostgreSQL provider as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
      .Property(p => p.DisplayName)
      .HasComputedColumnSql(@"""FirstName"" || ' ' || ""LastName""");
}

Note that previously, stored: true had to be specified in the above code sample; starting with version 10, omitting it causes a virtual generated column to be created (supported on PostgreSQL 18 only).

For more information, see the documentation.

Support for UUIDv7

By default, EF generates GUID (or UUID) values locally in .NET, rather than relying on the database to generate them. Version 9 of the PG provider already switched to generating UUIDv7 values by default (see release note), which are significantly better for database indexes. PostgreSQL 18 also added the uuidv7() built-in function, which allows database generation of UUIDv7 values. In EFCore.PG 10, if you configure the provider to target PG 18 (.UseNpgsql("...", o => o.SetPostgresVersion(18, 0))), the provider will also translate Guid.CreateVersion7() to that function.

Support for the cube extension

PostgreSQL bundles the cube extension, which provides a multi-dimensional cube data type and associated operations. EFCore.PG 10 now fully supports the cube type out-of-the-box, simply add an NpgsqlCube property to your entity type:

public class Blog
{
    ...

    public NpgsqlCube Cube { get; set; }
}

Most translations over the type are supported as well. Thanks to @kirkbrauer for contributing this feature!

Other new features

  • NodaTime LocalDate.At() and LocalDate.AtMidnight() are now translated.

See the 10.0.0 milestone for the full list of Npgsql EF provider issues.

Breaking changes

  • EF.Functions.Network() and EF.Functions.Merge() have been changed to return the new .NET IPNetwork instead of the obsolete NpgsqlCidr. The new IPNetwork type works with all other functions as well (but no breaking changes were necessary).
  • The PostgreSQL network type cidr is now scaffolded to the new .NET IPNetwork type. The older Npgsql representation for cidr - the NpgsqlCidr type - has been obsoleted and will be removed in a future release.
Edit this page