Audit.NET Entity Framework Core - Related Entities management

Question

I'm working on an ASP.NET Core 3.1 web app. I'd like to add Audit trails/logs when persisting data on the database.

I took inspiration from this SO answer to start working with Audit.NET in a test project.
Here are my goals (similar to the related SO thread):

1. Store audit records in a different database: Almost done using an extra AppAuditDbContext;
2. Have an audit table per type that matches the audited type (with additional audit fields): Done via reflection on the extra AppAuditDbContext;
3. Require no upkeep of separate audit entities. Changes between operational DB and audit DB should be seamless: Done via reflection on the extra AppAuditDbContext and DataAnnotations on the audited entities;
4. Retrieve audited data for related entities: TO DO

At this point, I can CRUD a standalone audited entity, and retrieve the correct audit on the Audit database.
However, although I can successfully delete a parent entity with its children and get audit data for both parent and children entities, I cannot figure out how to get grouped audit data from the DB for a scenario like this.
I tried to user Audit.NET EntityFramework's EntityFrameworkEvent.TransactionId and EntityFrameworkEvent.AmbientTransactionId, but they're both null on the DB.

Here are my POCOs

public interface IAuditableEntity
{
    [NotMapped]
    string AuditAction { get; set; }

    [NotMapped]
    string AuditTransactionId { get; set; }

    [NotMapped]
    string AuditAmbientTransactionId { get; set; }
}

public class Scope : IAuditableEntity
{
    [Key]
    public int Id {get;set;}

    public string Name { get; set; }

    public virtual ICollection<Job> Jobs { get; set; }

    [NotMapped]
    string AuditAction { get; set; }

    [NotMapped]
    string AuditTransactionId { get; set; }

    [NotMapped]
    string AuditAmbientTransactionId { get; set; }
}

public class Job : IAuditableEntity
{
    [Key]
    public int Id {get;set;}

    public int ScopeId { get; set; }
    public virtual Scope Scope { get; set; }

    [StringLength(128)]
    public string Name { get; set; }

    [NotMapped]
    public string AuditAction { get; set; }

    [NotMapped]
    public string AuditTransactionId { get; set; }

    [NotMapped]
    public string AuditAmbientTransactionId { get; set; }
}

Here is my Audit.NET config (from Startup.cs)

public class Startup
    {            
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

            services.AddDbContext<AppAuditDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("AuditConnection")));

            #region Audit.NET

            var auditDbContextOptions = new DbContextOptionsBuilder<AppAuditDbContext>()
                .UseSqlServer(Configuration.GetConnectionString("AuditConnection"))
                .Options;

            Audit.Core.Configuration.Setup()
                .UseEntityFramework(x => x
                .UseDbContext<AppAuditDbContext>(auditDbContextOptions)
                .AuditTypeNameMapper(typeName =>
                {
                    return typeName;
                }).AuditEntityAction<IAuditableEntity>((ev, ent, auditEntity) =>
                {
                    var entityFrameworkEvent = ev.GetEntityFrameworkEvent();
                    if (entityFrameworkEvent == null) return;

                    auditEntity.AuditTransactionId = entityFrameworkEvent.TransactionId;
                    auditEntity.AuditAmbientTransactionId = entityFrameworkEvent.AmbientTransactionId;
                    auditEntity.AuditAction = ent.Action;
                }));

            #endregion


            services.AddControllersWithViews();
        }

        // other stuff..
    }

Here's the audited context.

[AuditDbContext(IncludeEntityObjects = true)]
    public class ApplicationDbContext : AuditDbContext
    {

        public ApplicationDbContext([NotNullAttribute] DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }

        public DbSet<Scope> Scopes { get; set; }
        public DbSet<Job> Jobs { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Scope>().ToTable("Scope");

            modelBuilder.Entity<Job>().ToTable("Job");
        }

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

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

Here's the Scope's Delete controller action.

public class ScopeController : Controller
    {
        private readonly ApplicationDbContext _context;

        public ScopeController(ApplicationDbContext context)
        {
            _context = context;
        }

        // Other controller actions...

        // POST: Scope/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            var scope = await _context.Scopes.Include(s => s.Jobs).SingleOrDefaultAsync(w => w.Id == id);            
            // using _context.Scopes.FindAsync(id) instead does delete the children Jobs without auditing it
            _context.Scopes.Remove(scope);
            await _context.SaveChangesAsync().ConfigureAwait(false);
            return RedirectToAction(nameof(Index));
        }
    }

This controller action works from the EF viewpoint. It also audits both the parent and children delete actions, but I don't know how to relate the children audit record to the parent audit record Am I supposed to add an AuditScope somewhere in the code? Please, how can I configure Audit.NET to be able to query the Audit database to get grouped auditing data?

Here's the Audit trail for a Scope with Id #5.

Audit_Scope table

Here's the Audit trail for a Job with ScopeId #5.

Audit_Job table

Given the provided data, let's say I want to read the delete audit of the Scope (in this case AuditId #9 from the Audit_Scope table), including the delete audit of its child Jobs (in this case AuditId #10 from the Audit_Job table). How can I achieve this?

Thanks, Matteo

Answer 1

At the moment I've just added a custom field to my entities. I value it within a custom action with a Guid.

// EF AuditEventId per scope
Audit.Core.Configuration.AddCustomAction(ActionType.OnScopeCreated, scope =>
{
    var id = Guid.NewGuid();
    scope.SetCustomField("AuditScopeId", id);
});

In this way I both the Scope and Job table records related to the same audit event will hold the same AuditScopeId value.