Auditing with EF Core and Sql Server – Part 2: Triggers, Session context and dependency injection

In part 1 I looked at using temporal tables to cover auditing requirements. Although a great feature, it did not quite cover the scenario I was after. I wanted a solution that did not impact my .NET code or at least keep the necessary change as small as possible.

So I decided to use a SQL feature I tend to avoid. Triggers. There I’ll say it again: triggers.

They are very powerful, but can be misused. Certainly in legacy systems where people went all out with them, you can be spending quite some time looking into where your SQL statements are actually processed or why your delete is doing an update. But for my current use case they seemed to be the right fit. It’s a feature of the datastore and it would keep all logic out of my code.

So first I created an AuditInfo class in my codebase so I could generate a migration with the create table statement and, should I need it, be able to query it in code.

public class AuditInfo
{
    public long Id { get; set; }
    public string SchemaName { get; set; }
    public string TableName { get; set; }
    public long RowId { get; set; }
    public DateTime ActionDate { get; set; }
    public string Action { get; set; }
    public string Values { get; set; }
    public string ModifiedBy { get; set; }
}

Short and simple. In my current project I gave every entity an Id, even if I could use a composite key. It just makes things a lot easier and means I can get the history of a specific row in an easy way. I also created an extension method to add an audit trigger on every entity.

public static class EnableAuditTriggerExtensions
{
	public static void EnableAuditTrigger(this MigrationBuilder migrationBuilder, IEntityType entityType)
	{
		var tableName = entityType.Relational().TableName;
		var schemaName = entityType.Relational().Schema ?? "dbo";
		migrationBuilder.Sql($@"
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[{schemaName}].[{tableName}_Audit]'))
DROP TRIGGER [{schemaName}].[{tableName}_Audit]");
		migrationBuilder.Sql($@"
CREATE TRIGGER [{schemaName}].[{tableName}_Audit] ON  [{schemaName}].[{tableName}]
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
 
SET NOCOUNT ON;
DECLARE @Action AS CHAR(1)
DECLARE @Id AS BIGINT
DECLARE @Values AS NVARCHAR(MAX)
SET @Action = NULL
SET @Id = NULL
SET @Values = NULL
 
IF EXISTS (SELECT * FROM inserted)
BEGIN
	IF EXISTS(SELECT * FROM deleted)
	BEGIN
		SET @Action = 'U'
	END
	ELSE
	BEGIN
		SET @Action = 'I'
	END
END
ELSE IF EXISTS(SELECT * FROM deleted)
BEGIN
	SET @Action = 'D'
END
IF @Action = 'D'
BEGIN
	INSERT INTO dbo.AuditInfo (
		SchemaName,
		TableName,
		RowId,
                ActionDate,
		ModifiedBy,
		[Action],
		[Values])
		SELECT
		'{schemaName}',
		'{tableName}',
		d.Id,
                SYSUTCDATETIME(),
		(SELECT CONVERT(nvarchar(250), SESSION_CONTEXT(N'CurrentApplicationUser'))),
		@Action,
		(SELECT * FROM deleted vals where vals.Id = d.Id FOR JSON PATH)
		FROM deleted d
END
ELSE
BEGIN
	INSERT INTO dbo.AuditInfo (
		SchemaName,
		TableName,
		RowId,
                ActionDate,
		ModifiedBy,
		[Action],
		[Values])
		SELECT
		'{schemaName}',
		'{tableName}',
		i.Id,
                SYSUTCDATETIME(),
		(SELECT CONVERT(nvarchar(250), SESSION_CONTEXT(N'CurrentApplicationUser'))),
		@Action,
		(SELECT * FROM inserted vals where vals.Id = i.Id FOR JSON PATH)
		FROM inserted i
END
END");
	}
}

Since you get access to the deleted and inserted values in a trigger I can inspect it and insert the correct values in my audit table. It’s not that complicated.

You can also see that I’m using “SESSION_CONTEXT”. All the users connect to the database with the same connection string, since I want to use connection pooling, but I want to log the correct application user along with the changes they made. There are several ways you can solve this but using “SESSION_CONTEXT” gives me an easy way to share information between my application and its database. It is a new feature in SQL Server 2016 and can be used with SQL Azure. SESSION_CONTEXT allows you to associate key value pairs to your current SQLConnection.

Won’t this then fail because you are using the same connection for multiple users? No because if you review the log of what happens to your SQL Server you’ll notice that “sp_reset_connection” gets called. This does a couple of things, including clearing whatever is in the SESSION_CONTEXT. I tried hard to find it being explicitly mentioned on MSDN but could not find any reference. This article proved valuable to clear up some of the underlying functionality. Since the stored procedure clears “CONTEXT_INFO” which I could also have used but is a bit more cumbersome to work with.

The migration itself is then pretty straightforward. Again the same caveat as before, many to many associations need to be modelled as an entity using this approach as I iterate across all entities to add the trigger to the underlying table.

public partial class LogWithTrigger : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        foreach (var entity in TargetModel.GetEntityTypes())
        {
            if (entity.Name != typeof(AuditInfo).FullName)
            {
                migrationBuilder.EnableAuditTrigger(entity);
            }
        }
    }
}

So what remained is to actually call the stored procedure to add the current username to the key (‘CurrentApplicationUser’) that’s being referenced in my trigger. I had to dig a bit into how entity framework works to get this working as I need control over the actual connection. I could just create a SqlConnection and give that to the DbContext. If you do this Entity Framework will just use the connection you supply, however I wanted to use dependency injection. I subclassed SqlServerConnection to get the additional behaviour.

This is the version for EFCore < 2.0:

public class PrincipalAwareConnection
    : SqlServerConnection
{
    private IPrincipal principal;
 
    public PrincipalAwareConnection(IDbContextOptions options, ILogger<SqlServerConnection> logger, IPrincipal principal)
        : base(options, logger)
    {
        this.principal = principal;
    }
 
    public override async Task OpenAsync(CancellationToken cancellationToken = default(CancellationToken))
    {
        bool setSessionState = this.DbConnection.State != ConnectionState.Open;
        await base.OpenAsync();
        if (setSessionState)
        {
            SetSessionInfo();
        }
    }
 
    private void SetSessionInfo()
    {
        using (var cmd = base.DbConnection.CreateCommand())
        {
            cmd.Transaction = base.CurrentTransaction?.GetDbTransaction();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = $"sp_set_session_context";
            var keyParameter = cmd.CreateParameter();
            cmd.Parameters.Add(keyParameter);
            keyParameter.ParameterName = "key";
            keyParameter.Value = "CurrentApplicationUser";
            var valueParameter = cmd.CreateParameter();
            cmd.Parameters.Add(valueParameter);
            valueParameter.ParameterName = "value";
            valueParameter.Value = principal.Identity.Name;
            var readOnlyParameter = cmd.CreateParameter();
            cmd.Parameters.Add(readOnlyParameter);
            readOnlyParameter.ParameterName = "read_only";
            readOnlyParameter.Value = 1;
            cmd.ExecuteNonQuery();
        }
    }
 
    public override void Open()
    {
        bool setSessionState = this.DbConnection.State != ConnectionState.Open;
        base.Open();
        if (setSessionState)
        {
            SetSessionInfo();
        }
    }
}

This is the version for EFCore >= 2.0:

public class PrincipalAwareConnection
    : SqlServerConnection
{
    private IPrincipal principal;
 
    public PrincipalAwareConnection(RelationalConnectionDependencies dependencies, IPrincipal principal)
        : base(dependencies)
    {
        this.principal = principal;
    }
 
    public override async Task<bool> OpenAsync(CancellationToken cancellationToken, bool errorsExpected = false)
    {
        bool setSessionState = this.DbConnection.State != ConnectionState.Open;
        bool opened = await base.OpenAsync(cancellationToken, errorsExpected);
        if (setSessionState)
        {
            SetSessionInfo();
        }
        return opened;
    }
 
    private void SetSessionInfo()
    {
        using (var cmd = base.DbConnection.CreateCommand())
        {
            cmd.Transaction = base.CurrentTransaction?.GetDbTransaction();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = $"sp_set_session_context";
            var keyParameter = cmd.CreateParameter();
            cmd.Parameters.Add(keyParameter);
            keyParameter.ParameterName = "key";
            keyParameter.Value = "CurrentApplicationUser";
            var valueParameter = cmd.CreateParameter();
            cmd.Parameters.Add(valueParameter);
            valueParameter.ParameterName = "value";
            valueParameter.Value = principal.Identity.Name;
            var readOnlyParameter = cmd.CreateParameter();
            cmd.Parameters.Add(readOnlyParameter);
            readOnlyParameter.ParameterName = "read_only";
            readOnlyParameter.Value = 1;
            cmd.ExecuteNonQuery();
        }
    }
 
    public override bool Open(bool errorsExpected = false)
    {
        bool setSessionState = this.DbConnection.State != ConnectionState.Open;
        var opened = base.Open(errorsExpected);
        if (setSessionState)
        {
            SetSessionInfo();
        }
        return opened;
    }
}

In order to override the default SqlServerConnection and allow for the dependency injection of “IPrincipal” I also had to modify the “ConfigureServices” method in my “Startup.cs” class.

services.AddEntityFrameworkSqlServer();
services.AddTransient<ISqlServerConnection, PrincipalAwareConnection>();
services.AddDbContext<MyContext>((sp, options) =>
    options.UseSqlServer(Configuration.GetConnectionString("MyConnectionString"))
           .UseInternalServiceProvider(sp));

When adding, updating or deleting rows the audit table is now populated with values.

This snippet hooks up Entity Framework with the container you use in your application. So now whenever an entity is changed the audit table is filled with the correct values. Perhaps I’ll look into building a diff screen but for now I consider this the best fit for my goals. What do you think?

9 Replies to “Auditing with EF Core and Sql Server – Part 2: Triggers, Session context and dependency injection”

  1. Have you looked into CDC for auditing. Triggers slow down the insert performance. However not sure if Azure Sql supports CDC. Definately worth it if it does,

      1. Hi Simon

        Thanks for the info. I had never heard about CDC. Unfortunately I currently need a solution that works on SQL Azure. Will keep it in mind for a next job.

  2. Hi!

    Nice article. There is however one huge problem with this approach.

    In your trigger you are introducing a O(n^2) operations when doing:

    (SELECT * FROM inserted vals where vals.Id = i.Id FOR JSON PATH)
    FROM inserted i

    Say you modified 1000 records in a single sql update statement.

    This will cause the inserted table to contain 1000 records as well. As a result for every of the 1000 records the following statement:
    (SELECT * FROM inserted vals where vals.Id = i.Id FOR JSON PATH)
    will make a scan through inserted in search of vals.Id.

    This will l give you a grand total of (1 + 1000) * 1000 / 2 = 500500 record reads in order to complete the audit for the 1000 records modified which will simply kill the db.

    Cheers,

    1. Hi!

      Thank you for your well researched reply. I’m using this in an OLTP system so only have a couple of rows which are edited. As always different needs means different solutions. Do you have any suggestions in how to tackle this when batch updates are considered?

      Thanks

  3. Pardon any ignorance regarding EF Core, I am coming from a Fluent Migrator background. With your solution, would another ‘LogWithTrigger’ have to be created if audited tables were added or changed? If I was in Fluent Migrator I would use a migration with ‘MigrationStage.AfterAll’

    1. Yes you would need to alter the generated migration to include the extra bits. A automated way would be to subclass the Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator and add the plumbing in there.

  4. This is exactly what I was looking for. But not sure why I am getting this error after implementing this code

    None of the constructors found with ‘Autofac.Core.Activators.Reflection.DefaultConstructorFinder’ on type ‘PrincipalAwareConnection’ can be invoked with the available services and parameters:\r\nCannot resolve parameter ‘System.Security.Principal.IPrincipal principal’ of constructor ‘Void .ctor(Microsoft.EntityFrameworkCore.Storage.RelationalConnectionDependencies, System.Security.Principal.IPrincipal)’.”,

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.