Entity Framework：实现依赖ProjectId的自增字段、复合主键及自定义索引

需求明确 #

• Issue的Id1关联Project主键，Id2需按Id1分组自增（同一Project下的Issue，Id2从1开始依次递增）
• {Id1, Id2}作为Issue的复合主键
• 替代当前“统计同ProjectId的Issue数量设置Id2”的方案，解决并发场景下的重复问题
• 创建依赖其他字段的自定义索引

一、数据库层面最优实现（推荐） #

应用层统计数量的方式在高并发场景下容易出现Id2重复，数据库层面的原生支持能从根源解决问题。

1. 触发器自动生成Id2 #

以SQL Server为例，通过触发器在插入时自动计算当前Project下的最大Id2并递增：

-- 创建Issue表（先指定复合主键）
CREATE TABLE Issue (
    Id1 INT NOT NULL,
    Id2 INT NOT NULL,
    -- 其他业务字段
    FOREIGN KEY (Id1) REFERENCES Project(Id),
    PRIMARY KEY (Id1, Id2)
);

-- 创建插入触发器，自动生成分组自增的Id2
CREATE TRIGGER trg_Issue_GenerateId2
ON Issue
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 处理批量插入的情况，按Project分组生成连续Id2
    WITH InsertedWithRowNum AS (
        SELECT 
            Id1,
            ROW_NUMBER() OVER (PARTITION BY Id1 ORDER BY (SELECT NULL)) AS RowNum
        FROM inserted
    )
    INSERT INTO Issue (Id1, Id2)
    SELECT 
        iwrn.Id1,
        COALESCE(MAX(ii.Id2), 0) + iwrn.RowNum
    FROM InsertedWithRowNum iwrn
    LEFT JOIN Issue ii ON iwrn.Id1 = ii.Id1
    GROUP BY iwrn.Id1, iwrn.RowNum;
END;

插入时只需传入Id1，触发器会自动计算对应的Id2，无需应用层干预。

2. 数据库序列（部分数据库支持） #

比如PostgreSQL可以为每个Project创建独立序列，或者用全局序列结合分组逻辑，不过触发器方案兼容性更强。

二、ORM层面实现（以EF Core为例） #

如果必须在应用层处理，需通过事务+严格隔离级别避免并发问题：

1. 事务包裹的原子性插入 #

public async Task<Issue> AddIssueAsync(int projectId)
{
    using var transaction = await _dbContext.Database.BeginTransactionAsync(System.Data.IsolationLevel.Serializable);
    try
    {
        // 原子性查询当前Project下的最大Id2
        var maxId2 = await _dbContext.Issues
            .Where(i => i.Id1 == projectId)
            .MaxAsync(i => (int?)i.Id2) ?? 0;
        
        var newIssue = new Issue
        {
            Id1 = projectId,
            Id2 = maxId2 + 1
        };
        
        _dbContext.Issues.Add(newIssue);
        await _dbContext.SaveChangesAsync();
        await transaction.CommitAsync();
        return newIssue;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Serializable隔离级别会锁住查询范围，防止并发插入时的幻读，但会增加锁等待时间，仅适合低并发场景。

2. EF Core复合主键配置 #

在DbContext中显式配置复合主键：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置{Id1, Id2}为复合主键
    modelBuilder.Entity<Issue>()
        .HasKey(i => new { i.Id1, i.Id2 });
    
    // 配置与Project的关联关系
    modelBuilder.Entity<Issue>()
        .HasOne(i => i.Project)
        .WithMany(p => p.Issues)
        .HasForeignKey(i => i.Id1);
}

三、自定义索引创建 #

1. 数据库原生索引 #

如果需要基于Id1和其他业务字段创建索引（比如按Project分组查询最新Issue）：

-- 创建非聚集复合索引，包含常用查询字段减少键查找
CREATE NONCLUSTERED INDEX IX_Issue_Id1_CreatedTime
ON Issue (Id1, CreatedTime DESC)
INCLUDE (Id2, Title);

2. EF Core中配置索引 #

通过Fluent API直接配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置自定义复合索引
    modelBuilder.Entity<Issue>()
        .HasIndex(i => new { i.Id1, i.CreatedTime })
        .IsDescending(i => new { i.CreatedTime })
        .IncludeProperties(i => new { i.Id2, i.Title });
}

方案对比 #

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