Npgsql, Entity Framework Core and PostgreSQL 9
This post looks at the use of Npgsql for Entity Framework Core (EF Core), with an older version of PostgreSQL. I am writing an application that will use EF Core in .NET 5 to access PostgreSQL. For organisational reasons, the version of PostgreSQL we are using is 9.6. There are plans to upgrade in the coming months, but we are still running 9.6 in some situations.
Installing Npgsql (version 5.0.2 at the time of writing) was straight forward and building the EF Core migration was also easy - all as described in the documentation. However, applying the generated migration to the database raised the following error.
42601: syntax error at or near "GENERATED"
Reading further on npgsql.org, there is description of a change in how generated items are specified. An example generated item is an auto-incrementing column for an id. Since PostgreSQL 10, a new Identity generation mechanism was introduced and that is the default generation strategy used by Npgsql.
Without changes, the migrations created for Npgsql will not work with PostgreSQL 9 installations. They will use the new Indentity style of generation instead of the older Serial style of generation.
There are different ways that you can solve the issue, but the one I chose to use was adding a NpgsqlDbContextOptionsBuilder as the second parameter to the setup for Npgsql. This sets the version of PostgreSQL compatability to use.
services.UseNpgsql("connection_string", o => o.SetPostgresVersion(9, 6));
The "connection_string" is replaced by the relevant PostgreSQL connection information for the server.
Once that is in place, the generation will create a migration that uses the Serial generation strategy. That allows the EF Core database update command to run and correctly create the table structures for the migration.
Further information about the generation strategy is available in the Npgsql documentation.
