Breaking change in Entity Framework Core provider for PostgreSQL 3.0

Beware when upgrading form Npgsql.EntityFrameworkCore.PostgreSQL 2.2 and running PostgreSQL 9.6 or earlier.

Background

Recently I wanted to add ASP.NET Core Identity to an application using PostgreSQL as a data storage. Since I prefer to manage database schema and migrations separately from the application (currently using DbUp) I decided to use Entity Framework Core migrations to generate the SQL script for it.

Generating the migration SQL script

Using the Entity Framework Core tools 3.x is straight-froward. (Given that you have added the necessary dependencies and defined your application’s IdentityDbContext)

First make sure it is installed:

1
dotnet tool install --global dotnet-ef

Initialize the migrations in the project where your DbContext is:

1
dotnet ef migrations add init

Then generate the SQL script for the DbContext (called ApplicationDbContext in my case):

1
dotnet ef migrations script -c ApplicationDbContext -o migration_script.sql

Removed the SQL related to __EFMigrationsHistory and my migration script was ready. Then removed the migrations folder from my project and was ready to go.

Ran the SQL script and… it failed!

The issue

I was quite surprized to see that the error was in the SQL:

1
ERROR: syntax error at or near "GENERATED"

And the offending statement was:

1
CREATE TABLE "AspNetRoleClaims" (
2
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
3
...

which on further research made perfect sense because this is a new syntax GENERATED AS IDENTITY for creating auto-incremented column available in PostgreSQL 10 and I was running version 9.6.

I was using Npgsql.EntityFrameworkCore.PostgreSQL 3.1.1 and going through it I found a documented breaking change in 3.0.0:

The default value generation strategy has changed from the older SERIAL columns to the newer IDENTITY columns, introduced in PostgreSQL 10.

Details can be found in the Release Notes.

Fortunately the fix was easy - just had to specify the PostgreSQL version I was targeting:

1
public class ApplicationDbContext : IdentityDbContext
2
{
3
    public ApplicationDbContext(DbContextOptions options) : base(options)
4
    {
5
    }
6

7
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
8
    {
9
        optionsBuilder.UseNpgsql("DefaultConnection", o => o.SetPostgresVersion(9, 6));
10
    }
11
}

After re-generating the migrations the older SERIAL statement was used:

1
CREATE TABLE "AspNetRoleClaims" (
2
    "Id" serial NOT NULL,
3
...

and everything went fine.

Conclusion

PostgreSQL 10 introduced GENERATED AS IDENTITY syntax aiming to replace SERIAL for automatic assignment of unique value to a column. Npgsql.EntityFrameworkCore.PostgreSQL 3.0.0 takes advantage of it and uses it as a default when generating SQL migrations. Upgrading dependencies caught me off guard this time but the breaking change was well documented and my problem easily fixed.