3

I'm creating an audit trail using EF Core's change tracker, and for every entity that has the state Added, Modified or Deleted, I create a new audit entity to insert into my Audit table. I want to take a snapshot of what I had before an UPDATE and capture it in JSON and do the same for a snapshot of the entity after the UPDATE.

var entityType = this.Model.FindEntityType(dbEntry.Entity.GetType());
var originalObjectProperties = new Dictionary<string, object>();
var currentObjectProperties = new Dictionary<string, object>();

foreach (var property in entityType.GetProperties())
{
    originalObjectProperties.Add(property.GetColumnName().ToUpper(), dbEntry.OriginalValues[property.Name]);
    currentObjectProperties.Add(property.GetColumnName().ToUpper(), dbEntry.CurrentValues[property.Name]);
}

dbEntry is the entity entry from the change tracker. These give me the same exact JSON though. In my tests I have an INSERT, UPDATE and DELETE, and this is what ends up in my BEFORE_JSON and AFTER_JSON in my database for my UPDATE:

{
    "WORK_REQUEST_KEY": 2,
    "PROJECT_NAME": "This has now been updated",
    "WR_TYPE_KEY": 2
}
{
    "WORK_REQUEST_KEY": 2,
    "PROJECT_NAME": "This has now been updated",
    "WR_TYPE_KEY": 2
}
Wpowell
  • 31
  • 3

1 Answers1

2

I think this should be equivalent, but with slightly less complexity;

var originalObjectProperties = new Dictionary<string, object>();
var currentObjectProperties = new Dictionary<string, object>();

foreach (var property in dbEntry.Properties)
{
    var key = property.Metadata.GetColumnName().ToUpper();
    originalObjectProperties.Add(key, property.OriginalValue);
    currentObjectProperties.Add(key, dbEntry.CurrentValues[property.Metadata]);
}

But what you get out of your change tracker, depends on what you put into it. Were your entities loaded from the database and then changed? Or re-attached as modified?

The most reliable way to audit original values would be in a database trigger, or perhaps using some other built-in database feature.

Jeremy Lakeman
  • 9,515
  • 25
  • 29
  • I'm trying to avoid database triggers, but in my tests I'm creating a new `WORK_REQUEST` entity and inserting it into the database. I then get that entity out of the database using it's key, change a the `PROJECT_NAME` property and update it in the database. Finally I delete the entity from the database. – Wpowell Sep 17 '20 at 13:28
  • You could first obtain the entity from the database and then update it. In that way the changetracker will be aware of the previous values. Anyway the only 100% reliable mechanism is thru triggers on the database – thepirat000 Sep 17 '20 at 22:00