0

I'm building a Blazor App with dotet core 3.1, ef core 3.14 and postgres 12. Initially I had been using the mssql localdb that comes with Visual Studio to build the app and everything was going well, but then I changed over to postgres, ran the migrations fine and the tables were all created but when I go to add data to the tables I get the "Duplicate key value violates unique constraint" error. I think this is because when you create a new object in dotnet core it initialises the Id field with 0 and postgres doesn't seem to understand it needs to change that to the next available number in the table. So far I've tried adding the following to my dbcontext OnModelCreating as per the documentation here https://www.npgsql.org/efcore/modeling/generated-properties.html.

modelBuilder.Entity<Grade>()
        .Property(p => p.Id)
        .UseIdentityAlwaysColumn();

as well as the following in a seperate attempt

 modelBuilder.Entity<Grade>()
        .Property(p => p.Id)
        .ValueGeneratedOnAdd();

Neither of those solved the problem so I also tried adding the following decoration to the Id field in my Model.

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

This is the full error message in case it helps

    fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (14ms) [Parameters=[@p0='?' (DbType = Double), @p1='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Grades" ("MinimumSalary", "Name")
      VALUES (@p0, @p1)
      RETURNING "Id";
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'EmployeeManagement.Api.Controllers.PayrollContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Grades"
         at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
         at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
        Exception data:
          Severity: ERROR
          SqlState: 23505
          MessageText: duplicate key value violates unique constraint "PK_Grades"
          Detail: Key ("Id")=(1) already exists.
          SchemaName: public
          TableName: Grades
          ConstraintName: PK_Grades
          File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\access\nbtree\nbtinsert.c
          Line: 570
          Routine: _bt_check_unique
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Grades"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 23505
    MessageText: duplicate key value violates unique constraint "PK_Grades"
    Detail: Key ("Id")=(1) already exists.
    SchemaName: public
    TableName: Grades
    ConstraintName: PK_Grades
    File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\access\nbtree\nbtinsert.c
    Line: 570
    Routine: _bt_check_unique
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)```
H H
  • 263,252
  • 30
  • 330
  • 514
Fearthainn
  • 111
  • 1
  • 6
  • Did you try to generate a migration after your context changes or drop and recreate the DB ? Because if you don't the DB's schema is unchanged and the DB will not generate a new id by magic. – agua from mars May 29 '20 at 14:12
  • Yes, nuked the migrations and re-created from scratch after every change and then ran update-database for new db instance each time. – Fearthainn May 29 '20 at 15:55
  • And is the migration generates code to tell the DB to creates an Identity column with auto-increment ? – agua from mars May 29 '20 at 16:01
  • @aguafrommars It seems to, below is an excerpt from the migration that was created. Also if you do a manual insert in the db without the Id field it will autoincrement. ` b.Property("Id") .ValueGeneratedOnAdd() .HasColumnType("integer") .HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn); ` – Fearthainn May 29 '20 at 17:34

2 Answers2

1

For anyone else that has the same issue, Postgres has a limitation where if you seed the db it doesn't auto-increment the unique key counter, the work-around is to seed the DB with negative values.

Fearthainn
  • 111
  • 1
  • 6
  • 2
    There is no such thing in Postgres that's called "seed the db". What exactly are you talking about? Are you looking for this https://stackoverflow.com/questions/244243 –  Jul 17 '20 at 09:41
  • 1
    Sorry I realise now my answer is misleading, the issue is not with postgres but with the npgsql nuget package. Specifically when you call the Entity<>.Hasdata() method in the OnModelCreating() method if you generate primary key values that start from 1, when you next attempt to insert values into the database with ef-core it will attempt to re-use those values, the workaround is to call those methods but use negative values for the primary keys. this is the github issue where they also discuss other workarounds that can be used. https://github.com/npgsql/efcore.pg/issues/367 – Fearthainn Jul 17 '20 at 10:31
  • Does indeed sound as if that package inserts **without** using the sequence. You can use the answer from the linked question to syn the sequence with the real values. –  Jul 17 '20 at 10:36
  • Could you provide a sample on how to seed it with negative values? Apologies if what I'm asking is out of the scope but I believe it will provide the full context to this answer. – Sebastian Inones Apr 18 '22 at 10:41
0

if you pre-seed database with Id columns, this issue occurs

you can fix this like that

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
Mehmet Erdoğdu
  • 190
  • 2
  • 8