Audit.EntityFramework: SqlException: Cannot insert explicit value for identity column

• Entity Framework Core
• .NET 7
• SQL Server

Error:
Cannot insert explicit value for identity column in table 'AuditLogs' when IDENTITY_INSERT is set to OFF.

Info:

• Identity Insert is set to ON
• My Column is Identity(1,1) PRIMARY KEY
• This is my first time implementing this, so I could be missing something large that I'm not seeing.

Program.cs setup:

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)");
        });

I have the following classes for my contexts:

public partial class DFDBContext : Audit.EntityFramework.AuditDbContext

public partial class DatabaseContext : DFDBContext

Example of my saving logic (not perfect structure, I know):

        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); }
            }
        }

I can't seem to get around this identity error. Anyone have any tips? I have the same identity set up on many tables and have never hit this error before, so I assume that Audit is trying to insert the identity itself into the AuditLog table? Is there any workaround or way to stop it from trying to do that?

Thanks!

Take into consideration that, by default, the provider will map the properties with the same name from your entity (LiveLoadLocking) to your audit entity (AuditLog).

You can disable this mechanism, for example calling IgnoreMatchedProperties() in the fluent configuration API:

Audit.Core.Configuration.Setup()
    .UseEntityFramework(cfg => cfg
        .AuditTypeMapper(t =>
        {
            if (typesToAudit.Contains(t))
            {
                return typeof(AuditLog);
            }

            return null;
        })
        .AuditEntityAction((evt, entry, auditEntity) =>
        {
            AuditLog a = (AuditLog)auditEntity;

            // ...

            return true;
        })
        .IgnoreMatchedProperties());   // <-- Add this

In this way, the AuditLog properties will not be auto-populated.

Data Type of Int was defaulting to 0 when being handled by Audit.Net, hence it seeming like it's trying to fill in the identity!

Solution:

public int? Id

on AuditLog!