I am rewriting an application that was written 12 years ago using ASP.NET v4 with SQL Server 2014, and I'm using ASP.NET Core v6 Razor with SQL Server 2019, and yes I am required to use v6 because of client server limitations.
One of the requirements is that I need to create an audit log of any insert, modification (down to the column level), and deletes. On all entries, I need to know the ID of the user who made the modifications. On modifications, I need to know the table, column, original value and current value of each column that was changed for each entity.
The AuditLog table is part of the existing application, and cannot be changed.
I'm trying to use the ChangeTracker functionality in Entity Framework Core as it seemed to offer exactly what I needed.
What I did was override my SaveChanges() method, and insert logging into it to give me back the values. Once I have that working, I will create the necessary code to insert it into the AuditLog (see code below). I placed the overridden method in my context file so that I don't need to obtain the context to retrieve the data.
I'm gathering the data in the OnGet method, putting it into the model, and showing the UI page for the user. When they click Submit, it returns to my OnPost method. When it reaches the SaveChanges() method call, it goes to my overridden method, and creates the logging. It then returns to the base.SaveChanges() method call, and saves the data successfully.
public override int SaveChanges()
{
foreach (var entityEntry in ChangeTracker.Entries())
{
Debug.WriteLine($"Found {entityEntry.Metadata.Name} entity with a state of {entityEntry.State}");
Debug.WriteLine("=======================================================");
foreach (var property in entityEntry.Properties)
{
Debug.WriteLine($"Found {entityEntry.Metadata.Name} entity with property {property.Metadata.Name} with a original value of {property.OriginalValue} and a new value of {property.CurrentValue} and a modified state of {property.IsModified}");
}
Debug.WriteLine("=======================================================");
}
// Save changes to the database if needed
return base.SaveChanges();
}
What is happening is that the logging is reporting that every column in each entity has been modified, and the original and current values are the same (the current value - see text below). This is the text for a table named Post, and the only thing that was changed was the Active flag was changed from false to true.
Found TSSPortal.Models.Post entity with a state of Modified
=======================================================
Found TSSPortal.Models.Post entity with property PostGuid with a original value of 1ac64171-6eee-4ad6-a6ab-1a1f645dea61 and a new value of 1ac64171-6eee-4ad6-a6ab-1a1f645dea61 and a modified state of False
Found TSSPortal.Models.Post entity with property Active with a original value of True and a new value of True and a modified state of True
Found TSSPortal.Models.Post entity with property Name with a original value of Bob's House and a new value of Bob's House and a modified state of True
Found TSSPortal.Models.Post entity with property RegionGuid with a original value of c32a1393-d814-452c-96c5-c1ee8d753e05 and a new value of c32a1393-d814-452c-96c5-c1ee8d753e05 and a modified state of True
=======================================================
I was planning to iterate over each of the entries, and their corresponding properties, and use the ones that show a modified state of True to create a new AuditLog entry.
Since all the properties are showing a modified state of True, and the original and current values all equal the current value, I have no way of knowing what values were changed.
I have also tried putting the same logging code into a page that does the update in the OnGet method, and it reports exactly what the others do. It is my welcome page, and contains the menus for the application, so it does not need an OnPost method.
I have also looked at the tracking that comes with SQL Server, and it does not seem that it will provide all the information I need (User Id, original value, etc.)
Any suggestions?
public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
ViewData["Regions"] = BuildRegionsList();
return Page();
}
try
{
_context.Posts.Update(PostProductsViewModel.Post!);
var existingProducts = from pp in _context.PostProducts
where pp.PostGuid == PostProductsViewModel.Post!.PostGuid
select pp;
foreach (var existingProduct in existingProducts)
{
var postProducts = _context.PostProducts.Find(existingProduct.PostProductsGuid);
_context.PostProducts.Remove(postProducts!);
}
foreach (var postProductSelect in PostProductsViewModel.PostProducts!)
{
if (postProductSelect.IsSelected)
{
PostProduct pp = new PostProduct();
pp.PostProductsGuid = Guid.NewGuid();
pp.PostGuid = PostProductsViewModel.Post!.PostGuid;
pp.ProductGuid = postProductSelect.Id;
pp.CreateDt = DateTime.Now;
_context.PostProducts.Add(pp);
}
}
_context.SaveChanges();
}
catch (DbUpdateException dbUpdateException)
{
string[] errorMessage = dbUpdateException.InnerException!.Message.Split('.');
return RedirectToPage("/Error", new { ErrorMessage = dbUpdateException.Message + ". The statement has been terminated.", ReturnPage = "\\PORT\\Posts\\Edit", ID = PostProductsViewModel.Post!.PostGuid });
}
catch (Exception ex)
{
return RedirectToPage("/Error", new { ErrorMessage = ex.Message, ReturnPage = "\\PORT\\Posts\\Edit", ID = PostProductsViewModel.Post!.PostGuid });
}
return RedirectToPage("/PORT/Posts/Index");
}
This will happen when the code is using Update() with detached entities. A common example in a web application is when passing entities filled by a Form POST into a controller method. As a very simplistic example:
public async Task<ActionResult> UpdateOrder(Order order)
{
_context.Update(order);
_context.SaveChanges();
}
Change Tracker has nothing to do here. The DbContext has no tracked "original" instance, it just gets told "here is an entity, attach it, set everything (except the Key) to Modified and save it".
The same goes for code like:
_context.Attach(order);
_context.Entry(order).State = EntityState.Modified;
_context.SaveChanges();
To get information from the charge tracker you need to actually modify a tracked entity:
So as a very simple example:
public async Task<ActionResult> UpdateOrder(Order order)
{
var existingOrder = _context.Orders.Single(x => x.Id == order.Id);
_context.Entry(existingOrder).CurrentValues.SetValues(order);
_context.SaveChanges();
}
Here existingOrder is a tracked entity which we then copy the values across from the passed in detached quasi-entity. When we save it, the change tracker will have references to original and current values to work with.
The above is an example but not what I recommend using in production code since it is overly trusting of the source data coming in disguised as an entity. I would recommend either using a Mapper configured with what values are legally allowed to be changed, or copying the values manually from the updated data to the existing tracked entity to ensure that only what is expected to be changed is changed. Note that if the entity in question has any associated entities to be updated, those will need to be gone through and updated in a similar manner.