Using EF Core to update primary key

Question

Postgres database contains product table with natural primary key:

create table product (
  productcode char(10) primary key,
  price numeric(12,2),
  ... lot of other columns
  );

and other similar tables with natural primary keys.

ASP.NET 5 MVC application is used to update it using EF Core with
Npgsql data provider.

If product code is also changed, EF Core throws error

The property 'Product.Productcode' is part of a key and so cannot be
modified or marked as modified. To change the principal of an existing
entity with an identifying foreign key, first delete the dependent and
invoke 'SaveChanges', and then associate the dependent with the new
principal.

Product code is used as foreign key in other tables (with ON UPDATE CASCADE clause) so it cannot deleted.
Database structure change is not an option.

How to allow update primary key field also ?

Some ideas:

1. Block this check in EF Core, e.q setting old value to same as new value forcibly before update.
2. Set primary key to some other value before saving changes.
3. Force EF Core to create update statement and execute it manally
4. Use some EF Core extension or other framework.
5. Change Npgsql EF core provider to allow this.

Which is best way to implement this without changing database structure?

Answer 1

Just as the database prevents delete, it will not allow update, which in a database is a delete followed by an insert.

To change the key, insert a new row with the new key, update all the dependent row to the new key, then delete the original row. This should all be done as a single transaction. As this can easily cause deadlock detection, you will need retry logic.

I’d use raw sql to perform this action, as you want to use set operations.