Duplicate key value violates unique constraint (EF Core & PostgresSQL)

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)```
blazor
ef-core-3.1
postgresql-12
asked on Stack Overflow May 29, 2020 by Fearthainn

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0