PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Problem saving data to database using EF Core after inserting data using a SQL script because it tries to insert at ID = 1, which is already inserted

I’m encountering an issue while trying to insert data using Entity Framework Core in my .NET application. I have a table that I populate with data using an independent SQL script, which inserts records with primary keys (PKs) ranging from 1 to 10.

When I attempt to insert new data using a POST request via EF Core, I get the following error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_NodeParameterTypes"`

The error indicates that EF Core is trying to insert a new record with a PK value that already exists in the database (e.g., PK = 1).

Context

  • I do not want to use seed data to initialize the database automatically when starting the application.
  • The issue arises only when the database contains existing data, and it does not occur if I start with an empty database.

Question

How can I resolve this issue? Is there a way to configure EF Core to handle existing PK values, or do I need to manage the PK generation manually?

Any guidance or suggestions would be greatly appreciated!

Thank you!

I tried to set the records of the scripts to use PK that are big numbers, close from the integer maximum, but it worried me having overflows.

Edit:

This postgresql make existing primary key auto incrementing when inserting come close to what I need, but I generate tables using EF and inserting using a SQL script executed by python, so I don’t know how i can execute this setval

Tags:

2

Answers


  1. 0

    It seems the issue is that EF Core is trying to insert records with a primary key (PK) that already exists in your database, which causes the duplicate key error. This often happens when you have pre-existing data, and EF Core doesn’t know it should start from the last available ID.

    Login or Signup to reply.
  2. 0

    If the name of your property is something like ID then EF Core will default to assuming that it is an auto-generated value. But if you use some other less expected name, then you will need to tell EF Core yourself that it is auto generated. The syntax for this has varied over time but these days I think looks something like this:

    protected override void OnModelCreating(
   ModelBuilder mb
) {
   mb.Entity<MyEntity>(eb => {
      eb.Property(e => e.PrimaryKeyProperty).ValueGeneratedOnAdd();
      
      ...
   });

   ...
}

    With annotations it would look something like this:

    public class MyEntity {
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int PrimaryKey { get; set; }

   ...
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search