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 && 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 && !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:
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 && _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);
// 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.
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.
About the author
David Grace
Senior .NET web developer | ASP.NET Core | C# | Software developer
