How to get Changes for nested collections/entities

[Jimm119]

Hi,

Thank you for your work on this library. It's a great one ! And we look forward to use it in our next application. But we have some requirements that I'm not able to meet at the moment. I'm wondering if it's only my fault or if it's just not possible with the library.

Our backend is in .Net Core 8 and we use EF Core with a SQL Server database.

Our main problem/requirement is that we have some entities with some nested collections we want to audit as one piece. So for exemple users and their roles :

public class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public ICollection<Role> Roles { get; set; }
}

public class Role
{
    public long Id { get; set; }
    public long UserId { get; set; }
    public string Title { get; set; }
    [JsonIgnore]
    public User? User { get; set; }
}

And our audit tables look like :

public class AuditHeader
{
    public virtual long Id { get; set; }
    public virtual string ActionType { get; set; }
    public virtual DateTimeOffset ActionTime { get; set; }
    public virtual string AuthProviderId { get; set; }
    public virtual string ClientIpAddress { get; set; }
    public virtual string ResourceType { get; set; }
    public virtual long ResourceId { get; set; }
    public virtual string ResourceName { get; set; }
    public virtual string Context { get; set; }
    public virtual ICollection<AuditDetail> Details { get; set; }
}

public class AuditDetail
{
    public virtual long Id { get; set; }
    public virtual long AuditId { get; set; }
    public virtual string AttributeType { get; set; }
    public virtual string AttributeName { get; set; }
    public virtual string OldValue { get; set; }
    public virtual string NewValue { get; set; }
    public virtual AuditHeader Audit { get; set; }
}

The AuditHeader indicates the main entity (in this case User) and AuditDetail tracks each field that was changed. For example, if only the email changes, we expect one AuditDetail with the old and new email values — and that part works fine. However, when roles are changed (added/removed/replaced), we want a single AuditDetail showing the old and new roles array. The issue is that the roles array doesn't appear in EventEntry.Changes. Even with IncludeEntityObjects, we can only access the new roles array.

The problem is that the roles array is not included in the EventEntry.Changes. And even by using IncludeEntityObjects, it gives only access to the new roles array.

Is there a way to get both the old and new values of all members of my User, including nested collections/entities like Roles ?

Other questions :

• Is there a built-in option to have only fields that has actually changed in the EventEntry.Changes ? At the moment I have to remove them manually (but not a big deal).
• I would prefer to insert audits at the same time as my entities. But by doing so (with EarlySavingAudit for exemple), I don't get the entity IDs/foreign keys in the audit. On the other hand if I don't use EarlySavingAudit, a failure in saving the audit tables means the entity is saved without its audit. Is there a built-in option to rollback transaction or I have to handle this myself somehow ?

Here is a small repo with my entities/configuration if needed :
AuditTrail.zip

Simply run the following commands in your package manager console to create the local database :

add-migration AuditTrail
update-database

Example of payload :

{
    "Name": "John",
    "Email": "john@domain.com",
    "Roles": [
        { "Title": "Admin" }
    ]
}

Thank you for your time

[thepirat000]

The issue with the New and Old values being identical in the Changes collection is caused by the use of the DbContext.Update method. For more details, refer to issue 53. In your case, you could just remove the line dbContext.Update(userEntity); from the UsersController.

I'll try to respond to the remaining questions shortly.

[thepirat000]

Note that the EventEntry class (the second parameter in AuditEntityAction) represents a single entry from the internal EF change tracker, with each entry corresponding to one and only one entity. As a result, the AuditEntityAction is executed once for each changed mapped entry.

Since you are mapping only the User table using .Map<User, AuditHeader>(), only changes to User entities will be captured.

However, the AuditEvent (the first parameter in AuditEntityAction) contains all the changes from the EF change tracker for a single SaveChanges call. This means it is theoretically possible to detect changes in the Roles collection for a User within your AuditEntityAction.

To ensure that changes to a user's role are captured, you will need to mark the User entity as modified when a role change occurs. While you might have used DbContext.Update(), it is preferable to set the state explicitly using dbContext.Entry(userEntity).State = EntityState.Modified;.

Ultimately, the implementation will depend on the functionality available in your API. If your POST and PUT methods are designed to insert or update a single user, you should be able to accomplish this with some adjustments to your controller logic and the logic within your AuditEntityAction.

Attaching a modified version, note the changes in ApplicationDbContext and UsersController:

AuditTrail_v2.zip

Also, note you were calling Audit.Core.Configuration.Setup() from the DbContext constructor, the Setup should be called only once. I changed it to be on the static constructor of the DbContext.

[thepirat000]

Note that if you're reusing the same DbContext instance to save the audits, you can wrap the SaveChanges() call in a transaction to ensure the operation is rolled back if saving the audit fails:

var tran = dbContext.Database.BeginTransaction();
dbContext.SaveChanges();
tran.Commit();

Another alternative is to use the Generic DbContext Data Provider. However, you'll still need custom logic to create the entities according to your requirements. There is no built-in mechanism that will do this for you.

Audit.EntityFramework.Configuration.Setup()
    .ForAnyContext(cfg => cfg
        .ReloadDatabaseValues()
        .IncludeEntityObjects())
    .UseOptIn()
    .Include<User>()
    .Include<Role>();

Audit.Core.Configuration.Setup()
    .UseDbContext<ApplicationDbContext, AuditHeader>(db => db
        .DbContextBuilder(ev => (ApplicationDbContext)ev.GetEntityFrameworkEvent().GetDbContext()) // <-- Use the audited DbContext
        .Mapper((auditEvent, auditHeader) =>
        {
            var efEvent = auditEvent.GetEntityFrameworkEvent();

            var userEntry = efEvent.Entries.Find(e => e.Entity is User);

            auditHeader.ResourceName = userEntry.Entity.GetType().Name;
            auditHeader.ActionType = userEntry.Action;
            auditHeader.ActionTime = DateTime.UtcNow;
            auditHeader.ResourceId = (long)((dynamic)userEntry.Entity).Id;

            var rolesEntries = efEvent.Entries.FindAll(e => e.Entity is Role);

            var details = new List<AuditDetail>();

            foreach (var roleEntry in rolesEntries)
            {
                if (roleEntry.Action == "Insert")
                {
                    details.Add(new()
                    {
                        AttributeName = "Role.Title",
                        AttributeType = "Role",
                        OldValue = string.Empty,
                        NewValue = roleEntry.ColumnValues["Title"].ToString()
                    });
                }
                else if (roleEntry.Action == "Delete")
                {
                    details.Add(new()
                    {
                        AttributeName = "Role.Title",
                        AttributeType = "Role",
                        OldValue = roleEntry.ColumnValues["Title"]?.ToString() ?? string.Empty,
                        NewValue = string.Empty
                    });
                }
                else if (roleEntry.Action == "Update" && roleEntry.Changes.Exists(ch => ch.ColumnName == "Title" && (string)ch.OriginalValue != (string)ch.NewValue))
                {
                    details.Add(new()
                    {
                        AttributeName = "Role.Title",
                        AttributeType = "Role",
                        OldValue = roleEntry.Changes.Find(ch => ch.ColumnName == "Title")?.OriginalValue.ToString() ?? string.Empty,
                        NewValue = roleEntry.Changes.Find(ch => ch.ColumnName == "Title")?.NewValue.ToString() ?? string.Empty
                    });
                }
            }

            auditHeader.Details = details;
        }));

[Jimm119]

Thank you very much for all those quick and detailed answers. It's very appreciated. Can't have better than that :)

I'll continue to look into our audit implementation in the following days. I'll keep you in touch if I have any other question, but so far so good.