Error "Duplicate key value violates unique constraint" when adding with EF Core & Postgresql

Question

I'm trying to enable my application to be able to use SQL Server or Postgresql. My application is using Npgsql.EntityFrameworkCore.PostgreSQL version 8 and EF Core version 8.01.

When I am using Postgresql and adding a range of entities to a table, it fails with the following error

[23505] ERROR: duplicate key value violates unique constraint

but the same code pointing to SQL Server is fine. The code is adding entities in a loop to a List and then trying to add them to the database using AddRangeAsync.

The error occurs when SaveChangesAsync() is called. I think the issue is the value of the identity column of the added entities are all zeros and the code is trying to add multiple entities with an Id of zero to the table.

If I add one of these entities in Postgresql in PgAdmin via a SQL script it is added, and the Id is correctly incremented. I tried various solutions such as adding [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to the Id property of the entity class, adding entity.Property(e => e.Id).ValueGeneratedOnAdd(), and explicitly adding the entity with an Id of zero. None had any effect.

Please help.

Code:

await _dbContext.Database.ExecuteSqlInterpolatedAsync($"DELETE FROM dbo.PropertyStations WHERE (PropertyId = {property.Id})").ConfigureAwait(false);
await _dbContext.SaveChangesAsync().ConfigureAwait(false);

if (property.Stations != null && property.Stations.Any())
{
    List<PropertyStation> pss = new List<PropertyStation>();

    property.Stations.ForEach(s =>
    {
        PropertyStation ps = new PropertyStation()
        {
            PropertyId = prop.PropertyId,
            StationId = s.StationId
        };

        pss.Add(ps);
    });

    if (pss.Any())
    {
        await _dbContext.PropertyStations.AddRangeAsync(pss).ConfigureAwait(false);
        await _dbContext.SaveChangesAsync().ConfigureAwait(false);
    }
}

Table definition:

CREATE TABLE IF NOT EXISTS dbo.propertystations
(
    id integer NOT NULL DEFAULT nextval('dbo.propertystations_id_seq'::regclass),
    propertyid integer NOT NULL,
    stationid integer NOT NULL,
    CONSTRAINT pk_propertystations PRIMARY KEY (id),
    CONSTRAINT fk_propertystations_properties 
        FOREIGN KEY (propertyid)
        REFERENCES dbo.properties (propertyid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fk_propertystations_stations 
        FOREIGN KEY (stationid)
        REFERENCES dbo.stations (stationid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Entity definition:

[Index(nameof(PropertyId), Name = "IX_PropertyStations")]
[Index(nameof(StationId), Name = "IX_PropertyStations_1")]
[Index(nameof(PropertyId), nameof(StationId), Name = "IX_PropertyStations_2", IsUnique = true)]
public partial class PropertyStation
{
    [Key]
    //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int PropertyId { get; set; }
    public int StationId { get; set; }
}

If it works in SQL Server and it doesn't in PostgreSQL, the data in these databases is different. The error message is clear about this, your new content already exists, it's not unique. — Frank Heikens
– Frank Heikens, Commented Jan 18, 2024 at 4:11
DatabaseGeneratedOption.Identity only tells EF that the DBMS is supposed to generate a new value, maybe you forgot to configure the identity columns in Postgres — Mateus Zampol
– Mateus Zampol, Commented Jan 18, 2024 at 7:20

LanceM · Accepted Answer · 2024-01-21 00:36:50Z

1

In case it helps someone else, I'm posting what resolved this issue for me.

I changed the Id columns in my Postgresql database from type serial to identity (GENERATED ALWAYS AS IDENTITY).
I ensured that in my DbContext, I had the following for each entity with the above identity columns: entity.Property(e => e.Id).UseIdentityAlwaysColumn();

After doing that, it all worked as expected like it did with Sql Server.

answered Jan 21, 2024 at 0:36

LanceM

2,0087 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aryan Esmaili · Accepted Answer · 2024-10-08 10:16:23Z

just adding a solution that worked for me in case anyone is having the same issue:

I use Redis for caching. when I created a new record, I immediately cached in Redis. later when an incoming request came, I first checked Redis before Database and fetched the record from there. the tricky part is that you don't use EF to interact with Redis, so the records retrieved from Redis are NOT being tracked by EntityFramework. this creates a situation where when you execute SaveChanges on EF context, EF thinks this record is a new one, so it creates a whole new object for it and assigns an ID to it, later when EF sends this record to PostgreSQL, PostgreSQL checks the database and finds out it already has this record with the same info and an already existing ID,leading to this exception being thrown by PostgreSQL.

the solution:

just add the following line after retrieving a record that is not being tracked by EntityFramework:

_context.Attach(resultRecord);

you use _context.Attaach() when you know the entity already exists in the database and you do not want to modify its values (i.e., you don’t want EF to mark it as changed or to update the entity when SaveChanges is called). You just want EF to track the entity without assuming any changes have been made. thus this way you prevent EF from creating a whole new object based on this object's state.

Collectives™ on Stack Overflow

Error "Duplicate key value violates unique constraint" when adding with EF Core & Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related