Round #501

Add auditing via SaveChanges override that captures before/after state

To add auditing functionality to your Entity Framework (EF) context, you can override the `SaveChanges` method. This method will allow you to capture the state of entities before and after they are saved to the database. Here's a step-by-step guide on how to implement this: 1. **Create an Audit Log Table**: First, create a table in your database to store the audit logs. For example: ```sql CREATE TABLE AuditLog ( AuditLogId INT IDENTITY(1,1) PRIMARY KEY, TableName NVARCHAR(255), Action NVARCHAR(10), -- 'Insert', 'Update', 'Delete' OldValues NVARCHAR(MAX), NewValues NVARCHAR(MAX), ChangedOn DATETIME DEFAULT GETDATE(), UserId NVARCHAR(255) ); ``` 2. **Create an Audit Log Entity**: Create a corresponding entity for the audit log table. ```csharp public class AuditLog { public int AuditLogId { get; set; } public string TableName { get; set; } public string Action { get; set; } public string OldValues { get; set; } public string NewValues { get; set; } public DateTime ChangedOn { get; set; } public string UserId { get; set; } } ``` 3. **Override SaveChanges Method**: Override the `SaveChanges` method in your DbContext to capture and log changes. ```csharp using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; public class MyDbContext : DbContext { public DbSet<AuditLog> AuditLogs { get; set; } protected override int SaveChanges() { var entries = ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified || e.State == EntityState.Deleted).ToList(); foreach (var entry in entries) { if (entry.Entity is AuditLog) continue; // Skip audit log entries string tableName = entry.Entity.GetType().Name; string action = entry.State.ToString(); string oldValues = null; string newValues = null; switch (entry.State) { case EntityState.Added: newValues = GetEntityJson(entry.CurrentValues); break; case EntityState.Modified: oldValues = GetEntityJson(entry.OriginalValues); newValues = GetEntityJson(entry.CurrentValues); break; case EntityState.Deleted: oldValues = GetEntityJson(entry.OriginalValues); break; } AuditLogs.Add(new AuditLog { TableName = tableName, Action = action, OldValues = oldValues, NewValues = newValues, UserId = "CurrentUser" // Replace with actual user ID retrieval logic }); } return base.SaveChanges(); } private string GetEntityJson(DbDataRecord record) { var properties = record.FieldCount > 0 ? record.GetValues().Select((value, index) => $"{record.GetName(index)}: {value}").ToArray() : new string[] { }; return $"{{ {string.Join(", ", properties)} }}"; } } ``` 4. **Configure DbContext**: Ensure your `DbContext` is configured correctly in your application. ```csharp public class MyDbContextConfiguration : DbConfiguration { public MyDbContextConfiguration() { SetProviderServices("System.Data.SqlClient", SqlClientServices.Instance); } } ``` 5. **Use the DbContext**: Use your `MyDbContext` as usual in your application. ```csharp using (var context = new MyDbContext()) { // Perform database operations var user = new User { Name = "John Doe" }; context.Users.Add(user); context.SaveChanges(); } ``` This implementation captures the state of entities before and after they are saved to the database, logs them in an audit log table, and includes basic JSON serialization for simplicity. You can extend this by adding more sophisticated serialization logic or handling different types of data changes.

To implement auditing via a `SaveChanges` override that captures the before and after state of entities, we can create an extension method for `DbContext`. This approach allows us to intercept changes made during a save operation and log the necessary information. Here's how you can achieve this: 1. **Create an Audit Log Entity**: Define a model to store audit logs. 2. **Extend DbContext**: Override the `SaveChanges` method to capture and log changes. 3. **Register Services**: Ensure that the `DbContext` is registered in dependency injection (DI). ### Step 1: Create an Audit Log Entity ```csharp public class AuditLog { public int Id { get; set; } public string TableName { get; set; } public string Action { get; set; } // e.g., "Insert", "Update", "Delete" public DateTime ChangeDate { get; set; } public string OldValues { get; set; } public string NewValues { get; set; } } ``` ### Step 2: Extend DbContext ```csharp using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Reflection; public class AuditableDbContext : DbContext { private readonly List<AuditLog> _auditLogs = new List<AuditLog>(); public DbSet<AuditLog> AuditLogs { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); // Apply entity configurations if needed } public override int SaveChanges() { var entries = ChangeTracker.Entries().Where(e => e.Entity is not AuditLog).ToList(); foreach (var entry in entries) { switch (entry.State) { case EntityState.Added: _auditLogs.Add(CreateAuditLog(entry, "Insert")); break; case EntityState.Modified: _auditLogs.Add(CreateAuditLog(entry, "Update")); break; case EntityState.Deleted: _auditLogs.Add(CreateAuditLog(entry, "Delete")); break; } } var result = base.SaveChanges(); if (_auditLogs.Any()) { AuditLogs.AddRange(_auditLogs); _auditLogs.Clear(); SaveChanges(); } return result; } private AuditLog CreateAuditLog(EntityEntry entry, string action) { var auditLog = new AuditLog { TableName = entry.Entity.GetType().Name, Action = action, ChangeDate = DateTime.Now }; if (action == "Update" || action == "Delete") { var oldValues = GetPropertyValues(entry.OriginalValues); auditLog.OldValues = oldValues; } if (action == "Insert" || action == "Update") { var newValues = GetPropertyValues(entry.CurrentValues); auditLog.NewValues = newValues; } return auditLog; } private string GetPropertyValues(PropertyValues propertyValues) { var properties = propertyValues.Properties .Where(p => p.ClrType != typeof(byte[])) // Exclude binary data for simplicity .Select(p => $"{p.Name}: {propertyValues[p]}") .ToArray(); return string.Join(", ", properties); } } ``` ### Step 3: Register Services In your `Startup.cs` or wherever you configure services, register the `AuditableDbContext`. ```csharp public void ConfigureServices(IServiceCollection services) { services.AddDbContext<AuditableDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); // Other service registrations... } ``` ### Explanation 1. **AuditLog Entity**: This entity represents a single audit log entry, capturing the table name, action type, change date, and old/new values. 2. **AuditableDbContext**: This class extends `DbContext` and overrides the `SaveChanges` method to capture changes made during a save operation. It logs these changes into an in-memory list of `AuditLog` objects. 3. **CreateAuditLog Method**: This helper method creates an `AuditLog` object based on the entity entry's state (Added, Modified, Deleted). 4. **GetPropertyValues Method**: This method retrieves and formats the property values of an entity for logging. This approach ensures that all changes made through this `DbContext` are audited, capturing both the before and after states of entities.