Auditing with EF Core and Sql Server – Part 1: Temporal tables

It’s part of a lot of projects in which I’m involved. Keeping track of data that is changed, when the change occurred and, the most important part, who made the change. Apart from tracking down unauthorised changes it also helps when users claim they didn’t make a change. They tend to forget changes they made. It’s a recurring requirement, but most of the time it’s there without the need for any further features. I’ve never had to create a diff screen or the like. We just need to keep track of all the changes to be on the safe side.

Most of the time you will see this being implemented with an interface that has to be implemented by your entities. The IAuditable interface with ModifiedByUser and ModifiedDate. It’s not that hard to implement and there are plenty of examples out there. Entity Framework has all the info you need, you can even get access to the old and new values of the properties and log them as well.

A quick Google search shows you how others are doing it, like here or even a library that keeps track of it. It’s not that hard to roll your own implementation and you’ll learn more about EF in the process of implementing it.

I always consider it to be noise. It is a cross cutting concern showing up in all your entities and then there is the additional code in the dbcontext which needs to look at the changed entities and their properties. For my current project I wanted to look at it from a different angle and keep everything out of my entities and if possible the DbContext.

I’m using SQL Azure (or SQL 2016) and had read about temporal tables last year and it seemed this would be a perfect fit. SQL server can automatically insert the validity date of data and their value at the time in a history table.

When enabling it on a table you need to add two columns for the validity period and the rest is taken care of by SQL Server. After inserting one record in the Products table, it will contain one row and its history table will be empty.

If I then issue an update statement, I get one row in the products table and one in its history table.

So far so good, we get an automatic history log. I was concerned that if I would add a column to the source table I would have to create an update to the corresponding history table but that is also handled by SQL Server. Migrations would just work. There are some restrictions however, as cascading deletes are no longer possible and would need to be handled by the application. You can find more limitations here.

To enable temporal tables on all my entities I created a couple of extension methods and added a migration to my code base.

public partial class EnableTemporalTable : Migration
{
    private string schemaName = "Logging";
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql($"CREATE SCHEMA {schemaName}");
        var entityTypes = base.TargetModel.GetEntityTypes();
        foreach (var entityType in entityTypes)
        {
            migrationBuilder.AddAsTemporalTable(entityType, schemaName);
        }
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var entityTypes = base.TargetModel.GetEntityTypes();
        foreach (var entityType in entityTypes)
        {
            migrationBuilder.RemoveAsTemporalTable(entityType, schemaName);
        }
        migrationBuilder.Sql($"DROP SCHEMA {schemaName}");
    }
}

Note that I’m iterating all entities. If you have a many to many relationship you will have to model it as an association entity to have it also gain the temporal features.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
 
public static class EnableTemporalDataOnTable
{
    public static void AddAsTemporalTable(this MigrationBuilder migrationBuilder, IEntityType entityType, string temporalScheme, string temporalTableName)
    {
        var tableName = entityType.Relational().TableName;
        var schemaName = entityType.Relational().Schema ?? "dbo";
        migrationBuilder.Sql($@"
                    IF NOT EXISTS (SELECT * FROM sys.[tables] t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = '{tableName}' AND temporal_type = 2 and s.name = '{schemaName}')
                    BEGIN
                        ALTER TABLE {schemaName}.{tableName}   
                        ADD  ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
                                constraint DF_{tableName}_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
                            , ValidTo datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
                                constraint DF_{tableName}_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
                            , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);   
 
                        ALTER TABLE {schemaName}.{tableName}    
                        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {temporalScheme}.{temporalTableName})); 
                    END
                ");
 
    }
 
    public static void AddAsTemporalTable(this MigrationBuilder migrationBuilder, IEntityType entityType, string temporalScheme)
    {
        var tableName = entityType.Relational().TableName;
        AddAsTemporalTable(migrationBuilder, entityType, temporalScheme, tableName);
    }
 
    public static void RemoveAsTemporalTable(this MigrationBuilder migrationBuilder, IEntityType entityType, string temporalScheme, string temporalTableName)
    {
        var tableName = entityType.Relational().TableName;
        var schemaName = entityType.Relational().Schema ?? "dbo"; 
        string alterStatement = $@"ALTER TABLE {tableName} SET (SYSTEM_VERSIONING = OFF);";
        migrationBuilder.Sql(alterStatement);
        alterStatement = $@"ALTER TABLE {tableName} DROP PERIOD FOR SYSTEM_TIME";
        migrationBuilder.Sql(alterStatement);
        alterStatement = $@"ALTER TABLE {tableName} DROP DF_{tableName}_SysStart, DF_{tableName}_SysEnd";
        migrationBuilder.Sql(alterStatement);
        alterStatement = $@"ALTER TABLE {tableName} DROP COLUMN SysStartTime, COLUMN SysEndTime";
        migrationBuilder.Sql(alterStatement);
        alterStatement = $@"DROP TABLE {temporalScheme}.{temporalTableName}";
        migrationBuilder.Sql(alterStatement);
    }
 
    public static void RemoveAsTemporalTable(this MigrationBuilder migrationBuilder, IEntityType entityType, string temporalScheme)
    {
        var tableName = entityType.Relational().TableName;
        RemoveAsTemporalTable(migrationBuilder, entityType, temporalScheme, tableName);
    }
}

This is largely based on this blog post. I just adapted it to be a bit more generic. If I would create a migration for a new table I could just call the extension method and have it temporal in one go.

But there was still one missing piece. Who made the change? I have no property on my entities to store this information and thus no column in the table or history table to store that info. In one of the first versions of the article on the MSDN website that introduces temporal tables it was mentioned that you could track the user, but that reference has since been deleted. So custom code is necessary. I didn’t want to add the property to each of my entities so I used a new feature in EF core called shadow properties. Funny enough, the example on the MSDN site is about auditing.

So I created an override for the OnModelCreating and the SaveChanges and SaveChangesAsync method of the DbContext. So not completely honouring my initial goal to keep it out of my application code.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        modelBuilder.Entity(entityType.ClrType).Property<string>("ModifiedBy");
    }
}

The SaveChanges and SaveChangesAsync would call into this method and the principal was injected in the constructor of the DbContext.

public void SetModifiedInformation()
{
    var modifiedByEntries = ChangeTracker.Entries()
        .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified || e.State == EntityState.Deleted);
 
    foreach (EntityEntry entry in modifiedByEntries)
    {
        entry.Property("ModifiedBy").CurrentValue = this.principal?.Identity?.Name;
    }
}
 
public override int SaveChanges()
{
    SetModifiedInformation();
    return base.SaveChanges();
}
 
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
{
    SetModifiedInformation();
    return await base.SaveChangesAsync(cancellationToken);
}

This worked, but one scenario is now missing in this solution. We know who created or updated a row, but with a delete we lose the value in the ModifiedBy property. The deleted row is copied from the source table to the history table. I considered the ultimate hack: create an instead of trigger on the table and issue an update and then a delete but luckily SQL server prevented me from even trying it. Instead of triggers are not possible with table that is temporal. I considered adding a separate table to keep track of who deleted a certain row, but this would again introduce more code than I wanted and clutter the codebase. I could introduce soft deletes so that a delete will become an update but that just opens another can of worms.

So I went back to the drawing board to find a better solution, which turned out to be a blast from the past combined with a new feature in SQL server 2016. But that’s for next time.

6 Replies to “Auditing with EF Core and Sql Server – Part 1: Temporal tables”

    1. I think next week part 2 will be published. It’s always a matter of finding enough time to write a post.

    1. Indeed you can, I mention it at the end of the blog post. It does however also have some drawbacks. Again your mileage may vary.

Leave a Reply to Claus Behn Cancel 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.