EF Core value updates are not audited

[shubhamCedargate]

EntityFramework changes are not getting audited or detected.

I am using a dotnet project as a package to make any DB changes. ie the package houses the databaseContext (Persistence package) I use another .net project to trigger these database changes through repository methods.

The Persistence package is used by multiple projects. The database changes through this package are properly audited when running from other .net projects, but completely ignored when using from one .net project. The config I set up for both these projects is the same. Yet Audits are not triggered from this particular project.

My setup:

Persistence package (Common .net project that houses the databaseContext and is used as a package)

In the DBContext Class:

` [AuditDbContext(Mode = AuditOptionMode.OptIn, IncludeEntityObjects = false, AuditEventType = "EF")] public class CWContext : DbContext { private readonly DbContextHelper _helper = new DbContextHelper(); private readonly IAuditDbContext _auditContext;

    public CWContext(string connectionString) : base(GetOptions(connectionString))
    {
        _auditContext = new DefaultAuditContext(this);
        _helper.SetConfig(_auditContext);
    }
    . . . 
    . . .

    public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        try
        {
            //return await base.SaveChangesAsync();
            return await _helper.SaveChangesAsync(_auditContext, () => base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken));
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
    {
        try
        {
            //return await base.SaveChangesAsync();
            return await _helper.SaveChangesAsync(_auditContext, () => base.SaveChangesAsync(cancellationToken));
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public override int SaveChanges()
    {
        try
        {
            //return base.SaveChanges();
            return _helper.SaveChanges(_auditContext, () => base.SaveChanges());
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

`

In the Entity class: [AuditInclude] public class Patient { . . .

CSPROJ:

net6.0 . . . In the .net Project that uses this Persistence package (Project A-NotWorking) In the ProjectStartup code: var app = builder.Build(); . . . AuditLogsSetup.SetupAuditConfigs(); app.Run(); } The AuditLogsSetup static class: (Moving the contents of the static method directly up to the startup code didn't work either) public static class AuditLogsSetup { ////////////////////// I am using a custom data provider to write logs into ES from kinesis firehose here in other projects, but here, the default File Provider is also not working ///////////////////////////////// public static void SetupAuditConfigs() { Audit.Core.Configuration.Setup() .UseFileLogProvider(_ => _ .Directory(@"C:\Logs\AuditLogs") .FilenameBuilder(ev => $"{ev.StartDate:yyyyMMddHHmmssffff}_{ev.EventType}.json")); } } CSPROJ: net6.0 . . . . . . Am I missing configuring something here?

[thepirat000]

When using SaveChanges override method without inheritance, you have to make sure you are overriding the following two methods in your DbContext:

int SaveChanges(bool acceptAllChangesOnSuccess)

Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken))

Just so you know, no other SaveChanges override is needed, since the other overloads will call one of these two.

It looks like you are not overriding the first one, so maybe the client that is not triggering the audit is calling the int SaveChanges(bool acceptAllChangesOnSuccess)

Another option is to use a SaveChanges interceptor instead of overriding the SaveChanges:

https://github.com/thepirat000/Audit.NET/blob/master/src/Audit.EntityFramework/README.md#3-with-the-provided-save-changes-interceptor

[shubhamCedargate]

Hello!, I Changed the Context class and override the two methods that you mentioned. I also tried using the SaveChanges interceptors. Both didn't work and the changes are not audited still.

When I put a debug point on the overridden method, _auditContext is populated with correct values, but still, no audit events are triggered.

On application startup, If I put a debug point on the Audit core configs, that is reached as well

I have other projects that use the same Persistance package for database operations, Audit events are being triggerres successfully from those projects though.

I have the same Audit configurations in all of these projects. Its not working on this particular project only

[thepirat000]

Are you calling Audit.Core.Configuration.Setup() from all of your runnable projects? for example, if you want to audit from an ASP.NET app and a console app, you need to set up the data provider on both.

Can you share a minimal solution that reproduces the issue?

[shubhamCedargate]

It seems I found out why Audit events were not being triggered

I am using the following method to Bulk-Update am Entity list

    public async Task BulkUpdatePatients(string connectionString, List<Patient> patients)
    {
        var _db = new customContext(connectionString);
        _db .BulkUpdate(patients);
        foreach (var patient in patients)
        {
            var patientSources = patient.PatientSources;
            _db .BulkUpdate(patientSources.ToList());
        }
        await _db .SaveChangesAsync();
    }

When saving in the database this way, Audit events are not getting triggered.

But if I add the following in the method body,

            `patient.SOME_Column = "String_value";
            _db.Patients.Update(patient);
            `

The audit event is triggered.

    public async Task BulkUpdatePatients(string connectionString, List<Patient> patients)
    {
        var _db = new customContext(connectionString);
        _db .BulkUpdate(patients);
        foreach (var patient in patients)
        {
           patient.SOME_Column = "String_value";
            _db.Patients.Update(patient);
            var patientSources = patient.PatientSources;
            _db .BulkUpdate(patientSources.ToList());
        }
        await _db .SaveChangesAsync();
    }

How can I make it so that doing Bulk-Updates also trigger the Audit Events.

[thepirat000]

The Bulk updates and deletes using ExecuteUpdate() and ExecuteDelete() are executed directly to the database, without making use of the EF's change tracker, so the SaveChanges interception will not get those events.

Check this: https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete#change-tracking

The only way would be to add the AuditCommandInterceptor, which will generate logs like:

{
  "CommandEvent": {
    "Method": 0,
    "CommandType": 1,
    "CommandText": "DELETE TOP(@__p_0) FROM [v]\r\nFROM [Values] AS [v]",
    "Result": 1,
    "Database": "Test_1",
    "ConnectionId": "3001fe63-b822-4422-b59e-7b941a05b2ad",
    "DbConnectionId": "33e15718-7c07-45d8-953c-acea98a0a0a8",
    "ContextId": "b72a194f-7b66-4fe2-8a4d-5e178d0edb7d:0",
    "IsAsync": false,
    "Success": true
  },
  "EventType": "EF",
  ...

[shubhamCedargate]

I am not getting what changed in the Logs

The generated JSON looks like this:

{"commandEvent":{"method":2, "commandType":1, "commandText": "SELECT [p].[Id], [p].[Address_Address1],. . . [p1].[Patient_Id], [p1].[Type]\r\nFROM [Patients] AS [p]\r\nLEFT JOIN (\r\n SELECT [p0].[Id] ,[p0].[DataSourceId] ,[p0].[EmployerId] ,[p0].[LastSourceOfModification] ,[p0].[LocationId] ,[p0].[PatientId] ,. . . ,[d].[autoCreateReferralAppts]\r\n FROM [PatientDataSources] AS [p0]\r\n LEFT JOIN [DataSources] AS [d] ON [p0].[DataSourceId] = [d].[Id]\r\n WHERE [d].[AnalyticsIntegrated] = CAST(1 AS bit)\r\n ) AS [t] ON [p].[Id] = [t].[PatientId]\r\nLEFT JOIN [PhoneNumbers] AS [p1] ON [p].[Id] = [p1].[Patient_Id]\r\nWHERE [p].[EnterpriseId] IN (. . .) \r\nORDER BY [p].[Id], [t].[Id], [t].[Id0]", " database":"888", "connectionId":"b1a843bf-b4ad-442b-9007-0basdasdads", "dbConnectionId":"asdasdasd-aefa-4103-8ac7-86e3bf6f7727", "contextId":"asdadasda-1cac-4d3e-9ae6-e121ad6d4275:0", "isAsync":true}, "eventType":"ExecuteReader", "environment":{"userName":"Shubham.Pradhan", "machineName":"CGNPLAP586", "domainName":"corp", "callingMethodName":"System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start()", "assemblyName":"System.Private.CoreLib, Version = 6.0.0.0, Culture = neutral, PublicKeyToken = sdasdadasd", "culture":"en-US", "customFields":{}}, "customFields":{"user":"API"}, "startDate":"2023-06-22T05:14:29.3827405Z", "duration":0}

The command text has a "SELECT" statement after performing BulkUpdate.

Ive placed the AuditCommandInterceptor config inside DbContext configuration:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.AddInterceptors(new AuditCommandInterceptor()); }

[thepirat000]

I didn't find any official documentation stating so, but I think BulkUpdate cannot be intercepted in any way.

https://www.milanjovanovic.tech/blog/how-to-use-the-new-bulk-update-feature-in-ef-core-7

[shubhamCedargate]

Hello!

I am facing another issue here i am getting the old value and new value exactly the same even when there is an update.

My Updating method looks like this:

    public async Task BulkUpdatePatientsAndPatientDataSource(string connectionString, List<Patient> patients)
    {
        Context _db = new MyContext(connectionString);
        _db.Patients.UpdateRange(patients);
        foreach (Patient patient in patients)
        {
            ICollection<PatientDataSource> patientDataSources = patient.PatientDataSources;
            _db.PatientDataSources.UpdateRange(patientDataSources);
        }

        await _db.SaveChangesAsync();
    }

I called this method like this :

            await _patientDemographicsRepository.BulkUpdatePatientsAndPatientDataSource(clientCredential.ConnectionString, patients);

here i passed the list of updated patients

which i got by creating a new instance of the MyContext like this:

MyContext _db = new MyContext(connectionString);

and then modifying a fey patient entities.

Why am I getting the old value and new value exactly the same even when there is an update.

Is there a way I get the correct change records?

The Audit Json looks like this

{
    "entityFrameworkEvent": {
        "database": "DB001",
        "connectionId": "aaaabbbb",
        "contextId": "aaaabbbb",
        "entries": [
            {
                "table": "Patients",
                "name": "Patient",
                "primaryKey": {
                    "id": "ididididididididididididididid"
                },
                "action": "Update",
                "changes": [
                    {
                        "columnName": "Address_Address1Decrypted",
                        "originalValue": "KAthmandu",
                        "newValue": "KAthmandu"
                    },
                    {
                        "columnName": "Name_FirstNameDecrypted",
                        "originalValue": "SuPr",
                        "newValue": "SuPr"
                    },
                    {
                        "columnName": "Name_LastNameDecrypted",
                        "originalValue": "Awesome",
                        "newValue": "Awesome"
                    },
                    {
                        "columnName": "Name_MiddleName",
                        "originalValue": "",
                        "newValue": ""
                    },
                    {
                        "columnName": "Name_MiddleNameDecrypted",
                        "originalValue": "",
                        "newValue": ""
                    }
                ],
                "columnValues": {
                    "id": "ididididididididididididididid",
                    "address_Address1Decrypted": "KAthmandu",
                    "name_FirstNameDecrypted": "SuPr",
                    "name_LastNameDecrypted": "Awesome",
                    "name_MiddleName": null,
                    "name_MiddleNameDecrypted": null
                },
                "valid": true,
                "customFields": {}
            }
        ],
        "result": 24,
        "success": true,
        "customFields": {}
    },
    "eventType": "EF",
    "environment": {
        "userName": "Shubham.Pradhan",
        "machineName": "LAPTOP",
        "domainName": "corp",
        "callingMethodName": "System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start()",
        "assemblyName": "System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=aabbasd",
        "culture": "en-US",
        "customFields": {}
    },
    "customFields": {
        "user": null,
        "environment": "DEV"
    },
    "startDate": "2023-07-06T14:09:22.1093073Z",
    "endDate": "2023-07-06T14:09:22.4538774Z",
    "duration": 345
}

[thepirat000]

Doing the update with Update / UpdateRange will not retrieve the old values from the database, that's the way EF Change Tracker works.

From EF documentation here "Note that whenever real original property values are not available (e.g. entity was not yet persisted to the database) this will default to the current property values of this entity."

Please check this: https://github.com/thepirat000/Audit.NET/issues/53#issuecomment-315434618

[thepirat000]

I'm thinking that the Audit.EF library could provide an optional mechanism to load the original values before any modification, to cover the use of Update / UpdateRange (and also Remove and manually attached entities). This mechanism could use the GetDatabaseValues() function.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.changetracking.entityentry.getdatabasevalues?view=efcore-7.0#microsoft-entityframeworkcore-changetracking-entityentry-getdatabasevalues

Will take a deeper look

[thepirat000]

Starting from version 21.0.2, the Audit.EntityFramework and Audit.EntityFramework.Core libraries introduce a new feature.

The setting called ReloadDatabaseValues can be globally or individually configured for a DbContext instance. It determines whether the original values of audited entities should be fetched from the database prior to saving the audit event.

Consider the following examples of update and delete operations:

using (var context = new MyAuditedContext())
{
    //context.ReloadDatabaseValues = true;

    context.Cars.Update(new Car() { Id = 123, Name = "New name" });
    await context.SaveChangesAsync();
}

using (var context = new MyAuditedContext())
{
    //context.ReloadDatabaseValues = true;

    context.Entry(new Car() { Id = 123 }).State = EntityState.Deleted;
    await context.SaveChangesAsync();
}

When doing modifications like these, the EF Change Tracker will lack knowledge of the original values.

Enabling the ReloadDatabaseValues setting triggers an extra database query to retrieve the original values prior to the update operation. As a result, the audit event will contain the original values.

[shubhamCedargate]

Hello! This solution seems to be working when I inherit the AuditDbContext class

[AuditDbContext(Mode = AuditOptionMode.OptIn, IncludeEntityObjects = false, AuditEventType = "EF")]
public class MyContext : AuditDbContext
{

and set the ReloadDatabaseValues when creating the db context

public async Task BulkUpdatePatientsAndPatientDataSource(string connectionString, List<Patient> patients)
{
    Context _db = new MyContext(connectionString);
    _dbCW.ReloadDatabaseValues = true;
    _db.Patients.UpdateRange(patients);
    foreach (Patient patient in patients)
    {
        ICollection<PatientDataSource> patientDataSources = patient.PatientDataSources;
        _db.PatientDataSources.UpdateRange(patientDataSources);
    }
    await _db.SaveChangesAsync();
}

But this is not working when I try and configure it Globally for the DbContext

[AuditDbContext(Mode = AuditOptionMode.OptIn, IncludeEntityObjects = false, AuditEventType = "EF")]
public class MyContext : DbContext
{
    public MyContext(string connectionString) : base(GetOptions(connectionString))
    {
        _auditContext = new DefaultAuditContext(this);
        _auditContext.ReloadDatabaseValues = true;
        _helper.SetConfig(_auditContext);
    }

    public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
         return await _helper.SaveChangesAsync(_auditContext, () => base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken));
    }

    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        return _helper.SaveChanges(_auditContext, () => base.SaveChanges(acceptAllChangesOnSuccess));
    }
}

Is this the correct approach to configure it Globally for the DbContext?

[thepirat000]

You could do that, but you should call _auditContext.ReloadDatabaseValues = true AFTER calling _helper.SetConfig(), otherwise it will be overridden by the default configuration:

public MyContext(string connectionString) : base(GetOptions(connectionString))
{
    _auditContext = new DefaultAuditContext(this);
    _helper.SetConfig(_auditContext);
    _auditContext.ReloadDatabaseValues = true;
}

But the recommended way to set the configuration globally is by using the fluent API, for example:

Audit.EntityFramework.Configuration.Setup()
    .ForAnyContext(cfg => cfg.ReloadDatabaseValues());

Or:

Audit.EntityFramework.Configuration.Setup()
    .ForContext<MyContext>(cfg => cfg.ReloadDatabaseValues());

Also, I've just realized I didn't add a property to the AuditDbContextAttribute, but will do it for the next release.

So, you will be able to set the configuration like this:

[AuditDbContext(ReloadDatabaseValues = true, Mode = AuditOptionMode.OptIn, IncludeEntityObjects = false, AuditEventType = "EF")]
public class MyContext : DbContext
{
   ...
}