EF Core 7中如何约束同一ItemId仅一条IsCurrent=true记录？

针对你的医疗记录版本管理场景，以下是几种可行的实现方案，兼顾数据库无关性和SQL Server特性：

一、应用层约束（数据库无关） #

通过业务逻辑+事务控制，确保更新/创建时的一致性，适合跨数据库场景，但需注意并发处理。

1. 事务内批量更新+新增/修改 #

在创建新版本或标记某版本为当前时，先将同ItemId下的所有当前记录设为非当前，再保存目标记录，全程在事务中执行：

using var transaction = await _dbContext.Database.BeginTransactionAsync();
try
{
    // 将同ItemId的现有当前版本置为非当前
    await _dbContext.Treatments
        .Where(t => t.ItemId == targetTreatment.ItemId && t.IsCurrent)
        .ExecuteUpdateAsync(t => t.SetProperty(x => x.IsCurrent, false));
    
    // 标记目标记录为当前版本并保存
    targetTreatment.IsCurrent = true;
    _dbContext.Treatments.Update(targetTreatment); // 或 Add 新增版本
    await _dbContext.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

2. SaveChanges前验证 #

重写DbContext的SaveChanges方法，提前检查待提交的记录是否违反约束，避免无效数据库操作：

public override int SaveChanges()
{
    ValidateCurrentTreatmentConstraints();
    return base.SaveChanges();
}

public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    ValidateCurrentTreatmentConstraints();
    return await base.SaveChangesAsync(cancellationToken);
}

private void ValidateCurrentTreatmentConstraints()
{
    // 检查待新增/修改的记录中，是否有同一ItemId多条IsCurrent=true的情况
    var conflictingItems = ChangeTracker.Entries<Treatment>()
        .Where(e => (e.State == EntityState.Added || e.Property(x => x.IsCurrent).IsModified) && e.Entity.IsCurrent)
        .GroupBy(e => e.Entity.ItemId)
        .Where(g => g.Count() > 1);

    if (conflictingItems.Any())
    {
        throw new InvalidOperationException("同一医疗记录(ItemId)只能存在一条当前版本记录");
    }

    // 检查数据库中是否已存在同ItemId的当前版本
    var itemIdsToCheck = conflictingItems.Select(g => g.Key).ToList();
    if (itemIdsToCheck.Any())
    {
        var existingCount = _dbContext.Treatments
            .Count(t => itemIdsToCheck.Contains(t.ItemId) && t.IsCurrent);
        
        if (existingCount > 0)
        {
            throw new InvalidOperationException("该医疗记录(ItemId)已存在当前版本");
        }
    }
}

二、数据库层面约束（更可靠，支持SQL Server及多数据库适配） #

数据库约束是最后一道防线，能从底层避免数据不一致，以下是两种实现方式：

1. 过滤唯一索引（支持多数据库适配） #

利用EF Core的HasFilter配置过滤唯一索引，仅对IsCurrent=true的记录强制ItemId唯一，可适配不同数据库：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var treatmentIndex = modelBuilder.Entity<Treatment>()
        .HasIndex(x => x.ItemId) // 仅需ItemId作为索引键，结合过滤条件
        .IsUnique();

    // 根据数据库类型配置过滤条件
    if (_dbContext.Database.IsSqlServer())
    {
        treatmentIndex.HasFilter("IsCurrent = 1");
    }
    else if (_dbContext.Database.IsNpgsql())
    {
        treatmentIndex.HasFilter("\"IsCurrent\" = true");
    }
    else if (_dbContext.Database.IsMySql())
    {
        treatmentIndex.HasFilter("IsCurrent = TRUE");
    }
}

注：原复合索引可替换为上述配置，因为过滤条件已限定IsCurrent=true，仅需ItemId唯一即可满足需求。

2. SQL Server特有的触发器方案（备选） #

若需更复杂的逻辑控制，可创建SQL Server触发器，在插入/更新时检查约束：

CREATE TRIGGER TR_Treatment_EnforceSingleCurrent
ON Treatment
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM Treatment t
        JOIN inserted i ON t.ItemId = i.ItemId
        WHERE t.IsCurrent = 1
        GROUP BY t.ItemId
        HAVING COUNT(*) > 1
    )
    BEGIN
        RAISERROR('同一ItemId只能有一条当前版本记录', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

可通过EF Core的迁移脚本添加该触发器，或手动在数据库中创建。

总结 #

• 优先选择应用层事务+数据库过滤唯一索引的组合方案：应用层逻辑提升用户体验，数据库约束确保数据一致性。
• 若需严格跨数据库兼容，可通过EF Core的数据库提供者判断配置不同的过滤索引语法；若仅针对SQL Server，直接使用HasFilter("IsCurrent = 1")即可。

内容的提问来源于stack exchange，提问作者Bob.at.Indigo.Health