使用EF Core + PostgreSQL添加实体时触发重复键约束违反错误

使用Npgsql.EntityFrameworkCore.PostgreSQL 8 + EF Core 8.0.1，批量添加PropertyStation实体调用SaveChangesAsync()时抛出[23505] ERROR: duplicate key value violates unique constraint，相同代码在SQL Server运行正常。

推测原因是新增实体的标识列Id均为0，导致多条Id=0的实体冲突。但直接用PgAdmin执行SQL插入单条实体时，Id能正确自增。

尝试过的无效方案：

• 给Id属性添加[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
• 配置entity.Property(e => e.Id).ValueGeneratedOnAdd()
• 显式设置Id为0

相关代码与定义 #

业务代码 #

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

PostgreSQL表定义 #

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
)

实体类定义 #

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

1. 明确配置PostgreSQL自增列 #

在DbContext的OnModelCreating方法中，用Npgsql专属配置让EF Core正确识别PostgreSQL的自增逻辑：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PropertyStation>(entity =>
    {
        entity.HasKey(e => e.Id);
        entity.Property(e => e.Id)
            .ValueGeneratedOnAdd()
            .UseIdentityColumn(); // 强制EF使用PostgreSQL的IDENTITY语义
    });
}

2. 验证序列与表的关联 #

执行SQL检查序列是否正确绑定到表的Id列：

SELECT pg_get_serial_sequence('dbo.propertystations', 'id');

若返回空或错误值，重新关联序列：

ALTER SEQUENCE dbo.propertystations_id_seq OWNED BY dbo.propertystations.id;

3. 改用可空Id类型避免显式赋值 #

将实体的Id改为可空类型，让EF Core明确知道该值由数据库生成：

public int? Id { get; set; }

同时保留上述OnModelCreating中的配置。

4. 重置序列当前值 #

如果之前手动插入过数据导致序列值滞后，执行SQL重置序列：

SELECT setval('dbo.propertystations_id_seq', (SELECT MAX(id) FROM dbo.propertystations));

内容的提问来源于stack exchange，提问作者LanceM