Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

32 Comments

  1. I did almost the same two years ago and everything worked fine. But we had an issue that could not be resolved with this approach. Old and new values usually contain ids of other entities and responsible users want to see their representation instead of id.
    Here is an example:
    class Product { string Id; string Name; int Rate; }
    class ProductVariant { string Id; string ProductId; string Color }
    Imagine, you are changing the ProductVariant for product “Audi 100”, setting the color from yellow to red.
    In audit admin will see, that color changed from yellow to red for some ProductId and it won’t tell them a much. Or you could change for red ProductVariant one ProductId to another. What does it mean? Nobody knows
    We thought about dynamic joining one table to another to fetch extra details but. For this case it would be:
    SELECT * from ProductVariant left join Product on ProductVariant.ProductId = Product.Id
    But this implementation is almost impossible:
    1. Writing dynamic queries is a tricky task
    2. Product “Audi 100” could be deleted by that time. Yes, generally you have foreign keys or you might use soft deleting but still it could be the case.
    3. You could rename “Audi 100” to “BMW X5”. Yes, I know, you couldn’t, but users could. And this leads to false knowledge about what really was changed.
    4. Database structure constantly changes. You might rename ProductId field to ParentId for example (this is incorrect, but I beleive you know what I’m talking about). Would you change all audit records, renaming fields in serialized “Old values” and “New values” to be able to join those two tables?
    So, if you really need to allow user to see detailed information about changes, you might need adopt one of the two solutions:
    1. When saving to audtit table you also need to save some extra data. Basically these will be names for every id referencing another table. This can lead for several extra quries to a database to fetch those names and this leads to performance loss if your application writes much data.
    2. You might make a database structure shapshot on every migration and always change names to OldValues/NewValues fields. Then you need to join records only from audit. For example, when admin opens ProductVariant audit record, you do the following on the backend:
    a) deserialize old and new values
    b) get database structure snapshot for that period and find info about ids and referencing tables
    c) execute new query or queries to audit table: select top 1 * from Audit where Id = {ProductVariantId param here} and TableName = ‘Product’ and DateTime <= {ProductVariant audit record date here}

  2. Great tutorial, the audit trail is a very useful idea. Just for the clarity of the tutorial, would help to describe the new context and database update method, (I had to figure it myself, didn’t look into your repo).

  3. So great article.
    When selecting the template in VS 2019 my option are a little different .
    When selecting Individual User Accounts I need values for
    Domain Name
    Application ID
    Sign-up or Sign-in Policy
    Do I need an identity provider to create this demo and which did you use please
    Cheers

    1. Hi, Thanks.

      I believe you are trying to create a WebAPI Project. I created an MVC Core Project. WebAPIs do not have the built-in identity scaffolding like before. If you need to implement this tutorial with API, you can create one without authentication and add authentication later.

  4. I’m trying to implement this in my project, but I don’t understand which modifications to make.
    I’m using Windows authentication.

    I also don’t understand how the Audit table can be created.
    I did a add-migration, but because the Audit DbSet is in the “AuditableIdentityContext” and not in my “ProjectContext”, no table is created in my DB when i do update-database.

  5. Hi Mukesh, i got a question. What happend if i have a process that run into a transaction where i need to call savechanges twice? This gonna save for each saveChanges and duplicate the data?

    Thanks for all your posts, are awesome!!!!
    Keep coding!!

  6. Thanks Sir.
    Please added the step to modify ApplicationDbContext class after adding audit entity
    public class ApplicationDbContext : AuditableIdentityContext

  7. Hi Muckesh,
    Great article!!

    If I use the AspNetCoreHero.EntityFrameworkCore.AuditTrail package what are the changes needed in my mvc appaliction?
    Do you have a demo project?

    Thank you in advance.
    Br,

  8. Hi,
    I have a question:
    In OnAfterSaveChanges you call SaveChangesAsync() which ends up as recursive call.
    Is that intended, or should rather base.SaveChagnesAsync() be called?
    I came across that issue when I wanted to provide other SaveChangesAsync() overloads (and overrides) to prevent client of the code from calling base method (in DbContext) just because using e.g. CancellationToken…

  9. hi mukesh,thanks for the great tutorial , i have one doubt , how can i include identity tables to this (now its not logging AspNetUser,AspNetRoles …)

  10. Thank you for the great article. I have been trying to figure out how to use your technique to audit changes to the Identity AspNetUsers table, but I have been unsuccessful. I read that UserManager ultimately calls DbContext.SaveChangesAsync, but I may be mistaken because SaveChangesAsync in AuditableIdentityContext is not reached when the UpdateAsync of UserManager is called to update a user in the AspNetUsers table. It would be greatly appreciated if you could point me in the right direction.

  11. Thank you very much! This was very helpful for me, and I have fully implemented it.

    However, I did tried to add the “clean code” principles as per Robert C. Martin’s book (I honestly just did it to practice XD). Here is the same code of yours but applying the principles that I learnt from book. Please Let me know what you think Mukesh!

    private int UserId;

    public virtual async Task SaveChangesAsync(int userId)
    {
    UserId= userId;

    SaveCompleteAuditTrace();

    var result = await base.SaveChangesAsync();
    return result;
    }

    private void SaveCompleteAuditTrace()
    {
    using var transaction = Database.BeginTransaction();
    try
    {
    ChangeTracker.DetectChanges();

    var auditEntries = CreateAuditEntries();

    SaveAuditEntries(auditEntries);

    transaction.Commit();
    }
    catch (Exception)
    {
    transaction.Rollback();
    throw;
    }
    }

    private List CreateAuditEntries()
    {
    var auditEntries = new List();

    foreach (var entry in ChangeTracker.Entries())
    {
    if (appliesForAudit(entry) == false)
    continue;

    var auditEntry = CreateAuditEntry(entry);

    auditEntries.Add(auditEntry);
    }

    return auditEntries;
    }

    private bool appliesForAudit(EntityEntry entry)
    {
    bool aplicaAuditoria = true;

    if (entry.Entity is Auditoria || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
    aplicaAuditoria = false;

    return aplicaAuditoria;
    }

    private AuditEntry CreateAuditEntry(EntityEntry entry)
    {
    var auditEntry = new AuditEntry(entry);

    auditEntry.TableName = entry.Entity.GetType().Name;
    auditEntry.UserId = UserId;

    SetAuditEntryType(auditEntry, entry);

    return auditEntry;
    }

    private void SetAuditEntryType(AuditEntry auditEntry, EntityEntry entry)
    {
    foreach (var property in entry.Properties)
    {
    if (property.Metadata.IsPrimaryKey())
    {
    string propertyName = property.Metadata.Name;
    auditEntry.KeyValues[propertyName] = property.CurrentValue;

    continue;
    }
    switch (entry.State)
    {
    case EntityState.Added:
    SetAuditEntryAsAdded(auditEntry, property);
    break;
    case EntityState.Deleted:
    SetAuditEntryAsDeleted(auditEntry, property);
    break;
    case EntityState.Modified:
    SetAuditEntryAsModified(auditEntry, property);
    break;
    }
    }
    }

    private void SetAuditEntryAsAdded(AuditEntry auditEntry, PropertyEntry property)
    {
    string propertyName = property.Metadata.Name;

    auditEntry.AuditType = Enums.TipoAuditoria.Crear;
    auditEntry.NewValues[propertyName] = property.CurrentValue;
    }

    private void SetAuditEntryAsDeleted(AuditEntry auditEntry, PropertyEntry property)
    {
    string propertyName = property.Metadata.Name;

    auditEntry.AuditType = Enums.TipoAuditoria.Eliminar;
    auditEntry.OldValues[propertyName] = property.OriginalValue;
    }

    private void SetAuditEntryAsModified(AuditEntry auditEntry, PropertyEntry property)
    {
    string propertyName = property.Metadata.Name;

    if (property.IsModified)
    {
    auditEntry.ChangedColumns.Add(propertyName);
    auditEntry.AuditType = Enums.TipoAuditoria.Eliminar;
    auditEntry.OldValues[propertyName] = property.OriginalValue;
    auditEntry.NewValues[propertyName] = property.CurrentValue;
    }
    }

    private void SaveAuditEntries(List auditEntries)
    {
    List auditoriasToCreate = new List();

    foreach (var auditEntry in auditEntries)
    {
    auditoriasToCreate.Add(auditEntry.ToAudit());
    }

    Auditoria.AddRange(auditoriasToCreate);
    }

  12. I am having a problem with your implementation.

    In the case of an auditType of “Create”, the ID will always be some random negative number, this is because you are trying to put the primary key…. before the record is it saved to the DB.

    I don’t know why it did not happen to you, but it is happening to me… which makes sense.

  13. Hello Mukesh and thanks for this great approch!

    What can we do to prevent storing of sensitive data or do you see a solution which it makes possible to hide sensitive data?
    At first i think about to encrypt the old and new value JSON strings, but im afraid that the performance will suffer.

    BR Karmat

  14. Thank you very much!

    Just a very cool Edit:

    You can use a UserResolverService to prevent changing every “_context.SaveChangesAsync” to add the userId as Parameter. This Stackoverflow explaning the way:

    https://stackoverflow.com/questions/36401026/how-to-get-user-information-in-dbcontext-using-net-core

    Then you don’t need to add the UserId as param in every Controller because you can get the UserId from the UserResolverService directly. Just some small changed needed.

    In DBContext: string userId = _userResolverService.GetUserId();

    in the Resolver:
    public string GetUserId()
    {
    if (_context.HttpContext != null)
    return _context.HttpContext.User?.FindFirst(ClaimTypes.NameIdentifier).Value;
    else
    return null;
    }

  15. On Create operation, the PrimaryKey column has the following value: {“Id”:-2147482645}
    Do you know why that is? I would rather have it as Null.

  16. Hi Mukesh

    I am using your same code.
    “Create”, the ID will always be some random negative number, Why is Primary Key in negative number for Create ?

    Kind regards,
    Manish

  17. For everyone that is having the primary key issue on Creating, I have come up with a solution. The issue is that all of Mukesh’s logic occurs before SaveChangesAsync is called, so a primary key has not been generated yet. To get around this issue, on creates I pass back the AuditEntry record and then create a new method that runs after SaveChangesAsync, only when this is a create entry. Then, I lookup the created primary key, replace it in the AuditEntry, and then save the AuditEntry then. I will past my code shortly.

  18. Dear Mukesh,
    This is really amazing and I was looking for exactly something similar. I was wondering if we can have one audit table per table that way one table does not become big in size. Can you give any pointers to automatically find the audit table that is under change and the magic.
    e.g.
    Presently, we have one Audit table that saves all tables data in one table
    If I have user table then I would like to have user_audit that saves changes related to user
    If I have department table then I would like to have department_audit that saves changes related to user

    Do you think, this makes sense?
    Kind Regards
    Bharat