8.0 Release Notes

Npgsql.EntityFrameworkCore.PostgreSQL version 8.0 is out and available on nuget.org.

Full support for primitive collection querying

One of PostgreSQL unique features as a relational database is its rich support for array types, which can be stored just like any other basic data type and queried. The Npgsql EF provider has supported mapping .NET arrays and Lists to PostgreSQL arrays for a very long time; however, with the introducion of rich primitive collection support in EF itself, Npgsql's support for arrays was extended to include full querying capabilities.

Column collections

On to the code! You can now use any LINQ operator - or chain of operators - on top of a primitive array or list property. For example, the following queries for all blogs who have at least 3 tags starting with "x":

class Blog
{
    ...
    public List<string> Tags { get; set; }
}

var blogs = await ctx.Blogs
    .Where(b => b.Tags.Count(t => t.StartsWith("x")) > 3)
    .ToListAsync();

This is now translated to the following SQL:

SELECT b."Id", b."Tags"
FROM "Blogs" AS b
WHERE (
    SELECT count(*)::int
    FROM unnest(b."Tags") AS t(value)
    WHERE t.value LIKE 'x%') > 3

Such complex translations rely on the PostgreSQL unnest function, which allows expanding a PostgreSQL array to a set of rows; once that's done, the array values can be queried with regular SQL.

We can go further, querying for all blogs whose tags - or any of their posts' tags - contains a certain value:

var blogs = await ctx.Blogs
    .Where(b => b.Tags.Union(b.Posts.SelectMany(p => p.Tags))
        .Contains("foo"))
    .ToListAsync();

This uses unnest both on the blog's tags on and all its posts' tags (via a lateral join), to then do a UNION On results and check whether the value is contained.

SELECT b."Id", b."Tags"
FROM "Blogs" AS b
WHERE 'foo' IN (
    SELECT t.value
    FROM unnest(b."Tags") AS t(value)
    UNION
    SELECT t1.value
    FROM "Post" AS p
    JOIN LATERAL unnest(p."Tags") AS t1(value) ON TRUE
    WHERE b."Id" = p."BlogId"
)

Parameter collections

But that's not all... The new primitive collection support works not just on columns, but also on parameterized lists. Previously, special and limited support existed for translating Contains over a parameterized list, as follows:

var ids = new[] { 1, 2, 3 };

var blogs = await ctx.Blogs
    .Where(b => ids.Contains(b.Id))
    .ToListAsync();

This has now been extended to allow composing any LINQ operator. For example, the following query queries for any blogs which have at least one tag, where that tag starts with a list of possible prefixes:

var prefixes = new[] { "f", "b" };

var blogs = await ctx.Blogs
    .Where(b => prefixes.Any(p => b.Tags.Any(t => t.StartsWith(p))))
    .ToListAsync();

Complex, I know! This translates to the following SQL:

SELECT b."Id", b."Tags"
FROM "Blogs" AS b
WHERE EXISTS (
    SELECT 1
    FROM unnest(@__prefixes_0) AS p(value)
    WHERE EXISTS (
        SELECT 1
        FROM unnest(b."Tags") AS t(value)
        WHERE p.value IS NOT NULL AND left(t.value, length(p.value)) = p.value))

Note the two usages of unnest here: one is used to expand the Tags column, whereas the other is used to expand the @__prefixes_0 array parameter that EF sends. All this machinery works together to make your LINQ query execute correctly.

Inline collections

Finally, support was added to inline collections, which are collections specified inside the query. For example, let's say that the list of tag prefixes in the previous query were always the same; in that case, we'd inline the prefixes variable as follows:

var blogs = await ctx.Blogs
    .Where(b => new[] { "f", "b" }.Any(p => b.Tags.Any(t => t.StartsWith(p))))
    .ToListAsync();

This translates to the following:

      SELECT b."Id", b."Tags"
      FROM "Blogs" AS b
      WHERE EXISTS (
          SELECT 1
          FROM (VALUES ('f'::text), ('b')) AS v("Value")
          WHERE EXISTS (
              SELECT 1
              FROM unnest(b."Tags") AS t(value)
              WHERE left(t.value, length(v."Value")) = v."Value"))

Note that the unnest over the parameter has been replaced with a standard SQL VALUES construct, which allows constructing a set of rows inline, inside the query.

More information

Exciting stuff! We hope this helps you use LINQ to model and better interact with your database.

The primitive collection section in the EF 8.0 What's New page.
.NET Blog post on primitive collections
PostgreSQL-specific information on primitive collections, including some specialized translations done for PostgreSQL.

EF JSON support via ToJson()

Version 8.0 also brings support for EF7's JSON columns feature (ToJson), which allows mapping JSON columns via owned entity types. While the Npgsql EF provider has had POCO JSON mapping for a very long time, the full modeling of the new ToJson() allows for a more powerful mapping strategy, with more query and update features. ToJson is the recommended way to map JSON going forward.

You can read more on ToJson in the Npgsql JSON doc page, and in the EF7 what's new docs. Compared to the traditional Npgsql-specific POCO approach, a much wider range of LINQ queries can now be translated. For example, you can now compose LINQ operators over collections within JSON documents:

var blogs = await ctx.Customers
    .Where(b => b.Details.Orders.Any(o => o.Price > 8))
    .ToListAsync();

Note that this composes a LINQ operator - Any - on top of Orders, which is a list nested inside a JSON document. This translates to the following SQL:

SELECT c."Id", c."Details"
FROM "Customers" AS c
WHERE EXISTS (
    SELECT 1
    FROM ROWS FROM (jsonb_to_recordset(c."Details" -> 'Orders') AS (
        "Price" numeric,
        "ShippingAddress" text
    )) AS o
    WHERE o."Price" > 8.0)

Note the jsonb_to_recordset function, which drills into the JSON document, finds the Orders property, and then expands that out to a set of rows, which can then be queried via regular SQL.

Similarly, updates via EF's SaveChanges() are better, and can leverage partial updating to only patch the part of the JSON document which has changed. For example, let's assume that we load some customer with a JSON column, and change a single property within that JSON data:

var customer = await ctx.Customers.SingleAsync(c => c.Details.Name == "Foo");
customer.Details.Name = "Bar";
await ctx.SaveChangesAsync();

Rather than needlessly sending the entire JSON document back to PostgreSQL, the EF provider uses the jsonb_set function to patch the specific property which changed, which is more efficient:

UPDATE "Customers" SET "Details" = jsonb_set("Details", '{Name}', @p0)
WHERE "Id" = @p1;

Other new features

Version 8.0 contains many other smaller features and bug fixes, both on the EF side and on the Npgsql provider side. See the 8.0.0 milestone for the full list of Npgsql EF provider issues.

Breaking changes

Note: version 8.0 of the lower-level Npgsql ADO.NET driver, which is used by the EF provider, also has some breaking changes. It's recommended to read the release notes for that as well.

JSON POCO and other dynamic features now require an explicit opt-in

Because of the NativeAOT and trimming work done for Npgsql 8.0 (release notes), certain features now require an explicit opt-in, which you must add either on your NpgsqlDataSourceBuilder or on NpgsqlConnection.GlobalTypeMapper:

PostgreSQL type	Default .NET type
JSON POCO mapping, JsonNode and subtypes	EnableDynamicJson
Unmapped enums, ranges, multiranges	EnableUnmappedTypes
Read PostgreSQL records as .NET tuples	EnableRecordsAsTuples

Existing code using the above features will start throwing exceptions after upgrading to version 8.0 of the EF Core provider; the exceptions provide explicit guidance on how to add the opt-ins.

Note that EF Core itself is not yet compatible with NativeAOT, and Npgsql can only be used in NativeAOT applications without EF Core.

Default PostgreSQL compatibility mode has been bumped from 12 to 14

This means that the provider assumes at least PostgreSQL 14; if you're running against an older version, explicitly specify the PostgreSQL version when configuring your context:

optionsBuilder.UseNpgsql("<connection string>", o => o.SetPostgresVersion(12, 0))

Obsoleted HasPostgresArrayConversion

With EF 8.0 introducing first-class support for primitive collections, the PostgreSQL driver aligned its PostgreSQL array support to use that. As a result, HasPostgresArrayConversion can no longer be used to configure value-converted arrays; instead, the new standard EF mechanism can be used.

For example, the following Npgsql-specific code would configure value conversion for a property of type MyType[] to a PostgreSQL array of strings in EF Core 6 or 7:

modelBuilder.Entity<Blog>().Property(b => b.ValueConvertedArray)
    .HasPostgresArrayConversion(x => x.ToString(), s => MyType.Parse(s));

The same can now achieved with the following standard EF 8 code:

modelBuilder.Entity<Blog>().PrimitiveCollection(b => b.ValueConvertedArray)
    .ElementType()
    .HasConversion(typeof(MyConverter));

class MyConverter : ValueConverter<MyType, string>
{
    public MyConverter()
        : base(x => x.ToString(), s => MyType.Parse(s))
    {
    }
}

`cidr` now maps to `NpgsqlCidr` instead of `ValueTuple<IPAddress, int>`

As part of improving Npgsql's support for the PostgreSQL network mappings, the PostgreSQL cidr type now maps to the newly-introduced NpgsqlCidr, and can no longer be mapped to ValueTuple<IPAddress, int>.

Contributors

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

Milestone 8.0.0

Contributor	Assigned issues
@roji	34
@zpaks	1

Table of Contents