How do I target another database with Audit.Net - Audit.EntityFramework.Core

Question

I'm trying to implement the Audit.EntityFramework.Core package from the Audit.Net repository but am running into some difficulty. I'm unable to save changes or target a different database. I've modified my SaveChanges and SaveChangesAsync function to call the Audit.Net DbContextHelper class's save functions but I'm missing something.

Is there a way to do the following?

1. Target another database for storing audit data using an audit DbContext that inherits from the DbContext I'm trying to audit?

   public class MyDbContext : DbContext {} //Types defined here
   public class AuditDbContext : MyDbContext {} //This context stores audit data into a different DB
   

2. Not require mapping between the type and its audited type when setting up a global connection? (I'm trying to avoid calling AuditTypeMapper explicitly for each type with a model that's currently undergoing a lot of change).

   //MyDbContext has different connection string than AuditDbContext
   Audit.Core.Configuration.Setup()
   .UseEntityFramework(x => x
       .UseDbContext<AuditDbContext>());
   

I've tried code that resembles the following but get runtime errors on SaveChanges that indicate that there is no model set up. Adding a migration for the AuditDbContext didn't help.

Answer 1

I figured out what I was trying to do.

My design goals were:

1. Store audit records in a different database
2. Have an audit table per type that matches the audited type (with additional audit fields)
3. Require no upkeep of separate audit entities. Changes between operational DB and audit DB should be seamless

Things I discovered that did not work were:

1. Creating an audit DbContext that inherited from my operational DbContext doesn't work because the relationships, DBSets, and ID's could not be treated the same way in an audit DB.
2. Dynamically creating types using reflection over operational types with TypeBuilder doesn't work because Audit.Net casts objects between their operational and audit types and casting from a CLR type to a dynamically created type fails.
3. Mixing concrete types and EF Core shadow types doesn't work.

Steps Taken

1. Set up Global Auditing (in main setup code)

   //Global setup of Auditing
   var auditDbCtxOptions = new DbContextOptionsBuilder<MyAuditDbContext>()
       .UseSqlServer(options.AuditDbConnectionString)
       .Options;
   
   Audit.Core.Configuration.Setup()
       .UseEntityFramework(x => x
           .UseDbContext<MyAuditDbContext>(auditDbCtxOptions)
           .AuditTypeNameMapper(typeName => 
           {
               return typeName;
           })
           .AuditEntityAction<AuditInfo>((ev, ent, auditEntity) =>
           {
               auditEntity.DatabaseAction = ent.Action;
           }));
   

2. Had my audited models inherit from a baseclass AuditInfo

   public abstract class AuditInfo
   {
       public DateTime Created { get; set; }
       public DateTime? Updated { get; set; }
       public string CreatedBy { get; set; }
       public string UpdatedBy { get; set; }
   
       [NotMapped] //This is not mapped on the operational DB
       public string DatabaseAction { get; set; }
   }
   

3. Created a reflection-based audit schema using a new DbContext and OnModelCreating

   public class MyAuditContext : DbContext
   {
       public MyAuditContext(DbContextOptions<MyAuditContext> options) : base(options)
       {
   
       }
   
       private readonly Type[] AllowedTypes = new Type[] 
       { 
           typeof(bool),
           typeof(int),
           typeof(decimal),
           typeof(string),
           typeof(DateTime),
       };
   
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           Console.WriteLine($"Generating dynamic audit model");
   
           //Go through each of the types in Hsa.Engine.Data.Models
           var asm = Assembly.GetExecutingAssembly();
           var modelTypes = asm.GetTypes()
               .Where(type => type.Namespace == "My.Data.Models.Namespace");
   
           //Create an entity For each type get all the properties on the model
           foreach(var model in modelTypes.Where(t => t.IsClass && !t.IsAbstract && t.BaseType == typeof(AuditInfo)))
           {
               Console.WriteLine($"Creating entity for {model.Name}");
   
               var table = modelBuilder.Entity(model, entity => 
               {
                   //Remove all types from base model, otherwise we get a bunch of noise about foreign keys, etc.
                   foreach(var prop in model.GetProperties())
                   {
                       entity.Ignore(prop.Name);
                   }
   
                   foreach(var prop in model.GetProperties().Where(p => AllowedTypes.Any(t => p.PropertyType.IsAssignableFrom(t))))
                   {
                       Console.WriteLine($"   Adding field: {prop.Name} - Type: {prop.PropertyType.Name}");
                       //Create a typed field for each property, not including ID or foreign key annotations (do include field lengths)
   
                       var dbField = entity.Property(prop.PropertyType, prop.Name);
   
                       if(prop.PropertyType.IsEnum)
                       {
                           dbField.HasConversion<string>();
                       }
   
                       if(dbField.Metadata.IsPrimaryKey())
                       {
                           dbField.ValueGeneratedNever(); //Removes existing model primary keys for the audit DB
                       }
                   }
   
                   //Add audit properties
                   entity.Property<int>("AuditId").IsRequired().UseSqlServerIdentityColumn();
                   entity.Property<DateTime>("AuditDate").HasDefaultValueSql("getdate()");
                   entity.Property<string>("DatabaseAction"); //included on AuditInfo but NotMapped to avoid putting it on the main DB. Added here to ensure it makes it into the audit DB
   
                   entity.HasKey("AuditId");
                   entity.HasIndex("Id");
                   entity.ToTable("Audit_" + model.Name);
               });
           }
   
           base.OnModelCreating(modelBuilder);
       }
   }
   
   

4. Created a migration for both the primary DB and the Audit DB.

Some people may not need to go to these levels but I wanted to share in case anyone needed something similar when using Audit.Net