EF Core with PostgreSQL: the Npgsql Provider, snake_case, Array/JSON Mapping, Enums, xmin Concurrency and Migration Differences

Version note: The code in this post was built with .NET 10, Entity Framework Core 10.0.15, Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 and EFCore.NamingConventions 10.0.1 and run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02.

Guess First

We moved the catalog service of Bookstore, the online bookstore used as the shared example across this site's series, from SQL Server to PostgreSQL. The entities and the LINQ queries are all the same; we only wrote UseNpgsql instead of UseSqlServer. The catalog has a book called "The Hobbit #2", and the user types it into the search box in lower case:

IQueryable<Book> exact = db.Books.Where(b => b.Title == "the hobbit #2");
IQueryable<Book> ilike = db.Books.Where(b => EF.Functions.ILike(b.Title, "the hobbit #2"));
Console.WriteLine($"== : {await exact.CountAsync()} rows");
Console.WriteLine($"ILike : {await ilike.CountAsync()} rows");

In SQL Server the first query finds the book, because the default collation is case-insensitive. How many rows come back in PostgreSQL?

== : 0 rows
ILike : 1 rows
SELECT b.book_id, b.created_at, b.format, b.isbn, b.price, b.public_id, b.search_vector, b.tags, b.title, b.xmin, b.details
FROM catalog.books AS b
WHERE b.title ILIKE 'the hobbit #2'

None. Comparisons in PostgreSQL are case-sensitive by default. The code compiled, the tests passed if they were written against SQL Server, but the search silently returns nothing. In this post we look at what's different when you use EF Core with PostgreSQL: setting up the provider and snake_case naming, PostgreSQL-specific mappings (arrays, JSON, enums, uuid), concurrency with xmin, full-text search, and how migrations behave on PostgreSQL.

The Provider, the Data Source and snake_case

In post 02 we saw that EF Core's default naming produces names on PostgreSQL that need quotes, such as "Books" and "BookId". The EFCore.NamingConventions package turns table, column, key and index names into snake_case. The entities in this post live in their own catalog schema so they don't touch the existing lab tables:

public sealed class Book
{
    public int BookId { get; set; }
    public Guid PublicId { get; set; }
    public required string Isbn { get; set; }
    public required string Title { get; set; }
    public decimal Price { get; set; }
    public BookFormat Format { get; set; }
    public List<string> Tags { get; set; } = [];
    public BookDetails Details { get; set; } = new();
    public DateTime CreatedAt { get; set; }
    public uint Version { get; set; }
    public NpgsqlTsVector SearchVector { get; set; } = null!;
}

public static DbContextOptions<BookstoreContext> CreateOptions(string connectionString) =>
    new DbContextOptionsBuilder<BookstoreContext>()
        .UseNpgsql(connectionString, npgsql => npgsql.MapEnum<BookFormat>("book_format", "catalog"))
        .UseSnakeCaseNamingConvention()
        .Options;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("catalog");
    modelBuilder.Entity<Book>(book =>
    {
        book.HasAlternateKey(b => b.PublicId);
        book.Property(b => b.PublicId).ValueGeneratedOnAdd(); // only primary keys get a generated Guid by default
        book.HasIndex(b => b.Isbn).IsUnique();
        book.Property(b => b.Isbn).HasMaxLength(13);
        book.Property(b => b.Price).HasPrecision(10, 2);
        book.ComplexProperty(b => b.Details, details => details.ToJson());
        book.Property(b => b.Version).IsRowVersion();
        book.HasIndex(b => b.Tags).HasMethod("gin");
        book.HasGeneratedTsVectorColumn(b => b.SearchVector, "english", b => new { b.Title })
            .HasIndex(b => b.SearchVector)
            .HasMethod("gin")
            .IsCreatedConcurrently();
    });
}

In an ASP.NET Core application these settings go into AddDbContext; passing the NpgsqlDataSource we registered with dependency injection in post 11 through UseNpgsql(dataSource, ...) lets EF Core share the pool and type mapping settings with the Dapper code. If you use a data source, the enum has to be mapped in both places. The SQL the migration produces:

CREATE TYPE catalog.book_format AS ENUM ('audiobook', 'ebook', 'hardcover', 'paperback');

CREATE TABLE catalog.books (
    book_id integer GENERATED BY DEFAULT AS IDENTITY,
    public_id uuid NOT NULL,
    isbn character varying(13) NOT NULL,
    title text NOT NULL,
    price numeric(10,2) NOT NULL,
    format catalog.book_format NOT NULL,
    tags text[] NOT NULL,
    created_at timestamp with time zone NOT NULL,
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', title)) STORED NOT NULL,
    details jsonb NOT NULL,
    CONSTRAINT pk_books PRIMARY KEY (book_id),
    CONSTRAINT ak_books_public_id UNIQUE (public_id)
);

CREATE UNIQUE INDEX ix_books_isbn ON catalog.books (isbn);

CREATE INDEX ix_books_tags ON catalog.books USING gin (tags);

A few details stand out. The int key became the IDENTITY we recommended in post 03; the provider uses BY DEFAULT, and if you want ALWAYS there's UseIdentityAlwaysColumn(). DateTime maps to timestamptz, List<string> to text[], and the complex type to jsonb. There's no Version column in the table: xmin is a system column every table already has. The enum values were created in alphabetical order; PostgreSQL enums sort by declaration order, so ORDER BY format doesn't follow the order of the C# enum. For search_vector the provider wrote STORED itself; as we saw in post 10, the default in 18 would be virtual.

Array, JSON, Enum and uuid Mapping

The ef-seed command copies the first 2,000 books of the lab into catalog.books with EF Core, using the application role bookstore_app:

dotnet tool restore
$env:BOOKSTORE_PG_OWNER = "Host=localhost;Port=5432;Database=bookstore;Username=bookstore_owner;Password=<owner-password>"
dotnet ef database update --project src/Bookstore.PgLab --context Bookstore.PgLab.Catalog.BookstoreContext
dotnet run --project src/Bookstore.PgLab -- ef-seed
dotnet run --project src/Bookstore.PgLab -- ef-query

saved 2000 books; first PublicId 01a1d7f6-7ffc-721d-8bb0-ba5eea9e74f7 (version 7), xmin 2493

Tags.Contains: 200 rows
SELECT b.book_id, b.created_at, b.format, b.isbn, b.price, b.public_id, b.search_vector, b.tags, b.title, b.xmin, b.details
FROM catalog.books AS b
WHERE b.tags @> ARRAY['fantasy']::text[]

Details.Pages: 11 rows
SELECT b.book_id, b.created_at, b.format, b.isbn, b.price, b.public_id, b.search_vector, b.tags, b.title, b.xmin, b.details
FROM catalog.books AS b
WHERE (CAST(b.details ->> 'Pages' AS integer)) > 1000 AND b.format = 'ebook'::catalog.book_format

ExecuteUpdate on a JSON property: 1 row

The provider generated the Guids on the client as sequential version 7 values; we recommended v7 for index density in post 03. By default only the primary key gets this: in our first attempt PublicId, an alternate key, stayed empty and on the second book we got "another instance with the same key value for {'PublicId'} is already being tracked", which is why ValueGeneratedOnAdd() is there. If you want the value generated on the server, PostgreSQL 18 lets you use HasDefaultValueSql("uuidv7()").

Tags.Contains("fantasy") was translated to the @> operator and can use the GIN index. This translation changed in provider 10: the default is now 'fantasy' = ANY(tags), but when a GIN index is defined on the column, the @> form that the index can use is kept. Check this for columns you add an index to later.

On the JSON side, EF Core 10's complex types (the EF Core 10 features post of the EF Core series) map to jsonb with provider 10, and queries are translated with ->>. Note that the snake_case convention isn't applied to keys inside the JSON; the document says "Pages". If you filter on this field often, as in post 05, you need an expression index with the same expression. Updating a single JSON field with ExecuteUpdate also works; the generated SQL looks like this in pg_stat_statements:

UPDATE catalog.books AS b
SET details = jsonb_set(b.details, $2, to_jsonb($1))
WHERE b.book_id = $3

Range types such as NpgsqlRange<DateOnly> ↔ daterange map the same way; the campaign periods from post 04 could be held in an entity with this type.

Optimistic Concurrency with xmin

In the concurrency post of the EF Core series we used SQL Server's rowversion column. PostgreSQL doesn't need a separate column: xmin, which we saw in post 08, is the ID of the transaction that last wrote the row, and it changes on every update. Marking a uint property with IsRowVersion() is enough. Selin and Emre open the same book and change its price:

both loaded book 2 with xmin 2493
      UPDATE catalog.books SET price = @p0
      WHERE book_id = @p1 AND xmin = @p2
      RETURNING search_vector, xmin;
Selin saved, new xmin 2495
      UPDATE catalog.books SET price = @p0
      WHERE book_id = @p1 AND xmin = @p2
      RETURNING search_vector, xmin;
Emre: DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded.

The UPDATE adds the xmin that was read to the WHERE and gets the new one back with RETURNING; the generated search_vector is read back too, because the title might have changed. Emre's update affected 0 rows and EF Core threw; how to resolve it (reload, show the user, or merge) is the same as in the EF Core series. One note: xmin was the same on all 2,000 rows, because it's the ID of the single transaction that wrote the whole seed; the token isn't unique per row, it answers "has this row changed since I read it". That's also the difference from the 40001 error of REPEATABLE READ in post 09: optimistic concurrency works across transactions, over the time the user spends on a screen.

Full-Text, ILike and PostgreSQL Functions

The EF Core counterpart of what we built in post 10 is HasGeneratedTsVectorColumn: it produces the generated tsvector column and its GIN index from the model. In queries you use EF.Functions:

IQueryable<Book> search = db.Books.Where(b => b.SearchVector.Matches(EF.Functions.WebSearchToTsQuery("english", "hobbits")));

full-text: 100 rows
SELECT b.book_id, b.created_at, b.format, b.isbn, b.price, b.public_id, b.search_vector, b.tags, b.title, b.xmin, b.details
FROM catalog.books AS b
WHERE b.search_vector @@ websearch_to_tsquery('english', 'hobbits')

There are three ways to deal with the case difference from the guess. EF.Functions.ILike is a one-query fix, but it can't use a B-tree index (the trigram GIN index from post 06 can). b.Title.ToLower() == term translates to lower(title); use it with an expression index on lower(title) as in post 06. The lasting fix is to give the column a case-insensitive collation: you can assign the nondeterministic ICU collation from post 03 (tr_ci; since 18 it works with LIKE too) to the column with UseCollation("tr_ci"). The collation itself has to be created in a migration with modelBuilder.HasCollation(...); we also saw in post 03 that the Turkish I/İ rule may not be what you want for fields like e-mail.

How Migrations Behave on PostgreSQL

Because DDL is transactional in PostgreSQL, EF Core runs every migration inside START TRANSACTION ... COMMIT: if something fails halfway through a migration, the schema isn't left half done. Most DDL runs inside a transaction in SQL Server too, but in PostgreSQL almost everything can be rolled back, CREATE TYPE and CREATE SCHEMA included.

The exception is CREATE INDEX CONCURRENTLY: it can't run inside a transaction. In our first attempt we put the search_vector column and the concurrent index into the same migration; while applying it, EF Core wrote:

The migration operation 'CREATE INDEX CONCURRENTLY ix_books_search_vector ON catalog.books USING gin (search_vector);
' from migration 'AddTitleSearch' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it can be applied again. Create a separate migration that contains just this operation.

EF Core commits the transaction and runs the index outside it; if the index build fails, the column has been added but the migration isn't recorded, and a retry fails because the column already exists. On top of that, the INVALID index from post 06 stays behind. Following the advice, we moved the concurrent index into its own migration; the final state in the snapshot:

INSERT INTO "__EFMigrationsHistory" (migration_id, product_version)
VALUES ('20261114024951_InitialCatalog', '10.0.15');

COMMIT;

CREATE INDEX CONCURRENTLY ix_books_search_vector ON catalog.books USING gin (search_vector);

INSERT INTO "__EFMigrationsHistory" (migration_id, product_version)
VALUES ('20261114024952_AddTitleSearchIndex', '10.0.15');

There's one more part we added by hand at the end of the first migration: in post 02 we gave the application role permissions only in the public schema, so the migration itself grants them for the new catalog schema:

// the application role gets DML on everything the owner creates in the catalog schema
migrationBuilder.Sql("GRANT USAGE ON SCHEMA catalog TO bookstore_app;");
migrationBuilder.Sql("GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA catalog TO bookstore_app;");
migrationBuilder.Sql("GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA catalog TO bookstore_app;");
migrationBuilder.Sql("ALTER DEFAULT PRIVILEGES IN SCHEMA catalog GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO bookstore_app;");
migrationBuilder.Sql("ALTER DEFAULT PRIVILEGES IN SCHEMA catalog GRANT USAGE, SELECT ON SEQUENCES TO bookstore_app;");

We run migrations with the owner role (BOOKSTORE_PG_OWNER, in the design-time factory) and the application with the restricted bookstore_app; the permission model is covered in detail in post 14. We'll cover the changes that come with EF Core 11 and Npgsql provider 11 in the version posts of the series once the provider's stable release is out; on the day this post was written EF Core 11 is out, but Npgsql provider 11 is still a release candidate (11.0.0-rc.3).

Production Notes

In production, run migrations not with Migrate() at application startup but in the deploy pipeline with dotnet ef migrations bundle or a generated SQL script, using the owner role. Read the script in review: check whether an ALTER TABLE rewrites the table and watch for the lock queue effect from post 09; give the session a lock_timeout.

On large tables add indexes with IsCreatedConcurrently(), each in its own migration. After a failed deploy, check pg_index for indexes left with indisvalid = false.

Turn on EnableRetryOnFailure against connection errors and the 40001/40P01 errors from post 09; wrap the transactions you open yourself in CreateExecutionStrategy() as we saw in post 09. Watch the plans of the queries EF Core generates with the method from post 07: TagWith, pg_stat_statements and auto_explain. Filtering on JSON fields and ILike easily produce queries without an index.

Common Pitfalls

Carrying over raw SQL written for SQL Server: square brackets, TOP, GETDATE() and ISNULL don't exist in PostgreSQL. Our SqlQueryRaw("SELECT TOP 5 [BookId] ...") attempt ended with 42601 syntax error at or near "5". List your raw SQL before moving it; the dialect differences post of the SQL Temelleri series gives the equivalents in one place.

Forgetting that comparisons are case-sensitive: an empty result without any error, as in the guess. Decide this up front for fields like e-mail and user name.

Assuming JSON keys are snake_case too: if raw SQL or another service reads the same document, "Pages" and "pages" are different keys.

Putting a concurrent index into the same migration as other schema changes: you end up cleaning up a half-applied migration by hand.

A Small Experiment

★☆☆ Add a Comic value to BookFormat and generate a new migration. Is the generated SQL ALTER TYPE ... ADD VALUE? Can this command run inside a transaction?

★★☆ Give the Title column the case-insensitive ICU collation from post 03 with UseCollation, apply the migration and run the == query from the guess again. What SQL does StartsWith produce, and does it work?

★★★ Add an expression index on Details.Pages as in post 05 with migrationBuilder.Sql (CONCURRENTLY, in a separate migration) and show with EXPLAIN that the query from ef-query uses it.

In the next post we move on to the ecosystem with Block C: the mechanism behind the extensions we used one by one in this series, such as pg_stat_statements, pg_trgm and unaccent, the restrictions on managed services, and which extension to install when.