Some applications need to keep a list of all the changes in a table. You can implement this functionality by adding triggers in the database or by using native features of the RDBMS such as Temporal tables or Change Data Capture. Let's see how you can implement similar functionality in C# with Entity Framework Core in a provider agnostic way!
The short idea is to store a list of all changes in a table. For instance, the following code does some changes in the customer table. After calling the SaveChangeAsync method, the Audit table is automatically filled with the old and new values of each 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 get the old and new values of the entries by using the ChangeTracker property of the context. If some of the rows have database generated values (for instance, an auto-incremented value), you can get the value of the concerned properties after rows are saved. In this case, you must save the new audit rows just after.
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 OnBeforeChanges method creates a list of AuditEntry. An AuditEntry store the table name, the ids and the old and new values of an entry. If all the values are known before actually saving the row, you can the Audit instances in the change tracker, so there are saved at the same time as other entries.
If some entries have temporary values, e.g. values that will be set by the database, you cannot save the Audit entry immediately. Instead, you must wait until the SaveChanges method does its job. Then, you can get the generated value and finish the Audit entry. Finally, you must save the new entries in 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 should work with any database. However, it is not perfect. The DateTime column does not correspond to the date of the actual change in the database. It introduces an overhead, mainly when there are temporary values as you need to call the SaveChanges method twice. If you are using SQL Server, you should consider using existing functionalities such as Change Data Capture, Change Tracking, or Temporal Tables. Yes, you have the choice with SQL Server 😃 However, this post demonstrates how you can use the ChangeTracker of Entity Framework Core to get all the information about changes.
#Additional resources
Do you have a question or a suggestion about this post? Contact me!
