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

Question

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!

score 1 · Answer 1 · answered May 12 '23 at 23:20

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.

score 0 · Answer 2 · answered May 10 '23 at 23:21

0

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!

answered May 10 '23 at 23:21

Whaaa

73
4

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

2 Answers2