Entity Framework with Audit Tables

• Download TestEntityFramework.zip - 20 KB

Introduction

This article provides an example of using Entity Framework 6 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.

Background

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

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

The SQL operations do the following:

1. 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, UTimeStamp=current date and time.
2. 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.
3. DELETE: The AFTER DELETE trigger prohibits the DELETE statement. Deletion of records must be done through updating the Deleted column to 1.

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

1. INSERT CUsers(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
   CUsers	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1

2. UPDATE CUsers 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	TraceVersion	UTimeStamp	NTUser	FName	LName	Active
   CUsers_Audit	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1
   CUsers	2	1	0	2	2019-09-10 11:17:03.640	gmeyer	George	Meyers	1

3. UPDATE CUsers 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	TraceVersion	UTimeStamp	NTUser	FName	LName	Active
   CUsers_Audit	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1
   CUsers_Audit	2	1	0	2	2019-09-10 11:17:03.640	gmeyer	George	Meyers	1
   CUsers_Audit	2	1	0	3	2019-09-10 11:17:44.020	gmeyer	George	Meyers	1

   No record in CUsers.

The SQL statement to create the tables, the trigger, and insert an administration user is the following:

CREATE TABLE [dbo].[CUsers](
    [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_CUsers] UNIQUE NONCLUSTERED ([NTUser] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CUsers]  WITH CHECK ADD  CONSTRAINT [FK_CUsers_CUsers] FOREIGN KEY([UserId])
REFERENCES [dbo].[CUsers] ([Id])
GO

CREATE TABLE [dbo].[CUsers_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_CUsers_Audit] PRIMARY KEY CLUSTERED ([Id] ASC, _
            [TraceVersion] ASC) ON [PRIMARY]) ON [PRIMARY]
GO

---------- AUDIT TRIGGER SCRIPT FOR TABLE CUsers---------------
CREATE TRIGGER [dbo].[trCUsers_AUDIT_UD] ON [dbo].[CUsers]
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 CUsers', 1

    /* Insert previous record to Audit */
    INSERT INTO CUsers_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 CUsers 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 CUsers_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 CUsers d
    INNER JOIN INSERTED i ON d.Id = i.Id
    WHERE i.Deleted = 1

    /* Delete master record */
    DELETE c
    FROM CUsers 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].[CUsers] ENABLE TRIGGER [trCUsers_AUDIT_UD]
GO

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

The 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 where updated by the trigger. The 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.

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
            {
                int id;
                CUser user;
                var connString =
                    ConfigurationManager.ConnectionStrings["DB1Entities"].ConnectionString;
                Console.WriteLine("Connection string={0}", connString);
                Console.WriteLine("Adding user");
                using (var context = new DB1Entities())
                {
                    context.Database.Log = Console.WriteLine;
                    var dateNow = DateTime.Now;
                    user = new CUser()
                    {
                        UserId = 1,
                        NTUser = "gmeyer",
                        FName = "George",
                        LName = "Meyer",
                        Active = true
                    };
                    context.CUsers.Add(user);
                    context.SaveChanges();
                    id = user.Id;
                    Console.WriteLine("user.Id={0}", user.Id);
                    WriteChangeTrackerCount(context);
                }
                using (var context = new DB1Entities())
                {
                    context.Database.Log = Console.WriteLine;
                    context.CUsers.Attach(user);
                    user.LName = "Meyers";
                    context.SaveChanges();
                    Console.WriteLine("user.TraceVersion={0}", user.TraceVersion);
                    WriteChangeTrackerCount(context);
                }
                using (var context = new DB1Entities())
                {
                    context.Database.Log = Console.WriteLine;
                    context.CUsers.Attach(user);
                    context.CUsers.Remove(user);
                    context.SaveChanges();
                    Console.WriteLine
                        ("context.Entry(user).State={0}", context.Entry(user).State);
                    WriteChangeTrackerCount(context);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            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 previously. The connection string should be modified accordingly in app.config. In the provided connection string, the database is called DB1. Running the project should create the following output:

Connection string=metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DB1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
Adding user
Opened connection at 9/11/2019 9:44:08 AM +03:00
Started transaction at 9/11/2019 9:44:08 AM +03:00
INSERT [dbo].[CUsers]([UserId], [Deleted], [TraceVersion], [UTimeStamp], [NTUser], [FName], [LName], [Active])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
SELECT [Id]
FROM [dbo].[CUsers]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: '1' (Type = Int32)
-- @1: 'False' (Type = Boolean)
-- @2: '1' (Type = Int32)
-- @3: '9/11/2019 9:44:08 AM' (Type = DateTime2)
-- @4: 'gmeyer' (Type = AnsiString, Size = 50)
-- @5: 'George' (Type = AnsiString, Size = 20)
-- @6: 'Meyer' (Type = AnsiString, Size = 50)
-- @7: 'True' (Type = Boolean)
-- Executing at 9/11/2019 9:44:09 AM +03:00
-- Completed in 45 ms with result: SqlDataReader

Committed transaction at 9/11/2019 9:44:09 AM +03:00
Closed connection at 9/11/2019 9:44:09 AM +03:00
user.Id=3
ChangeTracker.Entries().ToList().Count=1
Opened connection at 9/11/2019 9:44:09 AM +03:00
Started transaction at 9/11/2019 9:44:09 AM +03:00
UPDATE [dbo].[CUsers]
SET [LName] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))
-- @0: 'Meyers' (Type = AnsiString, Size = 50)
-- @1: '3' (Type = Int32)
-- @2: '1' (Type = Int32)
-- Executing at 9/11/2019 9:44:09 AM +03:00
-- Completed in 138 ms with result: 1

Committed transaction at 9/11/2019 9:44:09 AM +03:00
Closed connection at 9/11/2019 9:44:09 AM +03:00
Opened connection at 9/11/2019 9:44:09 AM +03:00
SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[UserId] AS [UserId],
    [Extent1].[Deleted] AS [Deleted],
    [Extent1].[TraceVersion] AS [TraceVersion],
    [Extent1].[UTimeStamp] AS [UTimeStamp],
    [Extent1].[NTUser] AS [NTUser],
    [Extent1].[FName] AS [FName],
    [Extent1].[LName] AS [LName],
    [Extent1].[Active] AS [Active]
    FROM [dbo].[CUsers] AS [Extent1]
    WHERE [Extent1].[Id] = 3
-- Executing at 9/11/2019 9:44:10 AM +03:00
-- Completed in 14 ms with result: SqlDataReader

Closed connection at 9/11/2019 9:44:10 AM +03:00
user.TraceVersion=2
ChangeTracker.Entries().ToList().Count=1
Opened connection at 9/11/2019 9:44:10 AM +03:00
Started transaction at 9/11/2019 9:44:10 AM +03:00
UPDATE [dbo].[CUsers]
SET [Deleted] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))
-- @0: 'True' (Type = Boolean)
-- @1: '3' (Type = Int32)
-- @2: '2' (Type = Int32)
-- Executing at 9/11/2019 9:44:10 AM +03:00
-- Completed in 15 ms with result: 1

Committed transaction at 9/11/2019 9:44:10 AM +03:00
Closed connection at 9/11/2019 9:44:10 AM +03:00
context.Entry(user).State=Detached
ChangeTracker.Entries().ToList().Count=0

IAudited

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

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

Model1.edmx

The entity framework model Model1.edmx was created with Add new item/ADO.NET Entity Data Model/EF Designer from database and selecting the database DB1 and the table CUsers. In the properties of the column TraceVersion the ConcurrencyMode was set to Fixed.

When there are many tables, it is easier to open Model1.edmx with XML (Text) Editor and search

<Property Name="TraceVersion" Type="Int32" Nullable="false" />

and replace with

<Property Name="TraceVersion" Type="Int32" Nullable="false" ConcurrencyMode="Fixed" />

Setting the ConcurrencyMode to Fixed has the effect, that all update statements have the TraceVersion added to the WHEREclause, for example:

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

Optimistic Concurrency is implemented this way.

From Model1.edmx, the template file Model1.Context.tt is generated, and from that, the C# code file Model1.Context.cs is generated. Also from Model1.edmx, the template file Model1.tt is generated, which is used to generate a C# code file for each entity, in this case CUser.cs. To make this model work with the given database, following modifications were necessary.

Model1.Context.tt

This template is used to create the Context class.

1. The following using statements were added:
   C#

   using System.Configuration;
   using System.Linq;

2. The entity framework gets the connection string from the app.config. The original constructor looked as follows:
   C#

   public <#=code.Escape(container)#>()
   : base("name=<#=container.Name#>")
   

   However, the connection string is often retrieved from some other module, it is perhaps encoded and must be decoded, therefore, the function GetConnectionString was created. This function can be used to retrieve the connection string. In this case, it also reads it from the app.config. The constructor calls the function GetConnectionString.
   C#

   private static string GetConnectionString() {
       return ConfigurationManager.ConnectionStrings["<#=code.Escape(container)#>"].ConnectionString;
   }
   public <#=code.Escape(container)#>()
       : base(GetConnectionString())
   

3. The method SaveChanges is overridden.
   C#

   public override int SaveChanges()
       {
           ...
           int rowCount = base.SaveChanges();
           ...
           return rowCount;
       }
   

4. The model should ensure that all new entities have Deleted=false, TraceVersion=1 and UTimestamp=current date and time. The following code does that:
   C#

   var entriesAdded =
       ChangeTracker.Entries().Where(
           e => e.State == EntityState.Added).ToList();
   foreach (var item in entriesAdded)
   {
       if (item.Entity is IAudited entity)
       {
           entity.Deleted = false;
           entity.TraceVersion = 1;
           entity.UTimeStamp = DateTime.Now;
       }
   }
   

5. The model should reload the fields TraceVersion and UTimeStamp of updated entities. Unfortunately, it is only possible to reload the entire entity. A future improvement should be to reload only the fields TraceVersion and UTimeStamp.
   C#

   var entriesModified =
       ChangeTracker.Entries().Where(
           e => e.State == EntityState.Modified).ToList();
   int rowCount = base.SaveChanges();
   if (rowCount > 0)
   {
       entriesModified.ForEach(e=>e.Reload());
   }
   

6. The model should change the deleted entities to updated entities with column Deleted set to 1. After saving, these entities should be detached. Credit goes to the article Entity Framework Core: Soft Delete which describes how to implement a soft delete. Here it is like a soft delete, but the record is moved from the main table to the audit table.
   C#

       		var entriesDeleted = 
       			ChangeTracker.Entries().Where(
       				e => e.State == EntityState.Deleted).ToList();
       		foreach (var item in entriesDeleted)
       		{
       			if (item.Entity is IAudited entity)
       			{
       				// Set the entity to unchanged 
                       // (if we mark the whole entity as Modified, 
                       // every field gets sent to Db as an update)
       				item.State = EntityState.Unchanged;
       				// Only update the Deleted flag - only this will get sent to the Db
       				entity.Deleted = true;
       			}
       		}
       		int rowCount = base.SaveChanges();
       		if (rowCount > 0)
       		{
       			foreach (var item in entriesDeleted)
       			{
       			    if (item.Entity is IAudited entity)
       			    {
       			        item.State = EntityState.Detached;
       			    }
       			}
       		}

Model1.tt

This template is used to create the entity classes, in this case CUser.cs. The only modification was to make each Entity class implement the interface IAudited. The original template for the class definition was:

public string EntityClassOpening(EntityType entity)
{
    return string.Format(
        CultureInfo.InvariantCulture,
        "{0} {1}partial class {2}{3}",
        Accessibility.ForType(entity),
        _code.SpaceAfter(_code.AbstractOption(entity)),
        _code.Escape(entity),
        _code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType)));
}

which produces the following Entity class:

public partial class CUser

The entity.BaseType seems to be always null, therefore the generated class does not inherit from some base type. The template was changed to:

public string EntityClassOpening(EntityType entity)
{
    return string.Format(
        CultureInfo.InvariantCulture,
        "{0} {1}partial class {2} : IAudited",
        Accessibility.ForType(entity),
        _code.SpaceAfter(_code.AbstractOption(entity)),
        _code.Escape(entity));
}

which produces the following Entity class:

public partial class CUser : IAudited

Summary

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

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

History

1. Model for table CUsers, update reloads the entire entity.
2. Optimistic concurrency with column TraceVersion.