使用Audit.EntityFramework触发SqlException：无法插入标识列值

环境 #

• Entity Framework Core
• .NET 7
• SQL Server

错误信息 #

Cannot insert explicit value for identity column in table 'AuditLogs' when IDENTITY_INSERT is set to OFF.
中文翻译：当IDENTITY_INSERT设置为OFF时，无法为'AuditLogs'表中的标识列插入显式值。

相关信息 #

• 已开启IDENTITY_INSERT
• 列配置为Identity(1,1)主键
• 首次实现该功能，可能存在遗漏点

配置代码 #

Program.cs 配置 #

private static void AuditSetup()
{
    // Set up Audit.NET
    Audit.Core.Configuration.DataProvider = new EntityFrameworkDataProvider()
    {
        DbContextBuilder = ev => new DatabaseContext(AppConfig.ConnectionString),
        AuditTypeMapper = (t, ee) =>
        {
            var typesToAudit = new List<Type>()
            {
                typeof(LiveLoadLocking)
            };

            if (typesToAudit.Contains(t))
            {
                return typeof(AuditLog);
            }
            return null;
        },
        AuditEntityAction = async (evt, entry, auditEntity) =>
        {
            AuditLog a = (AuditLog)auditEntity;

            a.AuditDate = DateTime.UtcNow;
            a.UserName = evt.Environment.UserName;
            a.AuditAction = entry.Action;
            a.EntityType = entry.EntityType.Name;
            a.EntityId = entry.PrimaryKey.FirstOrDefault().Value?.ToString();

            var changes = entry.Changes;

            if (changes != null)
            {
                a.ChangedColumns = string.Join(", ", changes.Select(c => c.ColumnName));
                a.OldValues = JsonConvert.SerializeObject(changes.ToDictionary(c => c.ColumnName, c => c.OriginalValue));
                a.NewValues = JsonConvert.SerializeObject(changes.ToDictionary(c => c.ColumnName, c => c.NewValue));
            }

            return true;
        }
    };
}

AuditLog 实体类 #

public class AuditLog
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public DateTime AuditDate { get; set; }
    public string UserName { get; set; }
    public string AuditAction { get; set; }
    public string EntityType { get; set; }
    public string EntityId { get; set; }
    public string OldValues { get; set; }
    public string NewValues { get; set; }
    public string ChangedColumns { get; set; }
}

modelBuilder 配置 #

modelBuilder.Entity<AuditLog>(entity =>
{
    entity.HasKey(e => e.Id).HasName("PK__AuditLog__somename");
    entity.Property(e => e.Id).ValueGeneratedOnAdd();
    entity.Property(e => e.AuditDate).IsRequired();
    entity.Property(e => e.UserName).HasMaxLength(256);
    entity.Property(e => e.AuditAction).IsRequired().HasMaxLength(50);
    entity.Property(e => e.EntityType).IsRequired().HasMaxLength(128);
    entity.Property(e => e.EntityId).IsRequired().HasMaxLength(128);
    entity.Property(e => e.OldValues).HasColumnType("nvarchar(max)");
    entity.Property(e => e.NewValues).HasColumnType("nvarchar(max)");
    entity.Property(e => e.ChangedColumns).HasColumnType("nvarchar(max)");
});

上下文类结构 #

public partial class DFDBContext : Audit.EntityFramework.AuditDbContext

public partial class DatabaseContext : DFDBContext

保存逻辑示例 #

public static void UnlockLoad(string df)
{
    using (var context = new DatabaseContext(AppConfig.ConnectionString))
    {
        LiveLoadLocking loadLock = context.LiveLoadLockings
            .SingleOrDefault(x => x.somefield == somenumber);

        loadLock.IsLocked = false;
        loadLock.LockedBy = "";
        loadLock.LastUpdated = DateTime.Now;

        if (context.SaveChanges() <= 0) { throw new ContextFailedToSaveException("Items weren't saved or updated to the database.", loadLock); }
    }
}

疑问 #

此前其他表使用相同标识列配置从未出现问题，推测是Audit组件尝试自行插入AuditLog表的标识列值。请问有何解决办法或如何阻止该行为？

1. 强制重置标识列值 #

在AuditEntityAction中，获取AuditLog实例后立即重置Id为0，避免Audit组件自动赋值导致EF Core认为要插入显式值：

AuditLog a = (AuditLog)auditEntity;
a.Id = 0; // 让EF Core自动生成标识列值
// 后续属性赋值代码保持不变

2. 统一标识列配置方式 #

实体类和modelBuilder的标识列配置重复，可能导致EF Core识别冲突，任选一种配置方式即可：

• 移除实体类上的[DatabaseGenerated(DatabaseGeneratedOption.Identity)]，保留modelBuilder中的ValueGeneratedOnAdd()；
• 或者移除modelBuilder中的ValueGeneratedOnAdd()，保留实体类的特性标记。

3. 明确实体状态 #

在AuditEntityAction中手动将AuditLog实体标记为新增状态，确保EF Core正确处理标识列：

AuditEntityAction = async (evt, entry, auditEntity) =>
{
    AuditLog a = (AuditLog)auditEntity;
    var context = entry.Context as DatabaseContext;

    a.Id = 0;
    a.AuditDate = DateTime.UtcNow;
    // 其他属性赋值...

    context.Entry(a).State = EntityState.Added;
    return true;
};

4. 关闭IDENTITY_INSERT #

既然希望数据库自动生成标识列值，无需开启IDENTITY_INSERT，反而可能因为开启它导致EF Core强制要求提供显式Id值。直接关闭该设置，让EF Core按照正常的标识列逻辑处理即可。

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