Some applications need to track all changes made to a table. You can implement this by adding triggers in the database or by using native RDBMS features such as Temporal tables or Change Data Capture. This post shows how to implement similar functionality in C# with Entity Framework Core in a provider-agnostic way.
The idea is to store a record of every change in a table. For instance, the following code makes several changes to the customer table. After calling SaveChangesAsync, the Audit table is automatically populated with the old and new values of each affected row.
C#
using (var context = new SampleContext())
{
// Insert a row
var customer = new Customer();
customer.FirstName = "John";
customer.LastName = "doe";
context.Customers.Add(customer);
await context.SaveChangesAsync();
// Update the first customer
customer.LastName = "Doe";
await context.SaveChangesAsync();
// Delete the customer
context.Customers.Remove(customer);
await context.SaveChangesAsync();
}
Example of the Audit table content
#How does it work?
The idea is to wrap the SaveChangesAsync method. Before saving rows, you can retrieve the old and new values of the tracked entries by using the ChangeTracker property of the context. If some rows have database-generated values (for instance, an auto-incremented key), you can only obtain those values after the rows are saved. In that case, you must save the corresponding audit rows immediately afterward.
First, create the model:
C#
public class Audit
{
public int Id { get; set; }
public string TableName { get; set; }
public DateTime DateTime { get; set; }
public string KeyValues { get; set; }
public string OldValues { get; set; }
public string NewValues { get; set; }
}
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class SampleContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Audit> Audits { get; set; }
}
In the following code, I only override the SaveChangesAsync method for brevity. In real usage, you should also override the SaveChanges method.
The OnBeforeSaveChanges method creates a list of AuditEntry objects. Each AuditEntry stores the table name, the key values, and the old and new values of an entry. If all values are known before saving the row, you can add the Audit instances to the change tracker so they are saved at the same time as the other entries.
If some entries have temporary values (i.e., values that will be generated by the database), you cannot save the Audit entry immediately. Instead, you must wait until SaveChanges completes. Then, you can retrieve the generated values, finalize the Audit entry, and save the new entries to the database.
C#
public class SampleContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Audit> Audits { get; set; }
public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
var auditEntries = OnBeforeSaveChanges();
var result = await base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
await OnAfterSaveChanges(auditEntries);
return result;
}
private List<AuditEntry> OnBeforeSaveChanges()
{
ChangeTracker.DetectChanges();
var auditEntries = new List<AuditEntry>();
foreach (var entry in ChangeTracker.Entries())
{
if (entry.Entity is Audit || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
continue;
var auditEntry = new AuditEntry(entry);
auditEntry.TableName = entry.Metadata.Relational().TableName; // EF Core 3.1: entry.Metadata.GetTableName();
auditEntries.Add(auditEntry);
foreach (var property in entry.Properties)
{
// The following condition is ok with EF Core 2.2 onwards.
// If you are using EF Core 2.1, you may need to change the following condition to support navigation properties: https://github.com/dotnet/efcore/issues/17700
// if (property.IsTemporary || (entry.State == EntityState.Added && property.Metadata.IsForeignKey()))
if (property.IsTemporary)
{
// value will be generated by the database, get the value after saving
auditEntry.TemporaryProperties.Add(property);
continue;
}
string propertyName = property.Metadata.Name;
if (property.Metadata.IsPrimaryKey())
{
auditEntry.KeyValues[propertyName] = property.CurrentValue;
continue;
}
switch (entry.State)
{
case EntityState.Added:
auditEntry.NewValues[propertyName] = property.CurrentValue;
break;
case EntityState.Deleted:
auditEntry.OldValues[propertyName] = property.OriginalValue;
break;
case EntityState.Modified:
if (property.IsModified)
{
auditEntry.OldValues[propertyName] = property.OriginalValue;
auditEntry.NewValues[propertyName] = property.CurrentValue;
}
break;
}
}
}
// Save audit entities that have all the modifications
foreach (var auditEntry in auditEntries.Where(_ => !_.HasTemporaryProperties))
{
Audits.Add(auditEntry.ToAudit());
}
// keep a list of entries where the value of some properties are unknown at this step
return auditEntries.Where(_ => _.HasTemporaryProperties).ToList();
}
private Task OnAfterSaveChanges(List<AuditEntry> auditEntries)
{
if (auditEntries == null || auditEntries.Count == 0)
return Task.CompletedTask;
foreach (var auditEntry in auditEntries)
{
// Get the final value of the temporary properties
foreach (var prop in auditEntry.TemporaryProperties)
{
if (prop.Metadata.IsPrimaryKey())
{
auditEntry.KeyValues[prop.Metadata.Name] = prop.CurrentValue;
}
else
{
auditEntry.NewValues[prop.Metadata.Name] = prop.CurrentValue;
}
}
// Save the Audit entry
Audits.Add(auditEntry.ToAudit());
}
return SaveChangesAsync();
}
}
public class AuditEntry
{
public AuditEntry(EntityEntry entry)
{
Entry = entry;
}
public EntityEntry Entry { get; }
public string TableName { get; set; }
public Dictionary<string, object> KeyValues { get; } = new Dictionary<string, object>();
public Dictionary<string, object> OldValues { get; } = new Dictionary<string, object>();
public Dictionary<string, object> NewValues { get; } = new Dictionary<string, object>();
public List<PropertyEntry> TemporaryProperties { get; } = new List<PropertyEntry>();
public bool HasTemporaryProperties => TemporaryProperties.Any();
public Audit ToAudit()
{
var audit = new Audit();
audit.TableName = TableName;
audit.DateTime = DateTime.UtcNow;
audit.KeyValues = JsonConvert.SerializeObject(KeyValues);
audit.OldValues = OldValues.Count == 0 ? null : JsonConvert.SerializeObject(OldValues); // In .NET Core 3.1+, you can use System.Text.Json instead of Json.NET
audit.NewValues = NewValues.Count == 0 ? null : JsonConvert.SerializeObject(NewValues);
return audit;
}
}
#Conclusion
This solution is provider-agnostic, so it works with any database. However, it is not perfect. The DateTime column does not reflect the exact time of the change in the database. It also introduces overhead, especially when temporary values are involved, since SaveChanges must be called twice. If you are using SQL Server, consider using its built-in features such as Change Data Capture, Change Tracking, or Temporal Tables. That said, this post demonstrates how you can leverage the ChangeTracker in Entity Framework Core to capture complete change information in a portable way.
#Additional resources
Do you have a question or a suggestion about this post? Contact me!
