Entity Framework 5.0中基于SiteId实现Recipe计数器自增问题

需求说明 #

需要实现同一SiteId下的Recipe计数器（counter）自动递增，预期效果：

• Recipe 1 : siteId :1 | counter = 1
• Recipe 2 : siteId :1 | counter = 2
• Recipe 3 : siteId :2 | counter = 1
• Recipe 4 : siteId :1 | counter = 3

当前代码 #

ModelBuilder配置 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Recipe>().Property(s => s.Id)
                                 .UseIdentityColumn(seed: 1000, increment: 1).HasColumnName("Id");
   modelBuilder.Entity<Recipe>(s => s.UseTemporalTable());
   modelBuilder.Entity<Recipe>().HasAlternateKey(o => new { o.Order, o.SiteId })
                                .HasName("counter");
   modelBuilder.Entity<Recipe>().Property(o => o.Order)
                                .ValueGeneratedOnAdd().HasColumnName("Order").HasDefaultValueSql("1");
}

实体类 #

public class Recipe : AbstractModel
{
        public int Order { get; set; }
        public int? OrderBySite { get; set; }
        public string Comment { get; set; }
        public string Code { get; set; }
        public DateTime RecipeCreationDateTime { get; set; }
        public Site Site { get; set; }
        [ForeignKey("SiteId")]
        public int SiteId { get; set; }
} 

public class Site : AbstractModel
{
        public string Code { get; set; }
        public string Name { get; set; }
        public int? AddressId { get; set; }
        public Address Address { get; set; }
} 

public abstract class AbstractModel
{
        [Key]
        public int Id { get; set; }
}

报错信息 #

Cannot insert the value NULL into column 'OrderBySite', table 'StockProject2.dbo.Recipes'; column does not allow nulls. INSERT fails. The statement has been terminated.

更新信息 #

更新01：抛出异常的代码 #

添加Recipe的接口方法：

public async override Task<ActionResult<API.Recipe>> AddRecipe([FromBody] API.Recipe body)
{      
    body.RecipeCreationDateTime = DateTime.Now;
    MODEL.Recipe recipe = await _recipeService.AddAsync(_mapper.Map<MODEL.Recipe>(body));
    return Ok(_mapper.Map<API.Recipe>(recipe));
}

插入前的实体值：

new Recipe value : siteId :1000 | order = 0 | counter = null 

原以为ModelBuilder配置会自动填充计数器，但实际未生效。

更新02：抛出异常的时机 #

执行await _dataContext.SaveChangesAsync();时，Recipe实体的OrderBySite为null，Order也为null，疑问是否需要手动处理这两个字段的值。

解决方案 #

第一步：解决OrderBySite的Null错误 #

数据库中OrderBySite字段为非空约束，但实体类中定义为int?（可空），插入时未赋值导致报错。先修正实体类：

public class Recipe : AbstractModel
{
    // 保留原Order字段（如果不需要可删除）
    public int Order { get; set; }
    // 去掉可空标记，匹配数据库非空约束
    public int OrderBySite { get; set; }
    public string Comment { get; set; }
    public string Code { get; set; }
    public DateTime RecipeCreationDateTime { get; set; }
    public Site Site { get; set; }
    [ForeignKey("SiteId")]
    public int SiteId { get; set; }
}

第二步：实现基于SiteId的分组自增 #

EF Core 5.0本身不支持直接配置分组自增列，需要通过数据库触发器或代码层预计算实现，推荐用触发器保证并发安全。

方案1：使用SQL Server触发器（推荐）

在ModelBuilder中添加触发器脚本（或在迁移文件的Up方法中执行）：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Recipe>().Property(s => s.Id)
                                 .UseIdentityColumn(seed: 1000, increment: 1).HasColumnName("Id");
    modelBuilder.Entity<Recipe>(s => s.UseTemporalTable());
    
    // 配置OrderBySite为非空
    modelBuilder.Entity<Recipe>()
        .Property(o => o.OrderBySite)
        .IsRequired();

    // 添加同一SiteId下唯一约束，避免计数器重复
    modelBuilder.Entity<Recipe>()
        .HasAlternateKey(o => new { o.OrderBySite, o.SiteId })
        .HasName("UX_Recipe_OrderBySite_SiteId");

    // 插入触发器：自动计算当前SiteId的最大计数器+1
    modelBuilder.Sql(@"
        CREATE TRIGGER trg_Recipe_GenerateOrderBySite
        ON Recipes
        INSTEAD OF INSERT
        AS
        BEGIN
            SET NOCOUNT ON;
            INSERT INTO Recipes (Id, Order, OrderBySite, Comment, Code, RecipeCreationDateTime, SiteId)
            SELECT 
                i.Id, 
                i.Order, 
                COALESCE((SELECT MAX(OrderBySite) FROM Recipes WITH (UPDLOCK, HOLDLOCK) WHERE SiteId = i.SiteId), 0) + 1,
                i.Comment,
                i.Code,
                i.RecipeCreationDateTime,
                i.SiteId
            FROM inserted i;
        END
    ");
}

触发器中使用WITH (UPDLOCK, HOLDLOCK)锁定查询范围，避免并发插入时出现重复计数器。

方案2：代码层预计算（适合低并发场景）

在AddAsync方法中先查询当前SiteId的最大计数器，再赋值：

public async virtual Task<T> AddAsync(T entity)
{
    try
    {
        if (entity is Recipe recipe)
        {
            // 查询当前SiteId的最大OrderBySite，无数据则返回0
            int maxCounter = await _dbSet.Where(r => r.SiteId == recipe.SiteId)
                                      .MaxAsync(r => (int?)r.OrderBySite) ?? 0;
            recipe.OrderBySite = maxCounter + 1;
        }
        entity = (await _dbSet.AddAsync(entity)).Entity;
        await _dataContext.SaveChangesAsync();
        return entity;
    }
    catch (Exception ex)
    {
        StringBuilder erreurMsg = new StringBuilder();
        erreurMsg.AppendLine($"Function : {nameof(AddAsync)}");
        erreurMsg.AppendLine($"Parameters : {nameof(entity)} = {JsonSerializer.Serialize(entity)}");
        erreurMsg.AppendLine($"Erreur: { ex }");
        _logger.LogError(erreurMsg.ToString());
        return null;
    }
}

注意：高并发场景下可能出现重复计数器，需额外处理冲突。

第三步：修正ModelBuilder的无效配置 #

原代码中配置Order字段的默认值，但实际需要自动递增的是OrderBySite，所以可以删除Order字段的相关配置（如果不需要该字段），或调整为其他用途。

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