Entity Framework Core + SQL Server中TableGenerator实现优化与锁机制添加

一、EF是否内置支持TableGenerator？ #

Entity Framework（包括EF Core和EF6）没有内置直接对应Hibernate TableGenerator的主键生成策略，需要自行实现基于标识符表的主键生成逻辑。

二、优化后的实现方案（针对SQL Server） #

1. 数据库表设计 #

首先创建用于存储主键序列的表，确保表名作为唯一主键，避免重复：

CREATE TABLE [dbo].[TableGenerator]
(
    [TableName] NVARCHAR(128) NOT NULL PRIMARY KEY,
    [NextId] INT NOT NULL DEFAULT 1
)

2. EF实体与DbContext配置 #

定义对应实体并指定主键，确保EF能正确映射：

public class TableGenerator
{
    [Key]
    public string TableName { get; set; }
    public int NextId { get; set; }
}

public class YourDbContext : DbContext
{
    public DbSet<TableGenerator> TableGenerators { get; set; }
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 显式配置实体映射（可选，用于更精准控制）
        modelBuilder.Entity<TableGenerator>()
            .ToTable("TableGenerator")
            .HasKey(t => t.TableName);
    }
}

3. 核心主键生成方法（带并发锁） #

方案一：原子MERGE语句（推荐）

直接使用SQL Server的MERGE语句实现原子性的插入或更新，无需手动管理事务和锁，性能更优：

public int GenerateKey(string tableName)
{
    var sql = @"
        MERGE TableGenerator AS target
        USING (SELECT @TableName AS TableName) AS source
        ON (target.TableName = source.TableName)
        WHEN MATCHED THEN
            UPDATE SET target.NextId = target.NextId + 1
        WHEN NOT MATCHED THEN
            INSERT (TableName, NextId)
            VALUES (source.TableName, 1)
        OUTPUT inserted.NextId;";

    var param = new SqlParameter("@TableName", tableName);
    return Database.ExecuteSqlRaw(sql, param);
}

该语句在数据库层面完成所有操作，确保并发场景下不会生成重复主键。

方案二：EF事务+行级锁

如果更倾向于使用EF的API而非原生SQL，可以通过事务和UPDLOCK, HOLDLOCK锁定行，保证查询与更新的原子性：

public int GenerateKey(string tableName)
{
    using var transaction = Database.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
    try
    {
        // 锁定目标行，防止其他线程并发修改
        var generator = TableGenerators
            .FromSqlRaw("SELECT * FROM TableGenerator WITH (UPDLOCK, HOLDLOCK) WHERE TableName = {0}", tableName)
            .FirstOrDefault();

        int newId;
        if (generator == null)
        {
            generator = new TableGenerator { TableName = tableName, NextId = 2 };
            TableGenerators.Add(generator);
            newId = 1;
        }
        else
        {
            newId = generator.NextId;
            generator.NextId++;
        }

        SaveChanges();
        transaction.Commit();
        return newId;
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

4. 高级优化：批量分配主键（类似allocationSize） #

为减少高并发场景下的数据库请求次数，可以实现类似Hibernate的allocationSize机制，一次批量获取多个主键并缓存：

private readonly Dictionary<string, (int CurrentId, int MaxId)> _idCache = new();
private readonly object _cacheLock = new();

public int GenerateKey(string tableName, int allocationSize = 10)
{
    lock (_cacheLock)
    {
        // 检查缓存是否还有可用主键
        if (_idCache.TryGetValue(tableName, out var range) && range.CurrentId <= range.MaxId)
        {
            return range.CurrentId++;
        }

        // 从数据库批量获取新的主键范围
        var sql = @"
            MERGE TableGenerator AS target
            USING (SELECT @TableName AS TableName, @AllocationSize AS AllocationSize) AS source
            ON (target.TableName = source.TableName)
            WHEN MATCHED THEN
                UPDATE SET target.NextId = target.NextId + source.AllocationSize
            WHEN NOT MATCHED THEN
                INSERT (TableName, NextId)
                VALUES (source.TableName, source.AllocationSize + 1)
            OUTPUT inserted.NextId - source.AllocationSize, inserted.NextId;";

        var param = new[]
        {
            new SqlParameter("@TableName", tableName),
            new SqlParameter("@AllocationSize", allocationSize)
        };

        var result = Database.SqlQuery<(int StartId, int EndId)>(sql, param).First();
        var newRange = (CurrentId: result.StartId, MaxId: result.EndId - 1);
        _idCache[tableName] = newRange;
        return newRange.CurrentId++;
    }
}

5. 使用示例 #

using var dbContext = new YourDbContext();
var newId = dbContext.GenerateKey("Table1");
var entity = new MyEntity { Id = newId, Name = "Entity1" };
dbContext.MyEntities.Add(entity);
dbContext.SaveChanges();

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