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; }
}

Answer 1

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

1. I changed the Id columns in my Postgresql database from type serial to identity (GENERATED ALWAYS AS IDENTITY).
2. 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.