CREATE TRIGGER TRG_Products_UPDATE\nON Products AFTER UPDATE\nAS\nBEGIN\n SET NOCOUNT ON;\n\n UPDATE Products\n SET ModifiedBy = SYSTEM_USER, ModifiedOn = GETDATE()\n WHERE Id IN (SELECT Id FROM inserted);\nEND;\n<\/code><\/pre>\nThe CreatedBy\/On columns can be set with a default value for which some EF configuration is needed:<\/p>\n
protected override void OnModelCreating(ModelBuilder modelBuilder)\n{\n \/\/ Required since EF Core 7:\n \/\/ Without this saving changes will fail\n modelBuilder.Entity<Product>()\n .ToTable(tb => tb.UseSqlOutputClause(false));\n\n \/\/ Set default values for CreatedOn\/By\n \/\/ Also ignore property changes made in code:\n modelBuilder.Entity<Product>().Property(e => e.CreatedOn)\n .HasDefaultValueSql(\"GETDATE()\")\n .ValueGeneratedOnAdd()\n .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);\n\n modelBuilder.Entity<Product>().Property(e => e.CreatedBy)\n .HasDefaultValueSql(\"SYSTEM_USER\")\n .ValueGeneratedOnAdd()\n .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);\n\n \/\/ The ModifiedOn\/By are set by the trigger but still\n \/\/ require some EF configuration:\n modelBuilder.Entity<Product>().Property(e => e.ModifiedOn)\n .ValueGeneratedOnAddOrUpdate()\n .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);\n\n \/\/ We're not using ValueGeneratedOnUpdate() because that\n \/\/ would allow a value to be set in code during insertion\n modelBuilder.Entity<Product>().Property(e => e.ModifiedBy)\n .ValueGeneratedOnAddOrUpdate()\n .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);\n}\n<\/code><\/pre>\nUseSqlOutputClause(false)<\/h2>\n
Required for all tables that have triggers since EF Core 7<\/a>:<\/p>\nmodelBuilder.Entity<Product>().ToTable(tb => tb.UseSqlOutputClause(false));\n<\/code><\/pre>\nThis reverts the behavior of EF back to that of older versions of EF, a less performant way of capturing the generated Ids during insertion.<\/p>\n
Pros vs Cons<\/h2>\n
\u2705 All changes to the table are captured, not just those done by the application
\u26a0\ufe0f Changes made by the application are typically all done with the same database user, not the actual user
\u26a0\ufe0f Need to revert to a less performant way of inserting records, which could be a problem for bulk inserts
\u26a0\ufe0f This approach just keeps track of the last modification and doesn\u2019t tell you what<\/strong> has changed<\/p>\nOption 2: Created & Modified By\/On by EF<\/h1>\n
Doing exactly the same thing but this time we let EF do the heavy lifting.<\/p>\n
Abstraction: UserProvider<\/h2>\n
EF somehow needs to know who the current user is, so let\u2019s create a little abstraction and inject the correct implementation depending on the runtime context.<\/p>\n
public interface IUserProvider\n{\n string UserName { get; }\n}\n\n\/\/\/ <summary>A simple ASP.NET implementation<\/summary>\npublic class HttpUserProvider(IHttpContextAccessor accessor) : IUserProvider\n{\n public string UserName => accessor.HttpContext?.User?.Identity?.Name ?? \"???\";\n}\n<\/code><\/pre>\nOur DbContext<\/code> now needs this IUserProvider<\/code>, something the dotnet ef<\/code> CLI doesn\u2019t like.<\/p>\npublic class MyDbContextFactory : IDesignTimeDbContextFactory<MyDbContext>\n{\n public MyDbContext CreateDbContext(string[] args)\n {\n return new MyDbContext(new FakeUserProvider());\n }\n}\n<\/code><\/pre>\nAbstraction: Audit Owned Entity<\/h2>\n
By having all our entities implement IAudit<\/code> we can configure the Audit<\/code>ValueObject<\/a> for all our entities as an Owned entity<\/a>. This way we don\u2019t have 4 properties in all our entities cluttering our intellisense.<\/p>\npublic interface IAudit\n{\n Audit Audit { get; }\n}\n\npublic class Audit\n{\n public DateTime CreatedOn { get; set; }\n [StringLength(50)]\n public string CreatedBy { get; set; } = \"\";\n public DateTime? ModifiedOn { get; set; }\n [StringLength(50)]\n public string? ModifiedBy { get; set; }\n}\n<\/code><\/pre>\nThe DbContext Implementation<\/h2>\npublic class MyDbContext(IUserProvider userProvider) : DbContext\n{\n protected override void OnModelCreating(ModelBuilder modelBuilder)\n {\n var entities = modelBuilder.Model.GetEntityTypes()\n .Where(entityType => typeof(IAudit).IsAssignableFrom(entityType.ClrType))\n .ToArray();\n\n foreach (var entityType in entities)\n {\n modelBuilder.Entity(entityType.ClrType)\n .OwnsOne(typeof(Audit), nameof(IAudit.Audit), audit =>\n {\n audit.Property(nameof(Audit.CreatedOn)).HasColumnName(nameof(Audit.CreatedOn));\n audit.Property(nameof(Audit.CreatedBy)).HasColumnName(nameof(Audit.CreatedBy));\n audit.Property(nameof(Audit.ModifiedOn)).HasColumnName(nameof(Audit.ModifiedOn));\n audit.Property(nameof(Audit.ModifiedBy)).HasColumnName(nameof(Audit.ModifiedBy));\n });\n }\n }\n\n public override int SaveChanges()\n {\n \/\/ You may want to override SaveChangesAsync too!\n SetAuditFields();\n return base.SaveChanges();\n }\n\n private void SetAuditFields()\n {\n var entries = ChangeTracker\n .Entries<IAudit>()\n .Where(e => e.State is EntityState.Added or EntityState.Modified);\n\n foreach (var entityEntry in entries)\n {\n Audit audit = entityEntry.Entity.Audit;\n if (entityEntry.State == EntityState.Added)\n {\n audit.CreatedOn = DateTime.Now;\n audit.CreatedBy = userProvider.UserName;\n \/\/ Make sure a dev hasn't set these\n \/\/ But really... You know, just don't set them...\n audit.ModifiedOn = null;\n audit.ModifiedBy = null;\n }\n else\n {\n audit.ModifiedOn = DateTime.Now;\n audit.ModifiedBy = userProvider.UserName;\n\n \/\/ Here we also revert any changes made in code to the CreatedBy\/On properties\n \/\/ Again, this should go without saying and is probably just overhead...\n var originalAudit = entityEntry.Reference(e => e.Audit).TargetEntry!;\n audit.CreatedBy = originalAudit.OriginalValues.GetValue<string>(nameof(Audit.CreatedBy));\n audit.CreatedOn = originalAudit.OriginalValues.GetValue<DateTime>(nameof(Audit.CreatedOn));\n }\n }\n }\n}\n<\/code><\/pre>\nIf you really don\u2019t trust your team or something, the github repository also contains code where the Audit<\/code> only has getters and everything is set using reflection.<\/p>\nPros vs Cons<\/h2>\n
\u2705 Captures the actual user that made the request
\u26a0\ufe0f Changes done directly in the database are not tracked
\u26a0\ufe0f This approach just keeps track of the last modification and doesn\u2019t tell you what<\/strong> has changed<\/p>\nOption 3: Separate Audit Table with Trigger<\/h1>\n
We create a separate table and track all changes to all our tables with a trigger.<\/p>\n
CREATE TRIGGER [dbo].[TRG_Products_ALL] ON [dbo].[Products]\nAFTER INSERT, UPDATE, DELETE\nAS\nBEGIN\nSET NOCOUNT ON;\n\n-- This trigger also updates the Products table ModifiedOn\/By\n-- But we don't want to create audit records for that!\nIF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.TRG_Products_ALL')) > 1\n RETURN;\n\nIF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted)\n RETURN;\n\nDECLARE @ActionType CHAR(1)\nIF EXISTS (SELECT * FROM inserted)\n IF EXISTS (SELECT * FROM deleted)\n SELECT @ActionType = 'U'\n ELSE\n SELECT @ActionType = 'I'\nELSE\n SELECT @ActionType = 'D'\n\nDECLARE @inserted XML, @deleted XML\nSET @inserted = (SELECT * FROM inserted FOR XML PATH)\nSET @deleted = (SELECT * FROM deleted FOR XML PATH)\n\nDECLARE @tableIds VARCHAR(MAX);\nSET @tableIds = (SELECT STRING_AGG(CAST(Id AS VARCHAR(MAX)), ',') FROM (SELECT DISTINCT Id FROM (SELECT Id FROM inserted UNION SELECT Id from deleted) ids) distinctIds)\n\nINSERT INTO [Audit] (TableName, TableIds, ActionType, OldValues, NewValues)\nSELECT 'Products', @tableIds, @ActionType, @deleted, @inserted;\n\nIF @ActionType = 'U'\n UPDATE dbo.[Products]\n SET ModifiedOn=CURRENT_TIMESTAMP, ModifiedBy=SUSER_SNAME()\n WHERE [Id] IN (SELECT [Id] FROM inserted);\n\nEND;\n<\/code><\/pre>\nThis inserts XML with the changes in Audit.OldValues & NewValues. If you want to use this approach for many tables, you probably want to abstract this in a function or something \ud83d\ude09<\/p>\n
Pros vs Cons<\/h2>\n
\u2705 All changes to the table are captured, not just those done by the application
\u2705 This approach keeps track of all modifications
\u26a0\ufe0f Changes made by the application are typically all done with the same database user, not the actual user
\u26a0\ufe0f Need to revert to a less performant way of inserting records, which could be a problem for bulk inserts
\u26a0\ufe0f Huge update statements could overflow some of the Audit columns. This could be fixed by inserting a record for every single TableId<\/p>\n
Option 4: Separate Audit Table with EF<\/h1>\n
We do exactly the same but with EF doing the heavy lifting. The Old\/NewValues are stored as JSON, not as XML.<\/p>\n
Abstraction: IId<\/h2>\n
We need to get the db ID to insert it into the Audit table!
All entities have to implement this interface.<\/p>\n
public interface IId\n{\n int Id { get; set; }\n}\n<\/code><\/pre>\nThe DbContext Implementation<\/h2>\npublic class MyDbContext(IUserProvider userProvider) : DbContext\n{\n public override int SaveChanges()\n {\n \/\/ and\/or override SaveChangesAsync\n\n \/\/ Keep track of certain things before SaveChanges\n \/\/ As we need to capture the generated IDs to insert it into our Audit table\n var changes = ChangeTracker.Entries<IId>()\n .Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted)\n .Select(e => new ChangedEntity(e))\n .ToArray();\n\n \/\/ Get the inserted DB ids\n int rowsAffected = base.SaveChanges();\n\n \/\/ Generate (& save!) the Audit records\n SaveAuditRecords(changes);\n return rowsAffected;\n }\n\n private class ChangedEntity\n {\n public EntityEntry<IId> EntityEntry { get; }\n public EntityState State { get; }\n public (string oldValues, string newValues) Modifications { get; }\n\n public ChangedEntity(EntityEntry<IId> entityEntry)\n {\n EntityEntry = entityEntry;\n\n \/\/ After the first SaveChanges,\n \/\/ the State is reset to Unchanged!\n State = entityEntry.State;\n\n if (State == EntityState.Modified)\n {\n \/\/ After the first SaveChanges,\n \/\/ nothing is modified anymore!\n string oldValues = GetValues(entityEntry, true);\n string newValues = GetValues(entityEntry, false);\n Modifications = (oldValues, newValues);\n }\n }\n\n private static string GetValues(EntityEntry entry, bool originalValues)\n {\n var properties = entry.Properties\n .Where(p => p.IsModified)\n .ToDictionary(p => p.Metadata.Name, p => originalValues ? p.OriginalValue : p.CurrentValue);\n return JsonSerializer.Serialize(properties);\n }\n }\n\n private void SaveAuditRecords(ChangedEntity[] changes)\n {\n foreach (ChangedEntity change in changes)\n {\n var audit = new EfAudit()\n {\n ModifiedBy = userProvider.UserName,\n ActionType = change.State,\n ModifiedOn = DateTime.Now,\n TableId = change.EntityEntry.Entity.Id,\n TableName = change.EntityEntry.Metadata.GetTableName(),\n };\n\n switch (change.State)\n {\n case EntityState.Added:\n \/\/ Explicit cast to object or it serializes just the IId.Id property\n audit.NewValues = JsonSerializer.Serialize((object)change.EntityEntry.Entity);\n break;\n\n case EntityState.Deleted:\n audit.OldValues = JsonSerializer.Serialize(change.EntityEntry.OriginalValues.ToObject());\n break;\n\n case EntityState.Modified:\n audit.OldValues = change.Modifications.oldValues;\n audit.NewValues = change.Modifications.newValues;\n break;\n }\n\n Audit.Add(audit);\n }\n\n \/\/ Second save to insert the Audit records\n base.SaveChanges();\n }\n}\n<\/code><\/pre>\nIf you are doing EventSourcing, you already have this. It also allows you to implement this in different ways.<\/p>\n
Pros vs Cons<\/h2>\n
\u2705 This approach keeps track of all modifications
\u2705 Captures the actual user that made the request
\u26a0\ufe0f Changes done directly in the database are not tracked<\/p>\n
Conclusion<\/h1>\n
It depends? \ud83e\udd37<\/p>\n
\n- Do you care who has changed what in your tables directly\/manually or using other applications?<\/li>\n
- Do you care what exactly has changed over time and by whom?<\/li>\n<\/ul>\n
Of course, if you trust no one, you could also implement a combination of triggers and EF to really track everything \ud83d\ude00<\/p>\n
Extras<\/h1>\n
Stuff that came into being during the making of this post<\/b><\/p>\n
![](\"https:\/\/img.shields.io\/github\/stars\/dotnet\/efcore.svg?style=social&label=Star\")
<\/a>