How To Get Table Name and Action When Using SQL Server Provider?

[chobo2]

Hi

I first started using the EF Provider but found out that it does not track low level changes, so I had to switch to the SqlServer provider but not sure how to set it up properly.

I had for EF

Audit.Core.Configuration.Setup()
.UseEntityFramework(config => config.AuditTypeMapper(t => typeof(EventLog)).AuditEntityAction<EventLog>((ev, entry, entity) =>
    {
    entity.TableName = entry.Table;
    entity.Action = entry.Action;
    }
).IgnoreMatchProperties(true));

Now I come up with this for SqlServer

Audit.Core.Configuration.Setup()
.UseSqlServer(config => config
.ConnectionString("string")
.TableName("EventLogs")
.IdColumName("Id")
.CustomColumn("Action", ev => ev.GetEntityFrameworkEvent() != null ? ev.GetEntityFrameworkEvent().Entiies.First().Action : ev.EventType)
.CustomColumn("TableName", ev => ev.GetEntityFrameworkEvent() != null ? ev.GetEntityFrameworkEvent().Entiies.First().Table : null)
)

I had to do some if statement checks and I found for the low level ones there is no Table name or action (Though EventType).

Is this the way I have to do it, or is there a better way of doing it?

[thepirat000]

I guess you mean the command interceptor events, right?.

The EF data provider cannot handle the command interceptor changes, but only the changes from SaveChanges calls.

This is because the EF data provider relies on the EF change tracker which is not affected by commands.

One option could be to use a conditional data provider wrapper to use EF data provider for the savechanges audits and sql data provider for the rest.

[chobo2]

Cool, let me know if it can be done or not. Not a huge deal if it can't be done.

As far I have my code to get the Action and a table name when it exists. Am I doing it the best way?

[thepirat000]

Your code will work only if you affect a single table on each SaveChanges() call

[thepirat000]

Looks like there is no way to retrieve the table name from an arbitrary DbCommand or SqlDbCommand for EF Core.

Therefore, the only way to retrieve the table name is parsing the command text.

[chobo2]

Okay thanks for looking into it, not that big of a deal. If that is the case you think it is better just to do a conditional wrapper or better to do your example or create a derived SQL data provider?

[thepirat000]

I believe the derived SQL data provider would give you more flexibility. Therefore, if it aligns with your requirements, I would recommend that option

[thepirat000]

For the high-level events, if you need to insert a record for each entry using the SqlDataProvider, I think a better way is to create a derived SQL data provider that inserts a different audit event for each EF entry. You could also create your own derived AuditEvent for this purpose.

For example:

public class SingleEntryAuditEvent : AuditEvent
{
    public EventEntry Entry { get; set; }
}

public class CustomSqlDataProvider : SqlDataProvider
{
    public CustomSqlDataProvider(Action<ISqlServerProviderConfigurator> config) : base(config) {}

    public override object InsertEvent(AuditEvent auditEvent)
    {
        foreach (var entry in auditEvent.GetEntityFrameworkEvent().Entries)
        {
            var entryEvent = CreateEventForEntry(auditEvent, entry);

            base.InsertEvent(entryEvent);
        }

        return null;
    }

    public override async Task<object> InsertEventAsync(AuditEvent auditEvent, CancellationToken cancellationToken = new CancellationToken())
    {
        foreach (var entry in auditEvent.GetEntityFrameworkEvent().Entries)
        {
            var entryEvent = CreateEventForEntry(auditEvent, entry);

            await base.InsertEventAsync(entryEvent, cancellationToken);
        }

        return null;
    }

    private static SingleEntryAuditEvent CreateEventForEntry(AuditEvent auditEvent, EventEntry entry)
    {
        var entryEvent = AuditEvent.FromJson<SingleEntryAuditEvent>(auditEvent.ToJson());
        entryEvent.Entry = entry;

        return entryEvent;
    }
}

So you can configure your data provider like this:

Audit.Core.Configuration.Setup()
    .Use(new CustomSqlDataProvider(config => config
        .ConnectionString("...")
        .TableName("EventLogs")
        .IdColumnName("Id")
        .CustomColumn("Action", ev => (ev as SingleEntryAuditEvent)?.Entry.Action ?? ev.EventType)
        .CustomColumn("TableName", ev => (ev as SingleEntryAuditEvent)?.Entry.Table)
    ));

[thepirat000]

The Audit.EntityFramework.Core reference is still required.

Note this package includes an audit event generator as well as a data provider. So, you will not be using the data provider anymore, but you are still auditing your DbContext.

You can use the optin/optout mechanism, but that will only apply to the high-level events.

[chobo2]

Okay, so opt out/in does not work for low level events?

Also, am I no longer able to use the Fluent Way to set up the Opt IN/OUT and must use the Data Annotation way? As I see the example is using the EF Configuration but if I would do that then it would override my provider correct?

[thepirat000]

You can use OptIn/OptOut with the fluent API, regardless of the data provider.

Audit.EntityFramework.Configuration.Setup()
    .ForAnyContext(c => c.IncludeEntityObjects())
    .UseOptIn();

Please note that Audit.EntityFramework.Configuration.Setup() is for the EF high-level events configuration, distinct from the global configuration found under Audit.Core.Configuration.Setup().

Also, note that the AuditCommandInterceptor will not take into account any configuration set via Audit.EntityFramework.Configuration.Setup()

[chobo2]

Okay, so doing what you put in the example plus what Audit.EntityFramework.Configuration.Setup() will not override each other. I thought as soon as you use either Audit.EntityFramework.Configuration.Setup() or Audit.Core.Configuration.Setup() one of them would be totally overridden.

[thepirat000]

Indeed, they are entirely independent configurations and do not overlap.