Log Data Changes in Entity Framework Core – Part 3 - Ignore, Reference, SQL Server JSON and Testing

17th August 2020

We went ahead and built our entities, DbContext's and migrations. Then we went ahead and built services to integrate the ability to log data changes using an ASP.NET Core MVC API.

In this part, we will learn how to ignore properties we don't wish to log data changes to. In addition, we will also learn how to log data changes where a related entity, or reference is concerned. After that, we will write a SQL Server statement using it's JSON functionality that enables how to query and filter our data changes. Finally, we will look at XUnit and how we go about automated testing.

Ignoring Properties from Logging Data Changes

One of the problems we have at the moment is that we are logging data changes for all entities. There is no way of ignoring them. So, how do we go about doing that? Well the best way is to create an attribute that we can associate with these properties. Properties like the ID and last updated timestamp are logged elsewhere so we don't need to log these data changes.

So, we have created a [ChangeIgnore] attribute that can be associated with these properties:

// ChangeIgnore.cs
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public partial class ChangeIgnoreAttribute : Attribute
{
	protected string _schema;

	public ChangeIgnoreAttribute()
	{
	}
}

Then we can go ahead and apply it to the properties we don't wish to log data changes to. Here it is being applied in our Base class which our entities use to inherit:

// Base.cs
public abstract class Base : IBase
{
	[ChangeIgnore]
	public virtual int Id { get; set; }

	[ChangeIgnore]
	public virtual DateTimeOffset Created { get; set; }

	[ChangeIgnore]
	public virtual DateTimeOffset? LastUpdated { get; set; }

	[ChangeIgnore]
	public virtual DateTimeOffset? Deleted { get; set; }

	public static void OnModelCreating<TEntity>(ModelBuilder builder)
		where TEntity : class, IBase
	{
		builder.Entity<TEntity>().HasKey(entity => entity.Id);
	}

}

But, that alone won't ignore the properties. Inside our BaseChangeService class, we need to make a change to our PopulateChangeProperties so it ignores properties that have a [ChangeIgnore] attribute missing from them.

// BaseChangeService.cs
protected void PopulateChangeProperties([NotNull] ChangeData changeData, EntityState entityState, PropertyValues propertyValues, ChangePropertyTypeEnum changePropertyType)
{
	foreach (var property in propertyValues.Properties)
	{
		// These lines we have added to ignore properties with the [ChangeIgnore] attribute
		if (property.PropertyInfo.GetCustomAttributes(typeof(ChangeIgnoreAttribute), true).Any())
		{
			// Exclude any properties that contain the [ChangeIgnore] attribute
			continue;
		}

		var changePropertyName = property.Name;
		var changeProperty = changeData[changePropertyName]
		var changePropertyValue = propertyValues[property];

		if (changeProperty != null)
		{
			changeProperty.SetValue(changePropertyValue, changePropertyType);

			// Remove if the values remain the same.
			if (entityState == EntityState.Modified &amp;&amp; Equals(changeProperty.Current, changeProperty.Original))
			{
				changeData.ChangeProperties.Remove(changeProperty);
			}
		}
		else
		{
			changeData.AddChangeProperty(new ChangeProperty(changePropertyName, changePropertyValue, changePropertyType));
		}
	}
}

Including Reference Properties When Logging Data Changes

The nice thing about the EntityEntry class in Entity Framework is that it stores all the values for each property of that entity. However, what happens if you entity is related to another entity?

For this example, I've created a new entity called Engine. This will have a relationship with VideoGame where an Engine can have one, or many VideoGames. Vice-versa, a VideoGame can have zero or one Engine associated with it.

// Engine.cs
public partial class Engine : Base
{
	[ChangeReference]
	public virtual string Name { get; set; }
}

This relationship means we need to add a few new properties to our VideoGame entity.

// VideoGame.cs
[Table("VideoGame", Schema = "dbo")]
[ChangeTable("VideoGame-Change", 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; }

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

	public virtual int? EngineId { get; set; }

	[ChangeReference]
	public virtual Engine Engine { get; set; }

	public static void OnModelCreating(ModelBuilder builder)
	{
		builder.Entity<VideoGame>().HasOne(videoGame => videoGame.Engine)
			.WithMany()
			.HasPrincipalKey(engine => engine.Id)
			.HasForeignKey(videoGame => videoGame.EngineId);
	}
}

We have added an EngineId so we can form a relationship to the VideoGame and the Engine entity. In addition, we have also added an Engine property and this has our Engine entity as our type. As well, we have added a relationship between the two entities in the OnModelCreating method.

List of References

Now the nice thing about Entity Framework is you can get a list of References for an entry. The Engine property would be classed as a reference.

With the Reference class in Entity Framework, there is a method called Load. This means that if the reference hasn't been loaded, you can call that method to load it. And if it's already been loaded, it won't load it again.

This means we can create a new method in BaseChangeService class. This method will get all the values of the reference.

// BaseChangeService.cs
protected void GetChangeDataReference([NotNull] ChangeData changeData, EntityEntry entry, EntityState entityState, ChangePropertyTypeEnum changePropertyType, ref int depth, ref int count)
{
	var references = entry.References;
	depth += 1;
	count += 1;

	if (count > 100)
	{
		throw new Exception("GetChangeDataReference has been called more than 100 times, so there may be an issue with logging references")
	}

	if (references == null || depth > 2)
	{
		if (depth > 0)
		{
			depth -= 1;
		}
		return;
	}

	foreach (var entryReference in references)
	{
		if (!entryReference.Metadata.PropertyInfo.GetCustomAttributes(typeof(ChangeReferenceAttribute), true).Any())
		{
			continue;
		}

		entryReference.Load();

		if (entryReference.TargetEntry == null)
		{
			continue;
		}

		string referenceDisplayName = null;
		if (entryReference.Metadata.PropertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).Any())
		{
			referenceDisplayName = ((DisplayNameAttribute)entryReference.Metadata.PropertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).FirstOrDefault()).DisplayName;
		}

		PopulateChangeProperties(changeData, entityState, changePropertyType == ChangePropertyTypeEnum.Original ? entryReference.TargetEntry.OriginalValues : entryReference.TargetEntry.CurrentValues, changePropertyType, entryReference.Metadata.PropertyInfo.Name, referenceDisplayName);

		GetChangeDataReference(changeData, entryReference.TargetEntry, entityState, changePropertyType, ref depth, ref count);
	}

	if (depth > 0)
	{
		depth -= 1;
	}
}

It will loop through each reference and get the current values for it, in the same way we do for entities. And we can keep calling GetChangeDataReference so we can get references within that reference. This means that if we were to change the EngineId in VideoGame, not only would it capture the EngineId, but it can use the Engine entity to log the name.

Risks of Logging Data Changes for References

There are some risks associated with this. If you have a larger number of relationships between each entity, you could find that GetChangeDataReference is called multiple number of times. Multiple calls to GetChangeDataReference could slow your application down.

So to mitigate these risks, I've implemented a couple of checks:

  • Ensure that GetChangeDataReference only goes down to two levels
  • Ensure that GetChangeDataReference only gets called 100 times maximum

These are not the most important check. The most important check is to see if a reference has a [ChangeReference] attribute associated with it. In-order to log data changes for references you have to explicitly associated a reference with [ChangeReference]. [ChangeReference] is an attribute shown below:

// ChangeReferenceAttribute.cs
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class ChangeReferenceAttribute : Attribute
{
	public ChangeReferenceAttribute()
	{
	}
}

If you look at the GetChangeDataReference method shown above, if this attribute is not present, it will continue to the next one.

Logging References when Creating and Updating

Logging references when creating an entity is relatively easy. You just need to call the GetChangeDataReference in between adding the entity and saving the changes.

// BaseChangeService.cs
public override async Task<TEntity> CreateAsync(TEntity entity)
{
	var changeData = new ChangeData(EntityState.Added);
	await AddEntityToContextAsync(entity); // Add entity to DbContext

	var depth = 0;
	var count = 0;
	GetChangeData(changeData, _dataDbContext.Entry(entity), EntityState.Added);
	GetChangeDataReference(changeData, _dataDbContext.Entry(entity), EntityState.Added, ChangePropertyTypeEnum.Current, ref depth, ref count);

	await SaveChangesAsync(); // Save changes to DbContext
	await CreateChangeAsync(entity.Id, changeData);

	return entity;
}

It's when we update where it's get a little tricker. When we pass the entity into our UpdateAsync method, we need to make sure it's not being tracked in our DbContext.

How do we do that? By ensuring that when we get that entity, we apply the AsNoTracking method to it. Here's an example from our ReadAsync method from our BaseService.

public virtual async Task<TEntity> ReadAsync(int id, bool track = true)
{
	var query = _dataDbContext.Set<TEntity>().AsQueryable();

	if (!track)
	{
		query = query.AsNoTracking();
	}

	return await query.FirstOrDefaultAsync(entity => entity.Id == id &amp;&amp; !entity.Deleted.HasValue);
}

So in our ReadAsync method, if we provide the track parameter as false, any changes will not be tracked in the DbContext.

So why do we need to do this? It's for capturing references. In the VideoGame example, if we were to update the EngineId, the Engine reference updates as well. And this change is tracked in the DbContext, the reference would have been updated even before we called the UpdateAsync method. This means we wouldn't be able to get the original values from the Engine reference.

We need to call GetChangeDataReference method twice in our UpdateAsync method. Once before we set the values of the update, and once before we call the SaveChanges method.

// BaseChangeService.cs
public override async Task<TEntity> UpdateAsync(int id, TEntity updateEntity)
{
	// Check that the record exists.
	var entity = await ReadAsync(id);

	if (entity == null)
	{
		throw new Exception("Unable to find record with id '" + id + "'.");
	}

	var entityEntry = _dataDbContext.Entry(entity);

	// Store references before we update our entity.
	var depth = 0;
	var count = 0;
	var changeData = new ChangeData(EntityState.Modified);
	GetChangeDataReference(changeData, entityEntry, EntityState.Modified, ChangePropertyTypeEnum.Original, ref depth, ref count);

	// Update changes if any of the properties have been modified.
	_dataDbContext.Entry(entity).CurrentValues.SetValues(updateEntity);
	_dataDbContext.Entry(entity).State = EntityState.Modified;

	depth = 0;
	count = 0;
	GetChangeData(changeData, entityEntry, EntityState.Modified);
	GetChangeDataReference(changeData, entityEntry, EntityState.Modified, ChangePropertyTypeEnum.Current, ref depth, ref count);

	if (entityEntry.Properties.Any(property => property.IsModified))
	{
		await SaveChangesAsync();
		await CreateChangeAsync(entity.Id, changeData);
	}
	return entity;
}

Display Name

Currently, we are storing the property name when logging field changes. But, wouldn't it be nice if we could have a nicer title? Property names don't allow you to have spaces or special characters.

Now, we could go ahead and create a new attribute for this, but why would you? We already have the [DisplayName] attribute available to us, so why not use that?

So we did. Any properties that we are logging data changes to will have the display name captured if it exists. And it also works for reference as you can see here from the VideoGame entity.

// VideoGame.cs
[Table("VideoGame", Schema = "dbo")]
[ChangeTable("VideoGame-Change", 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 virtual int? EngineId { get; set; }

	[ChangeReference, DisplayName("Engine")]
	public virtual Engine Engine { get; set; }

	public static void OnModelCreating(ModelBuilder builder)
	{
		builder.Entity<VideoGame>().HasOne(videoGame => videoGame.Engine)
			.WithMany()
			.HasPrincipalKey(engine => engine.Id)
			.HasForeignKey(videoGame => videoGame.EngineId);
	}
}

Transforming JSON into a SQL Server Query

When we log a data change, we store it in a field that is formatted in JSON. So how we can translate that to a query in SQL Server? Fortunately, SQL Server has excellent support for JSON. We can go ahead and "CROSS APPLY" the ChangeData column with our table, using the OPENJSON function to translate the JSON keys into SQL Server columns.

SELECT Id AS ChangeId, ReferenceId, JSON_VALUE(change.ChangeData, '$.CUD') AS CUD, ChangeData.PropertyName, ChangeData.DisplayName, ChangeData.[Current], ChangeData.Original, ChangeData.Reference, ChangeData.ReferenceDisplayName, Created from [dbo].[VideoGame-Change] [change]
                CROSS APPLY OPENJSON([change].ChangeData, N'$.ChangeProperties')
                WITH(
                PropertyName NVARCHAR(100) '$.PropertyName',
                DisplayName NVARCHAR(100) '$.DisplayName',
                [Current] NVARCHAR(100) '$.Current',
                [Original] NVARCHAR(100) '$.Original',
                [Reference] NVARCHAR(100) '$.Reference',
                [ReferenceDisplayName] NVARCHAR(100) '$.ReferenceDisplayName'
                ) AS ChangeData

This will display a result similar to this:

Use OPENJSON and JSON_VALUE in SQL Server query to translate JSON into columns

This means you can now filter or order by any of the keys in your JSON.

Implementation in Entity Framework

How do you implement this in Entity Framework? Well, for our example, we are going to create a new class which mimics the properties with the column names in our SQL Server query.

// ChangeView.cs
public partial class ChangeView<TEntity>
            where TEntity : class, IBase, IChangeEntity<TEntity>, new()
{
	public virtual int ChangeId { get; set; }

	public virtual int ReferenceId { get; set; }

	public virtual string CUD { get; set; }

	public virtual string PropertyName { get; set; }

	public virtual string DisplayName { get; set; }

	public virtual string Current { get; set; }

	public virtual string Original { get; set; }

	public virtual string Reference { get; set; }

	public virtual string ReferenceDisplayName { get; set; }


	public virtual DateTimeOffset Created { get; set; }

	public static void OnModelCreating(ModelBuilder builder)
	{
		builder.Entity<ChangeView<TEntity>>().HasNoKey();
	}
}

You can see we are passing in a generic type of TEntity. This will be used for Entity Framework so it knows which table it's querying. We also have a static OnModelCreating method, that dictates that it doesn't have a key.

We can add a couple of lines when overriding the OnModelCreating method in our ChangeDbContext. By getting the ChangeView type, we can append the relevant entity as the generic type. Then we can invoke the OnModelCreating method.

// ChangeDbContext.cs
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 &amp;&amp; _interface.GetGenericTypeDefinition().Equals(typeof(IChangeEntity<>))) &amp;&amp; s.IsClass &amp;&amp; !s.IsAbstract &amp;&amp; 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);

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

			// Looks for static 'OnModelCreating' method in the ChangeView class
			CustomOnModelCreating(builder, changeViewGenericClass);
		}
	}
}

Now, we can go ahead and create a ReadViewAsync method in our BaseChangeService class. This will call the FromSqlRaw method from ChangeDbContext where we can pass in our SQL query. It will then serialise it into our ChangeView class.

// BaseChangeService.cs
public virtual async Task<IList<ChangeView<TEntity>>> ReadViewAsync(int referenceId)
        {
            // All change tables MUST have a table name set in the change database
            var attribute = (ChangeTableAttribute)typeof(TEntity).GetCustomAttributes(typeof(ChangeTableAttribute)).FirstOrDefault();

            if (attribute == null)
            {
                // Throw an error if no change table is specified
                throw new Exception("To read change records from " + typeof(TEntity).Name + ", you must include the [ChangeTable] attribute in your class, specifying which table you wish to write your change information to");
            }

            return await _changeDbContext.Set<ChangeView<TEntity>>().FromSqlRaw(
                $"SELECT Id AS ChangeId, ReferenceId, JSON_VALUE(change.ChangeData, '$.CUD') AS CUD, ChangeData.PropertyName, ChangeData.DisplayName, ChangeData.[Current], ChangeData.Original, ChangeData.Reference, ChangeData.ReferenceDisplayName, Created from [" + attribute.Schema + "].[" + attribute.Name + "] [change]" +
                " CROSS APPLY OPENJSON([change].ChangeData, N'$.ChangeProperties')" +
                " WITH(" +
                "PropertyName NVARCHAR(100) '$.PropertyName'," +
                "DisplayName NVARCHAR(100) '$.DisplayName'," +
                "[Current] NVARCHAR(100) '$.Current'," +
                "[Original] NVARCHAR(100) '$.Original'," +
                "[Reference] NVARCHAR(100) '$.Reference'," +
                "[ReferenceDisplayName] NVARCHAR(100) '$.ReferenceDisplayName'" +
                ") AS ChangeData"
                ).Where(changeView => changeView.ReferenceId == referenceId).ToListAsync();
        }

A Word on Testing

If you work for a large technology company, automated testing is probably second nature. But, for smaller companies that have time restrictions and where the customer doesn't see the testing, it might be put to one side.

This is a good example of why automated testing is important. If you fail to log data changes, or log incorrect information, the customer will notice and not be very happy. This would be a good argument to spend a little bit more time on testing, even if it was to delay a release date.

With XUnit, or any other test-driven development package out there, you can create a host and a scope in the same way you would do for a ASP.NET Core application. This means you can use a StartUp class and use dependency injection to test your services.

// BaseTest.cs
protected BaseTest()
{
	string[] args = null;

	// Creates the application
	_builder = Host.CreateDefaultBuilder(args)
		.UseContentRoot(Directory.GetCurrentDirectory())
		.ConfigureWebHostDefaults(webBuilder =>
		{
			webBuilder.UseStartup<TestStartup>();
		}).Build();

	_scope = _builder.Services.CreateScope();

	_testVideoGameService = (ITestVideoGameService)_scope.ServiceProvider.GetRequiredService(typeof(ITestVideoGameService));
	_testDataDbContext = (TestDataDbContext)_scope.ServiceProvider.GetRequiredService(typeof(TestDataDbContext));
	_testChangeDbContext = (TestChangeDbContext)_scope.ServiceProvider.GetRequiredService(typeof(TestChangeDbContext));
}

You can see I've created some test DbContext's to use in testing. These use Entity Framework's in-memory feature, so it doesn't communicate with the database.

We also created a TestVideoGameService class, and this inherits the BaseChangeService, passing in our test DbContext's and a TestVideoGame entity as it's generic types. This mean it keeps the DbContext's and entities away from the main application.

// TestVideoGameService.cs
public partial class TestVideoGameService : BaseChangeService<TestDataDbContext, TestChangeDbContext, TestVideoGame>, ITestVideoGameService
{
	public TestVideoGameService(TestDataDbContext mainDbContext, TestChangeDbContext changeDbContext) : base(mainDbContext, changeDbContext) { }
}

Integration of Ignore, Reference Properties

You can see the integration of logging data changes, ensuring that properties that are marked as [ChangeIgnore] do not get logged. In addition, you can see the ability of logging a data change which includes a reference. Then, we will examine how un-commenting one line of code can make our test success go from 100% to 30%!

Any Questions?

You should be able to use this code if you are using Entity Framework Core. If you have any questions, please let me know.

Want More ASP.NET Core Coding Tutorials?

Subscribe to my YouTube channel to get more ASP.NET Core coding tutorials.

You'll get videos where I share my screen and implement a how-to guide on a topic related to ASP.NET Core.

You can expect to see videos from the following technologies:

  • Blazor
  • Web APIs
  • SQL Server
  • Entity Framework
  • SignalR
  • and many more...

By subscribing, you can get access to all my ASP.NET Core coding tutorials completely free!

And so you never miss out on a new video, you have the option to be notified every time a new video is published.

So what are you waiting for?

To subscribe, click here to view my YouTube channel, and click on the red "Subscribe" button.

File Downloads

David Grace

David Grace

I am a .NET developer, building web applications in .NET Framework and .NET Core with a SQL Server database.

Some of the .NET packages I have used include Entity Framework and MVC.

I've also used many JavaScript frameworks such as React and jQuery, and have experience building CSS in SASS.

Twitter Feed