Log Data Changes in Entity Framework Core - Part 1 - Create Entities & DbContext's

Then we have to consider database and table size. When logging data changes, we want a table that doesn't have too many columns. However, we also want to keep the number of record entries down to a minimum. So, creating a new record for every column change is not going to be practical.

The Solution

We will have a "data" database and this will store all our records. This will integrate with Entity Framework in the same way that most demos will show you how to integrate it.

In addition, we will create a new database to log all our data changes from our "data" database. That's call it our "change" database. Now, for each entity that we wish to log data changes to, it will have a separate table in the "change" database.

So for example, if we are storing records for video games, there will be a VideoGame table in the "data" database, and there will be VideoGame table in the "change" database.

However, the table structure to VideoGame will be different in the "change" database. In the "data" database, the table structure will mimic your VideoGame class in your .NET Core application. But in the "change" database, it will have the following columns:

• Id - Unique ID
• ReferenceId - Refers to the ID in the "data" database that has been changed.
• ChangeData - A JSON formatted value which will contain details such as whether the record was created, updated or deleted, and any properties that have changed
• Created - Timestamp of when the change was logged

In-fact, any entity that we wish to log data changes will have the same columns in the "change" database. But each entity will have it's own table in the "change" database.

The latest versions of SQL Server have great support for JSON formatted values. This means that we will be able to store the changes in JSON and be able to query that data when needed.

How it Works in Entity Framework

We will set up two DbContext's to make this work:

• DataDbContext - Integrates with the "data" database
• ChangeDbContext - Integrates with the "change" database

Now, that take a look at the code for our VideoGame entity:

// VideoGame.cs
[Table("VideoGame", Schema = "dbo")]
[ChangeTable("VideoGame", Schema = "dbo")]
public partial class VideoGame : Base, IChangeEntity<VideoGame>
{
	[MaxLength(100)]
	public virtual string Title { get; set; }

	[MaxLength(100)]
	public virtual string Publisher { get; set; }

	[DisplayName("Release Date"), Column(TypeName = "date")]
	public virtual DateTime? ReleaseDate { get; set; }

	public static void OnModelCreating(ModelBuilder builder)
	{
	}
}

There's a couple of things to note from this:

• We are using the [Table] attribute. This is an attribute from Entity Framework which tells which table in the "data" database to integrate with
• There is a [ChangeTable] attribute. This is an attribute we have created for this application. It tells EF which table in the "change" database to integrate with
• It inherits the Base class. The Base class gives us properties to use on all entities, such as the id and created timestamp
• It inherits the IChangeEntity interface. We will use this interface to tell Entity Framework that we wish to log data changes against this entity

BaseChange and How It Integrates With ChangeDbContext

If you have used Entity Framework before, the way the DataDbContext works should be familiar to how you integrate Entity Framework. So we are going to concentrate on the ChangeDbContext.

Now, we've set up a BaseChange entity that looks like the following

// BaseChange.cs
public partial class BaseChange<TChangeEntity> : IBaseChange<TChangeEntity>
        where TChangeEntity : class, IChangeEntity<TChangeEntity>, new()
{
	public virtual int Id { get; set; }

	public virtual int ReferenceId { get; set; }

	public virtual ChangeData ChangeData { get; set; }

	public virtual DateTimeOffset Created { get; set; }

	public BaseChange(int referenceId, ChangeData changeData)
	{
		ReferenceId = referenceId;
		ChangeData = changeData;
		Created = DateTime.Now;
	}

	public static void OnModelCreating(ModelBuilder builder)
	{
		// Makes the ChangeData unicode
		builder.Entity<BaseChange<TChangeEntity>>().Property(m => m.ChangeData).HasConversion(ChangeJsonValueConverter());
	}

	protected static ValueConverter<ChangeData, string> ChangeJsonValueConverter()
	{
		return new ValueConverter<ChangeData, string>(
			changeData => JsonConvert.SerializeObject(changeData, Formatting.None, new JsonSerializerSettings()
			{
				NullValueHandling = NullValueHandling.Ignore
			}),
			text => JsonConvert.DeserializeObject<ChangeData>(text)
		);
	}
}

Now, something to note is that this class passes in a generic type called TChangeEntity. The TChangeEntity is of type IChangeEntity, which has been integrated with our VideoGame entity.

In addition, we have created a static OnModelCreating method. This will be used in our ChangeDbContext.

With the ChangeData property, we are using a ChangeData type. But how will SQL Server know what a ChangeData type is?

Well it can do so by using EF Core's ValueConverter class. When communicating with the database, it will convert the ChangeData type to a JSON formatted string. Going the other way, it will deserialise the JSON object into a ChangeData type.

The ChangeDbContext

If we now have a look at the code for ChangeDbContext:

// ChangeDbContext.cs
public class ChangeDbContext : BaseDbContext
{
	// Constructers inherited from BaseDbContext
	public ChangeDbContext() : base() { }
	public ChangeDbContext([NotNull] IConfiguration configuration) : base(configuration) { }

	protected virtual IList<Assembly> Assemblies
	{
		get
		{
			return new List<Assembly>()
			{
				{
					Assembly.Load("RoundTheCode.LogDataChange.Data")
				}
			};
		}
	}

	protected override void OnModelCreating(ModelBuilder builder)
	{
		foreach (var assembly in Assemblies)
		{
			// Gets all the classes that inherit IChangeEntity<>
			var changeEntityClasses = assembly.GetTypes().Where(s => s.GetInterfaces().Any(_interface => _interface.IsGenericType && _interface.GetGenericTypeDefinition().Equals(typeof(IChangeEntity<>))) && s.IsClass && !s.IsAbstract && s.IsPublic);

			foreach (var changeEntityClass in changeEntityClasses)
			{
				// Creates a new instance of BaseChange, passing in the change entity class as the generic type
				var baseChangeGenericClass = typeof(BaseChange<>).MakeGenericType(changeEntityClass);

				// Uses the [ChangeTable] attribute to bind a table to the change database
				SetChangeTable(builder, changeEntityClass, baseChangeGenericClass);

				// Looks for a static 'OnModelCreating' method in the BaseChange class
				CustomOnModelCreating(builder, baseChangeGenericClass);
			}
		}
	}

	protected void SetChangeTable(ModelBuilder builder, Type changeEntityClass, Type baseChangeGenericClass)
	{
		if (Database.IsInMemory())
		{
			return;
		}

		// All change tables MUST have a table name set in the change database
		var attribute = (ChangeTableAttribute)changeEntityClass.GetCustomAttributes(typeof(ChangeTableAttribute)).FirstOrDefault();

		if (attribute == null)
		{
			// Throw an error if no change table is specified
			throw new Exception("As you are including changes in " + changeEntityClass.Name + ", you must include the [ChangeTable] attribute in your class, specifying which table you wish to write your change information to");
		}

		// Set the table and schema name to the change database.
		builder.Entity(baseChangeGenericClass).ToTable(attribute.Name, attribute.Schema);
	}

	protected void CustomOnModelCreating(ModelBuilder builder, Type baseChangeGenericClass)
	{
		// Get the static OnModelCreating from BaseChange
		var onModelCreatingMethod = baseChangeGenericClass.GetMethods().FirstOrDefault(x => x.Name == "OnModelCreating" && x.IsStatic);

		if (onModelCreatingMethod != null)
		{
			// Runs BaseChange.OnModelCreating static method
			onModelCreatingMethod.Invoke(baseChangeGenericClass, new object[] { builder });
		}

	}

	protected override string ConnectionStringLocation { get => "ConnectionStrings:ChangeDbContext"; }

	public async override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
	{
		foreach (var entry in ChangeTracker.Entries())
		{
			if (entry.State == EntityState.Added)
			{
				entry.Property("Created").CurrentValue = DateTimeOffset.Now;
			}
		}

		return await base.SaveChangesAsync(cancellationToken);
	}
}

Now, I want to concentrate on the code in the OnModelCreating method that we are overriding from DbContext.

To get all the entities for this DbContext, it uses reflection to get all the class types that inherit IChangeEntity. Remember that the VideoGame entity inherits IChangeEntity.

// ChangeDbContext.cs
var changeEntityClasses = assembly.GetTypes().Where(s => s.GetInterfaces().Any(_interface => _interface.IsGenericType && _interface.GetGenericTypeDefinition().Equals(typeof(IChangeEntity<>))) && s.IsClass && !s.IsAbstract && s.IsPublic);

It then goes through and creates a BaseChange type, passing in the type that inherits IChangeEntity.

// ChangeDbContext.cs
// Creates a new instance of BaseChange, passing in the change entity class as the generic type
var baseChangeGenericClass = typeof(BaseChange<>).MakeGenericType(changeEntityClass);

From there, it looks to get the table name for the "change" database. It looks to see if a [ChangeTable] attribute is apparent. Remember that we added the [ChangeTable] to our VideoGame entity? This is where it comes to use.

Lastly, it will invoke the static OnModelCreating method in BaseChange. This is how it knows how to convert the ChangeData type to a JSON string and vice-versa when communicating with SQL Server.

It works with EF Migrations

The nice thing about this is the ChangeDbContext will work with Entity Framework Migrations.

Now, you will need to set up an application that integrates your DbContext's, and ensure that you have a connection string set up. But once you've done that, you can create a migration with the following command in Package Manager Console in Visual Studio:

Add-Migration Init -context ChangeDbContext

This will create the migration script for your "change" database. To integrate it with SQL Server, you can run the following:

Update-Database -context ChangeDbContext

You can watch the migrations taking place and the SQL Server database being created.

Next Time...

That's our integration with Entity Framework and SQL Server set up. Next time, we will create functionality for integration with an API. We will set up an ASP.NET Core MVC API and use dependency injection to inject these services. This will allow us to test the ability to log data changes.