Entity Framework Core with Audit Tables

Alexandros Pappas

5.00/5 (1 vote)

Apr 16, 2020

CPOL

6 min read

12293

114

This project describes how to use Entity Framework Core with update triggers and audit tables.

Download source code - 5.2 KB

Introduction

This article provides an example of using Entity Framework Core with existing SQL Server tables. The tables have an update trigger that copies the current version of the record to the respective audit table, and updates the record with a new TraceVersion and UTimeStamp.

The example tables:

The Entity Framework classes:

Background

All tables in the database have four additional columns for auditing purposes:

UserId (int): Id of the user that modified the record
Deleted (bit): Indicates whether the record is deleted
TraceVersion (int): Version number of the record
UTimeStamp (datetime): Date and time of last modification

The SQL operations do the following:

INSERT: There is no trigger on Insert, the record is inserted as is to the table. The data access layer ensures that Deleted=0, TraceVersion=1, and UTimeStamp=current date and time.
UPDATE: There is an AFTER UPDATE trigger. If
- Deleted=0: The current record in the table is inserted to the audit table, then the current record is updated, the TraceVersion is incremented by 1, and the UTimeStamp is set to the current date and time.
- Deleted=1: As with Deleted=0, but additionally, the updated record (with Deleted=1) is also inserted to the audit table, and is deleted from the main table.
DELETE: The AFTER DELETE trigger prohibits the DELETE statement. Deletion of records must be done through updating the Deleted column to 1 (like a soft delete).

For example, the following statements will produce the following records in the database:

INSERT ABC_Users(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active) 
VALUES(1,0,1,GETDATE(),'gmeyer','George','Meyer',1)

One record is inserted to the main table:

Table	Id	UserId	Deleted	TraceVersion	UTimeStamp	NTUser	FName	LName	Active
`ABC_Users`	`2`	`1`	`0`	`1`	`2019-09-10 11:08:23.340`	`gmeyer`	`George`	`Meyer`	`1`

UPDATE ABC_Users SET LName='Meyers' WHERE Id=2

The current record (with TraceVersion=1) is inserted to the Audit table. The updated record gets TraceVersion=2:

Table	Id	UserId	Deleted	Trace Version	UTimeStamp	NTUser	FName	LName	Active
`ABC_Users_Audit`	`2`	`1`	`0`	`1`	`2019-09-10 11:08:23.340`	`gmeyer`	`George`	`Meyer`	`1`
`ABC_Users`	`2`	`1`	`0`	`2`	`2019-09-10 11:17:03.640`	`gmeyer`	`George`	`Meyers`	`1`

UPDATE ABC_Users SET Deleted=1

The current record (with TraceVersion=2) is inserted to the Audit table. The updated record (with Deleted=1) gets TraceVersion=3 and is also added to the Audit table. The record is deleted from the main table:

Table	Id	UserId	Deleted	Trace Version	UTimeStamp	NTUser	FName	LName	Active
`ABC_Users_Audit`	`2`	`1`	`0`	`1`	`2019-09-10 11:08:23.340`	`gmeyer`	`George`	`Meyer`	`1`
`ABC_Users_Audit`	`2`	`1`	`0`	`2`	`2019-09-10 11:17:03.640`	`gmeyer`	`George`	`Meyers`	`1`
`ABC_Users_Audit`	`2`	`1`	`0`	`3`	`2019-09-10 11:17:44.020`	`gmeyer`	`George`	`Meyers`	`1`

No record in ABC_Users.

The SQL statements to create the tables and the trigger, and to insert an administrator user are the following:

DROP TABLE IF EXISTS ABC_Users
GO
CREATE TABLE [dbo].[ABC_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserId] [int] NOT NULL,
    [Deleted] [bit] NOT NULL,
    [TraceVersion] [int] NOT NULL,
    [UTimeStamp] [datetime] NOT NULL,
    [NTUser] [varchar](50) NOT NULL,
    [FName] [varchar](20) NOT NULL,
    [LName] [varchar](50) NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [IX_ABC_Users] UNIQUE NONCLUSTERED ([NTUser] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ABC_Users]  WITH CHECK ADD  CONSTRAINT [FK_ABC_Users_ABC_Users] _
                               FOREIGN KEY([UserId])
REFERENCES [dbo].[ABC_Users] ([Id])
GO

DROP TABLE IF EXISTS ABC_Users_Audit
GO
CREATE TABLE [dbo].[ABC_Users_Audit](
    [Id] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [Deleted] [bit] NOT NULL,
    [TraceVersion] [int] NOT NULL,
    [UTimeStamp] [datetime] NOT NULL,
    [NTUser] [varchar](50) NOT NULL,
    [FName] [varchar](20) NOT NULL,
    [LName] [varchar](50) NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_ABC_Users_Audit] PRIMARY KEY CLUSTERED ([Id] ASC, 
            [TraceVersion] ASC) ON [PRIMARY]) ON [PRIMARY]
GO

---------- AUDIT TRIGGER SCRIPT FOR TABLE ABC_Users---------------
CREATE TRIGGER [dbo].[trABC_Users_AUDIT_UD] ON [dbo].[ABC_Users]
AFTER UPDATE, DELETE
AS
/* If no rows were affected, do nothing */
IF @@ROWCOUNT=0
    RETURN

SET NOCOUNT ON
BEGIN TRY
    DECLARE @Counter INT, @Now DATETIME
    SET @Now = GETDATE()
    /* Check the action (UPDATE or DELETE) */
    SELECT @Counter = COUNT(*)
    FROM INSERTED
    IF @Counter = 0 -->; DELETE
        THROW 50000, 'DELETE action is prohibited for ABC_Users', 1

    /* Insert previous record to Audit */
    INSERT INTO ABC_Users_Audit([Id],[UserId],[Deleted], _
                [TraceVersion],[UTimeStamp],[NTUser],[FName],[LName],[Active])  
    SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],_
                  d.[UTimeStamp],d.[NTUser],d.[FName],d.[LName],d.[Active]
    FROM DELETED d

    /* Update master record TraceVersion, UTimeStamp */
    UPDATE main
    SET main.TraceVersion = d.TraceVersion + 1, main.UTimeStamp = @Now
    FROM ABC_Users main
    INNER JOIN DELETED d ON d.Id = main.Id
    INNER JOIN INSERTED i ON i.Id = main.Id

    /* Process deleted rows */
    IF NOT EXISTS(SELECT 1 FROM INSERTED WHERE Deleted = 1)
        RETURN
    /* Re-insert last updated master record into Audit table where Deleted = 1 */
    INSERT INTO ABC_Users_Audit([Id],[UserId],[Deleted],[TraceVersion],_
                                [UTimeStamp],[NTUser],[FName],[LName],[Active])  
    SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],d.[UTimeStamp],_
                             d.[NTUser],d.[FName],d.[LName],d.[Active]
    FROM ABC_Users d
    INNER JOIN INSERTED i ON d.Id = i.Id
    WHERE i.Deleted = 1

    /* Delete master record */
    DELETE c
    FROM ABC_Users c
    INNER JOIN INSERTED i ON c.Id = i.Id
    WHERE i.Deleted = 1
END TRY
BEGIN CATCH
    THROW
END CATCH
GO

ALTER TABLE [dbo].[ABC_Users] ENABLE TRIGGER [trABC_Users_AUDIT_UD]
GO

INSERT ABC_Users(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active)
VALUES(1,0,1,GETDATE(),'admin','Admin','Admin',1)

Entity Framework creates for every updated Entity an SQL UPDATE statement, but does not create a SELECT statement to retrieve the columns TraceVersion and UTimeStamp that were updated by the trigger. Entity Framework creates for every deleted Entity an SQL DELETE statement, but in this case, an UPDATE statement is required setting the column Deleted to 1.

Using the Code

The project is a Console application.

The following Nuget packages were installed:

Install-Package Microsoft.Extensions.Logging.Console
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer

Main Method

The Main method in Program.cs inserts, updates and deletes a record exactly as the above SQL statements but using Entity Framework:

static void Main(string[] args)
{
    try
    {
        AbcUsers user;
        var optionsBuilder =
            new DbContextOptionsBuilder<model1>()
            .UseSqlServer(GetConnectionString())
            .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()));
        Console.WriteLine("Adding user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            var dateNow = DateTime.Now;
            user = new AbcUsers()
            {
                UserId = 1,
                Ntuser = "gmeyer",
                Fname = "George",
                Lname = "Meyer",
                Active = true
            };
            context.AbcUsers.Add(user);
            context.SaveChanges();
            Console.WriteLine("user.Id={0}", user.Id);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Updating user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            context.AbcUsers.Attach(user);
            user.Lname = "Meyers";
            context.SaveChanges();
            Console.WriteLine("user.TraceVersion={0}", user.TraceVersion);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Deleting user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            context.AbcUsers.Attach(user);
            context.AbcUsers.Remove(user);
            context.SaveChanges();
            Console.WriteLine("context.Entry(user).State={0}", context.Entry(user).State);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Test ok");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Test not ok");
        Console.WriteLine(ex.ToString());
    }
    Console.WriteLine("Press any key to close");
    Console.ReadKey();
}

Running the Program

To run the program, a database on an SQL Server should be created, and in that database, the two tables should be created with the SQL script given in the CreateTables.sql script. The connection string should be modified accordingly in the method GetConnectionString of Program.cs. In the provided connection string, the database is called DB1. Running the project should create the following output:

Adding user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (77ms) [Parameters=[@p0='?' (DbType = Boolean), _
      @p1='?' (DbType = Boolean), @p2='?' (Size = 20) (DbType = AnsiString), _
      @p3='?' (Size = 50) (DbType = AnsiString), @p4='?' _
      (Size = 50) (DbType = AnsiString), @p5='?' (DbType = Int32), _
      @p6='?' (DbType = Int32), @p7='?' (DbType = DateTime)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [ABC_Users] ([Active], [Deleted], [FName], [LName], _
                  [NTUser], [TraceVersion], [UserId], [UTimeStamp])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
      SELECT [Id]
      FROM [ABC_Users]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
user.Id=2
ChangeTracker.Entries().ToList().Count=1
Updating user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (181ms) [Parameters=[@p1='?' (DbType = Int32), _
      @p0='?' (Size = 50) (DbType = AnsiString), @p2='?' (DbType = Int32)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [ABC_Users] SET [LName] = @p0
      WHERE [Id] = @p1 AND [TraceVersion] = @p2;
      SELECT @@ROWCOUNT;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TraceVersion, UTimeStamp FROM ABC_Users WHERE Id=2
user.TraceVersion=2
ChangeTracker.Entries().ToList().Count=1
Deleting user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (27ms) [Parameters=[@p1='?' (DbType = Int32), _
      @p0='?' (DbType = Boolean), @p2='?' (DbType = Int32)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [ABC_Users] SET [Deleted] = @p0
      WHERE [Id] = @p1 AND [TraceVersion] = @p2;
      SELECT @@ROWCOUNT;
context.Entry(user).State=Detached
ChangeTracker.Entries().ToList().Count=0
Test ok
Press any key to close

IAudited

The interface IAudited is implemented by all entities. It defines that all entities have the columns Id, Deleted, TraceVersion and UTimeStamp.

interface IAudited
{
    int Id { get; set; }
    bool Deleted { get; set; }
    int TraceVersion { get; set; }
    DateTime UTimeStamp { get; set; }
}

Model1.cs

The entity framework model Model1.cs was created in the Nuget Package Manager Console with the command:

Scaffold-DbContext 'data source=localhost;initial catalog=DB1;
integrated security=True;' Microsoft.EntityFrameworkCore.SqlServer 
-Context Model1 -F -DataAnnotations -Tables ABC_Users

The connection string in the above command might have to be adjusted, but it is not necessary to run this command again.

Model1.Partial.cs

Custom code for the class generated by Scaffold-DbContext may be placed here.

Partial Class CUsers

Every audited table must implement the interface IAudited:

public partial class CUsers : IAudited { }

For every table, a line similar to the above must be added.

Class TVUT

This class holds the fields, TraceVersion and UTimeStamp. It is used to reload these two values after an update statement.

public class TVUT
{
    public int TraceVersion { get; set; }
    public DateTime UtimeStamp { get; set; }
}

Partial Class Model1

The class Model1 is generated by the Scaffold-DbContext command. Any custom code concerning this class is placed in the partial class. It holds a DbSet of TVUT, so that a query can be written to retrieve the TraceVersion and UTimeSTamp.

public partial class Model1
{
    public DbSet<tvut> TVUTs { get; set; }
    ...
}

OnModelCreatingPartial

In this method, special attributes of the entities are set. The TVUT entity is marked as having no key, and the TraceVersion field of the AbcUsers entity is set as the concurrency token. That means, this field is added in the WHERE clause of the UPDATE and DELETE statements, for example:

UPDATE [dbo].[CUsers]
SET [LName] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))

Optimistic Concurrency is implemented this way.

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<tvut>(e => e.HasNoKey());
    modelBuilder.Entity<abcusers>(entity => entity.Property
                 (e => e.TraceVersion).IsConcurrencyToken(true));
}

For every table, a line similar to the line of entity AbcUsers above must be added to the function OnModelCreatingPartial.

GetTableName

When Scaffold-DbContext is run without the option -UseDatabaseNames, Entity Framework creates the entity classes from the table names by removing the underscore characters, and by converting all characters except the first to lower case. This function is used to retrieve the table name of a given entity object.

private string GetTableName(object entity)
{
    var entityType = Model.FindEntityType(entity.GetType());
    return entityType.GetTableName();
}

SaveChanges

The method SaveChanges is overridden.

public override int SaveChanges()
{
    var entriesAudited = ChangeTracker.Entries().Where(e => e.Entity is IAudited);
    var entriesAdded = entriesAudited.Where(e => e.State == EntityState.Added).ToList();
    var entriesModified = entriesAudited.Where(e => e.State == EntityState.Modified).ToList();
    var entriesDeleted = entriesAudited.Where(e => e.State == EntityState.Deleted).ToList();
    foreach (var item in entriesAdded)
    {
        var entity = (IAudited)item.Entity;
        (entity.Deleted, entity.TraceVersion, entity.UtimeStamp) = (false, 1, DateTime.Now);
    }
    foreach (var item in entriesDeleted)
    {
        item.State = EntityState.Unchanged;
        ((IAudited)item.Entity).Deleted = true;
    }
    var rowCount = 0;
    using (var scope = new TransactionScope())
    {
        rowCount = base.SaveChanges();
        foreach (var item in entriesModified)
        {
            var entity = (IAudited)item.Entity;
            var sql = $"SELECT TraceVersion, _
                      UTimeStamp FROM {GetTableName(entity)} WHERE Id={entity.Id}";
            var tu = TVUTs.FromSqlRaw(sql).ToList()[0];
            (entity.TraceVersion, entity.UtimeStamp) = (tu.TraceVersion, tu.UtimeStamp);
        }
        scope.Complete();
    }
    if (rowCount > 0)
        foreach (var item in entriesDeleted)
            item.State = EntityState.Detached;
    return rowCount;
}

The audited entries are retrieved.
For each added entity in the audited entries, the fields Deleted, TraceVersion and UTimeStamp are filled.
For each deleted entity in the audited entries, the entity is set to unchanged, and then the Deleted field is set to 1. Credit goes to the article Entity Framework Core: Soft Delete. This case is like a soft delete, but the record is moved from the main table to the audit table.
A new transaction is created.
SaveChanges of the base class is called.
For each modified entity, an SQL statement is constructed to retrieve the TraceVersion and UTimeStamp. The SQL statement is executed with FromSqlRaw on the DbSet TVUTs. Once retrieved, the values are assigned to the entity. The transaction is needed because of the reload of these two values. Somebody else could update the entity between the end of base.SaveChanges() and the beginning of TVUTs.FromSqlRaw(sql).
For each deleted entity, its State is changed to Detached, and therefore it is deleted from Model1.

Summary

This project demonstrated that it is possible to create an Entity Framework Model that:

ensures that all new (added) entities get Deleted=false, TraceVersion=1 and UTimeStamp=current date and time.
reloads for all updated entities, the columns TraceVersion and UTimeStamp given by the trigger.
changes all deletions to updates with column Deleted=1 and after saving, detaches these entities.

History

16^th April, 2020: Initial version - Model for table Abc_Users, update reloads TraceVersion and UTimeStamp, optimistic concurrency with column TraceVersion