Comprehensive Guide to Implementing Audit Logging in .NET with EF Core Interceptors

Audit logging is a critical component of modern applications, providing transparency, security, and compliance. This guide explores a sophisticated audit logging solution using Entity Framework Core (EF Core) Interceptors, explaining each component in detail, providing real-world examples, and discussing best practices for implementation.

---

Table of Contents

1. Introduction to Audit Logging
2. Core Components Overview
3. Deep Dive into the Audit Interceptor
4. Real-World Example: E-Commerce Product Updates
5. Advanced Configuration & Best Practices
6. Performance Considerations
7. Security Implications
8. Future Enhancements

---

1. Introduction to Audit Logging

Audit logging captures who changed what data and when, serving three primary purposes:

1. Regulatory Compliance

   • GDPR, HIPAA, PCI-DSS requirements
   • Legal evidence in disputes

2. Operational Integrity

   • Debugging data anomalies
   • Recovery from accidental changes

3. Business Intelligence

   • User behavior analysis
   • Change pattern recognition

Traditional approaches often require manual logging in every service method. Our EF Core Interceptor solution automates this process through database-level observation.

---

2. Core Components Overview

2.1 Audit Log Entity

public class AuditLog
{
    public long Id { get; set; }
    public string TableName { get; set; }        // Modified entity type
    public long RecordId { get; set; }           // Modified record ID
    public string Operation { get; set; }        // CREATE/UPDATE/DELETE
    public string OldValues { get; set; }        // JSON snapshot before changes
    public string NewValues { get; set; }        // JSON snapshot after changes
    public long ModifiedBy { get; set; }         // User ID from context
    public DateTimeOffset ModifiedAt { get; set; } // UTC timestamp
}

2.2 User Context Service

public interface IUserContext
{
    long? CurrentUserId { get; }
}

// Implementation fetching user from JWT
public class JwtUserContext : IUserContext
{
    private readonly IHttpContextAccessor _contextAccessor;

    public JwtUserContext(IHttpContextAccessor contextAccessor) 
        => _contextAccessor = contextAccessor;

    public long? CurrentUserId => 
        long.TryParse(
            _contextAccessor.HttpContext?.User.FindFirstValue(ClaimTypes.NameIdentifier), 
            out var userId
        ) ? userId : null;
}

2.3 Audit Interceptor Architecture

sequenceDiagram
    participant Client
    participant DbContext
    participant AuditInterceptor
    participant Database

    Client->>DbContext: SaveChangesAsync()
    DbContext->>AuditInterceptor: SavingChangesAsync()
    AuditInterceptor->>AuditInterceptor: Analyze ChangeTracker
    AuditInterceptor->>Database: Save AuditLogs
    AuditInterceptor->>DbContext: Continue original save
    DbContext->>Database: Save business entities
    Database-->>DbContext: Success
    DbContext-->>Client: Result

---

3. Deep Dive into the Audit Interceptor

3.1 Change Detection Mechanism

The interceptor hooks into EF Core's SaveChangesAsync pipeline:

public override async ValueTask<InterceptionResult<int>> SavingChangesAsync(
    DbContextEventData eventData, 
    InterceptionResult<int> result,
    CancellationToken cancellationToken = default)
{
    // Prevent recursive saving
    if (_isSaving) return await base.SavingChangesAsync(...);

    try
    {
        _isSaving = true;
        var audits = new List<AuditLog>();

        foreach (var entry in eventData.Context.ChangeTracker.Entries())
        {
            if (ShouldAudit(entry))
                audits.Add(CreateAuditEntry(entry));
        }

        await SaveAudits(eventData.Context, audits);
        return await base.SavingChangesAsync(...);
    }
    finally 
    {
        _isSaving = false;
    }
}

private bool ShouldAudit(EntityEntry entry) =>
    entry.Entity is not AuditLog && 
    entry.State is EntityState.Added or EntityState.Modified or EntityState.Deleted;

3.2 Change Processing Logic

Handles different entity states with precision:

Added Entities

if (entry.State == EntityState.Added)
{
    audit.NewValues = JsonSerializer.Serialize(entry.CurrentValues.ToObject());
}

Deleted Entities

if (entry.State == EntityState.Deleted)
{
    audit.OldValues = JsonSerializer.Serialize(entry.OriginalValues.ToObject());
}

Modified Entities

var changes = entry.Properties
    .Where(p => p.IsModified && !Equals(p.OriginalValue, p.CurrentValue))
    .ToDictionary(
        p => p.Metadata.Name,
        p => new { Old = p.OriginalValue, New = p.CurrentValue }
    );

audit.OldValues = changes.Any() 
    ? JsonSerializer.Serialize(changes.ToDictionary(k => k.Key, k => k.Value.Old)) 
    : null;

audit.NewValues = changes.Any()
    ? JsonSerializer.Serialize(changes.ToDictionary(k => k.Key, k => k.Value.New))
    : null;

---

4. Real-World Example: E-Commerce Product Updates

4.1 Scenario: Price & Stock Adjustment

// Original product state
var product = new Product 
{
    Id = 1,
    Name = "Wireless Headphones",
    Price = 199.99m,
    Stock = 50
};

// User updates
product.Price = 179.99m;
product.Stock = 45;

// Save changes
await _context.SaveChangesAsync();

4.2 Generated Audit Log

{
  "Id": 315,
  "TableName": "Product",
  "RecordId": 1,
  "Operation": "Modified",
  "OldValues": {
    "Price": 199.99,
    "Stock": 50
  },
  "NewValues": {
    "Price": 179.99,
    "Stock": 45
  },
  "ModifiedBy": 2345,
  "ModifiedAt": "2024-02-21T09:30:45Z"
}

4.3 Querying Audit History

Find all price changes for a product:

var priceHistory = await _context.AuditLogs
    .Where(a => 
        a.TableName == "Product" &&
        a.RecordId == productId &&
        a.Operation == "Modified" &&
        a.OldValues.Contains("Price"))
    .OrderByDescending(a => a.ModifiedAt)
    .Select(a => new 
    {
        OldPrice = JsonDocument.Parse(a.OldValues).RootElement.GetProperty("Price").GetDecimal(),
        NewPrice = JsonDocument.Parse(a.NewValues).RootElement.GetProperty("Price").GetDecimal(),
        ChangedBy = a.ModifiedBy,
        ChangedAt = a.ModifiedAt
    })
    .ToListAsync();

---

5. Advanced Configuration & Best Practices

5.1 Configuration Options

services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(configuration.GetConnectionString("Default"))
        .AddInterceptors(new AuditInterceptor(
            userContext: new JwtUserContext(),
            options: new AuditOptions
            {
                IgnoreUnchanged = true,
                MaxValueLength = 2000,
                SensitiveFields = { "PasswordHash", "CreditCardNumber" }
            }));
});

5.2 Best Practices

1. Data Retention Policy

   // Auto-delete logs older than 2 years
   services.AddHostedService<AuditLogCleanupService>();

   public class AuditLogCleanupService : BackgroundService
   {
       protected override async Task ExecuteAsync(CancellationToken stoppingToken)
       {
           while (!stoppingToken.IsCancellationRequested)
           {
               await _context.AuditLogs
                   .Where(a => a.ModifiedAt < DateTimeOffset.UtcNow.AddYears(-2))
                   .ExecuteDeleteAsync();

               await Task.Delay(TimeSpan.FromDays(1), stoppingToken);
           }
       }
   }

1. Performance Optimization
   • Use database indexing:

   CREATE NONCLUSTERED INDEX IX_AuditLogs_Search 
   ON AuditLogs (TableName, RecordId, ModifiedAt DESC)

1. Custom Serialization

   var options = new JsonSerializerOptions
   {
       PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
       Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping
   };

   JsonSerializer.Serialize(data, options);

---

6. Performance Considerations

6.1 Impact Analysis

Operation	Without Audit	With Audit	Overhead
Insert 1000	120ms	450ms	275%
Update 1000	150ms	520ms	247%
Delete 1000	110ms	430ms	291%

6.2 Mitigation Strategies

1. Batching
   
   Process audits in batches of 100 records

2. Asynchronous Logging
   
   Use background queues for non-critical audits

3. Selective Auditing
   
   Attribute-based opt-out:
   

   [Audit(Ignore = true)]
   public class TemporaryData
   {
       // ...
   }

---

7. Security Implications

7.1 Sensitive Data Handling

public class AuditInterceptor : SaveChangesInterceptor
{
    private readonly List<string> _sensitiveFields;

    public AuditInterceptor(/* ... */, List<string> sensitiveFields)
        => _sensitiveFields = sensitiveFields;

    private string SanitizeValues(IDictionary<string, object> values)
        => values.ToDictionary(
            kvp => kvp.Key,
            kvp => _sensitiveFields.Contains(kvp.Key) ? "**REDACTED**" : kvp.Value
        );
}

7.2 Access Control

Implement row-level security:

CREATE SECURITY POLICY AuditLogAccessPolicy
ADD FILTER PREDICATE dbo.fn_UserCanAccessAuditLog(@UserId, TableName, RecordId)
ON dbo.AuditLogs
WITH (STATE = ON);

---

8. Future Enhancements

8.1 Planned Features

1. Change Visualization

   public class AuditDiff
   {
       public static string GetHtmlDiff(string oldJson, string newJson)
       {
           // Generates side-by-side HTML comparison
       }
   }

1. Real-Time Notifications

   services.AddSignalR();

   public class AuditHub : Hub
   {
       public async Task SubscribeToAudits(string entityType, long entityId)
           => await Groups.AddToGroupAsync(Context.ConnectionId, $"{entityType}-{entityId}");
   }

1. Machine Learning Anomaly Detection

   public class AuditAnalyzer
   {
       public bool IsSuspiciousChange(AuditLog audit)
           => _model.Predict(new AuditFeatures(audit)) > 0.95;
   }

---

Conclusion

This EF Core audit logging solution provides:

Comprehensive Change Tracking

Minimal Code Impact

Enterprise-Grade Security

Scalable Architecture

By implementing this pattern, you establish a robust foundation for data governance while maintaining development agility. The system adapts to various use cases through:

• Customizable serialization
• Flexible user context integration
• Performance-optimized logging

Comments

[Mo]

This guide is incredibly detailed and well-structured!

Thanks for sharing ⭐