0

I currently have a system that has an EF 6 tied to Azure SQL back end. Each application entity has a field for createdby, last modified by, deleted by. Each of those user fields is tied to a user in the user entity table. The application was for a small company that was just moving some of their internal processes online. It's worked well up to this point but now the company is growing significantly and they want to add features that are more critical to the business. So I have to update my auditing pattern to be way more indicative of what has happened in the system at any point in time (not just last modified by but every modification and snapshots of data before any changes occur and some other various items).

Is there a best way to handle capturing auditing records (created by, modified by, deleted by, etc)?

Currently, I am thinking about overriding EF6's SaveChanges/Async methods and capturing the records that are changing pre and post save and serializing them and saving them to the documentdb with a timestamp and user id. I know this should work but is there a better way where I'm not doing this in my code and it can be handled somewhere else? I was also thinking about maybe publishing the serialized entity tree to an EventGrid and then having a webjob subscribe to that EventGrid and have it handle the auditing but I'm not sure if that's overkill or not.

I know that I could do ChangeDataCapture if I was using SQL Server but thats not available in AzureSQL.

UPDATE: There was a proposed already answered question that talked about overriding SaveChanges/Async methods to update the particular tracked entity's values as a way of auditing. This would work in a general sense the way I have outlined above. However, I need something like ChangeDataCapture in SQL where every change to a row was saved so a historical tracking of a record could be created to see exactly what happened and who did what. While I believe that my planned way of handling things would have worked, the MUCH better answer is below (the accepted answer). The temporal tables answer would allow me to immediately start recording every row change and from that data, be able to see exactly who did what at what point in time.

JakeHova
  • 1,189
  • 2
  • 15
  • 36
  • Possible duplicate of [Entity Framework 6: audit/track changes](https://stackoverflow.com/questions/26355486/entity-framework-6-audit-track-changes) – Gert Arnold Jul 20 '19 at 19:19
  • @GertArnold , This isn't a duplicate because that solution tracks in place with strictly who did what. I needed who did what for the lifetime of the record while also tracking what changed in the record. The Temporal Tables answer below is a much better solution. – JakeHova Jul 21 '19 at 14:55

1 Answers1

0

To track "what has happened in the system at any point in time" you can use a feature that is built in to Azure SQL, Temporal tables. "Temporal Tables are a new programmability feature of Azure SQL Database that allows you to track and analyze the full history of changes in your data, without the need for custom coding". See:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-temporal-tables

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
  • Holy mackeral! This is awesome!! I had no idea this existed! This actually seems perfect for what I want to do. Thank you! – JakeHova Jul 21 '19 at 14:49