使用PostgreSQL+EF Core时Price主键未按所属公司分组自增问题

你遇到的现象是PostgreSQL自增列的默认行为，不属于代码逻辑错误：

• 你当前配置的PriceId为SERIAL类型或者IDENTITY自增列，这类列底层依赖PostgreSQL的全局序列实现，每插入一条数据，全局序列就自增1，和数据所属的CompanyId没有关联，所以必然会出现全局连续递增的效果，无法直接支持按CompanyId分组独立自增。
  你可以打开PgAdmin的Prices表配置，查看PriceId列的默认值，会看到类似nextval('prices_price_id_seq'::regclass)的配置，这里的prices_price_id_seq就是全局共享的序列，所有插入操作共用这个序列的值，这就是问题的根因。

推荐两种常用的落地方式，可根据你的业务场景选择：

方案1：应用层手动生成分组自增序号 #

新增LocalPriceId字段作为每个公司下的独立自增序号，原有PriceId保留作为全局唯一主键，插入时手动计算当前公司的最大序号+1：

步骤1：修改Price实体 #

public class Price
{
    public int PriceId { get; set; }
    // 新增字段：公司内独立自增ID
    public int LocalPriceId { get; set; }
    public DateTime TimeStamp { get; set; }
    public double Value { get; set; }
    public int CompanyId { get; set; }
    public virtual Company Company { get; set; }
}

步骤2：插入时计算序号 #

var companyId = 1;
// 查询当前公司的最大LocalPriceId，没有数据时默认从1开始
var maxLocalId = Context.Prices
    .Where(p => p.CompanyId == companyId)
    .Max(p => (int?)p.LocalPriceId) ?? 0;

var company = Context.Companies.FirstOrDefault(c => c.CompanyId == companyId);
company.Prices.Add(new Price 
{
    Value = 123.45, 
    TimeStamp = DateTime.Now,
    LocalPriceId = maxLocalId + 1
});
Context.SaveChanges();

注意：高并发插入场景下，需要将查询和插入逻辑放在可重复读隔离级别的事务中，避免多个请求拿到相同的maxId导致主键冲突。

方案2：数据库触发器自动生成分组序号 #

如果不想在应用层处理逻辑，可以通过PostgreSQL触发器在插入数据时自动生成分组自增序号，完全不需要修改插入代码：

步骤1：执行SQL创建触发器函数和触发器 #

-- 新增LocalPriceId字段（如果还没加）
ALTER TABLE Prices ADD COLUMN LocalPriceId INT NOT NULL;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION generate_local_price_id()
RETURNS TRIGGER AS $$
BEGIN
    SELECT COALESCE(MAX(LocalPriceId), 0) + 1 INTO NEW.LocalPriceId
    FROM Prices
    WHERE CompanyId = NEW.CompanyId;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定插入前触发器
CREATE TRIGGER trg_prices_set_local_id
BEFORE INSERT ON Prices
FOR EACH ROW EXECUTE FUNCTION generate_local_price_id();

步骤2：EF Core配置字段为数据库生成 #

在你的DbContext的OnModelCreating方法中添加如下配置，避免EF Core插入时给LocalPriceId赋值：

modelBuilder.Entity<Price>()
    .Property(p => p.LocalPriceId)
    .ValueGeneratedOnAdd();

配置完成后，原有插入代码不需要任何修改，数据库会自动为每条新插入的Price生成本公司内的自增序号。

可选：使用复合主键 #

如果不需要全局唯一的PriceId字段，也可以直接用CompanyId + LocalPriceId作为Price表的复合主键，删除原有PriceId字段即可，EF Core配置如下：

modelBuilder.Entity<Price>()
    .HasKey(p => new { p.CompanyId, p.LocalPriceId });

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