How to Track Changes with Entity Framework

What is Change Tracking?

Database change tracking is a mechanism for recording changes made to the contents of a database. Change tracking makes it possible for users to keep track of what changes have been made, by whom, when, and why.

Note that change-tracking is not the same as change-auditing. While auditing records user actions after the fact, tracking attempts to record changes as they are being made.

Any transaction in a database is a change. In designing the tracking mechanisms, the types of changes need to considered. These types are often referred to as inserts, updates and deletes (CRUD operations).

Benefits of Change Tracking

Security Investigations

Change tracking can help identify how security vulnerabilities have been exploited. For example, which user was impersonated during an attack.

Audit Trail Creation

When change tracking is implemented, an organisation can access all modifications to records.

This means that an audit trail can be created. Hence ensuring any entry made to a system is recorded so that it can be easily tracked, located or verified.

Analysis of Data

Many companies gather data about how users interact with their products. This can then analysed to suggest ways to improve their products and thus create better experiences for their users.

The data might be gathered from user actions, such as mouse clicks and taps on mobile devices,

Legal Disputes

If an employee or organisation is sued, audited, or investigated for something, change tracking can be essential.

This is because they may need to find out the details of interactions with the database, such as, who accessed that data, and when.

Solutions Available

CRUD operations to a database can be tracked with Entity Framework in multiple ways.

One approach is to override the db.SaveChanges event with custom logic.

However, handling the retrieval of properties such can be challenging. For example, when checking for pluralised names or if table names do not match primary keys.

If you are interested in learning how to do this manually, articles to read include Stack Overflow and C-Sharp-Corner.com

Alternatively and more easily, pre-built plugins such as Audit.NET or Tracker Enabled DB Context can be used. 

Both of which are listed on entityframework.net as recommended third-party plugins.

For this tutorial, we will look at Tracker Enabled DB Context.

How to Setup Change Tracking for a .NET Framework Application

It is very straightforward to set up change tracking with Tracker Enabled DB Context and does not involve any difficult coding.

Firstly, the plugin must be installed and the DbContext inheritance must be adapted.

If you are intending to use the library with Identity Framework, run the following command in the NPM Console

Install-Package TrackerEnabledDbContext.Identity

and inherit the Db Context containing the entities that need to tracked, from TrackerIdentityContext with the User Entity as a constructor (default user class is ApplicationUser)

DbContext : TrackerIdentityContext<ApplicationUser>

Or for non identity projects, install the package

Install-Package TrackerEnabledDbContext

And adjust the DbContext inheritance as#

DbContext : TrackerIdentityContext<ApplicationUser>

Next, the audit tracking tables need to be created.

This can be done by either copying and executing this code as an SQL query.

Or via code-first migrations with the following code. Whereby ApplicationContextName is the full context name including the namespace (only necessary if there is more than one context in the project).

Enable-Migrations -ContextTypeName ApplicationContextName
Add-Migration AuditTrail -Context ApplicationContextName
Update-Database

However, if using code first migrations, it must be considered that the migration created may overwrite existing table structure/data. The options for this scenario can be read here.

Additionally, as of the date of this post, there is a bug with Visual Studio 2022 that stops code-first migrations from working. See updates via this link.

The tables/entities that need to be tracked need to then be specified with either a [TrackChanges] or [SkipChanges] data annotation above the respective table/entity or field/property.

For example, the following code will track changes on the entire table but skip the RecordId field.

[TrackChanges]

public class Records
{

    [SkipChanges]
    public int RecordId { get; set; }
}

Whereas, the following code will only track changes on the RecordType field.

public class Records
{
    public int RecordId { get; set; }

    [TrackChanges]
    public int RecordType { get; set; }
}

Icons made by Freepik from www.flaticon.com