Adding Metadata to EF Core Table

Has this scenario ever happened to you. You have a well established database / Entity Framework (EF) environment. Your customer decides he wants to track an additional piece of data associated with lets say an order. Once implemented and published to production the customer again wants a different piece of data associated with the order. You have to go thru all the effort to push an addition migration to the database thru your DBA. Eventually the DBA gets annoyed. So what is the solution.

First option that comes to mind keep adding columns to the order table to hold the changes. The upside each column can specify the datatype it holds. The bad news is this data maybe sparsely populated and we will keep adding columns with each and every change we need.

Second option would be to add a table to hold this let’s call it metadata. This new table would need a link back to the order table, a key that would be the name of the metadata, and a value. But the value, what datatype would we make that column. It may be a number for one type of metadata but a date for another, and a GUID for yet another. Hmmm.

So what about adding a Dictionary of objects with a key of type string. The string would be the name of the metadata and the object would be the value. All we have to do is add the dictionary to the order entity. Wow that was so simple.

All that is left is to add a migration to EF. And here is the problem. EF wont support the use of a dictionary.

We can serialize/de-serialize the dictionary using a backing field but that code would pollute our order entity with a storage issue.

So we need a solution maintained in the EF Core specific code, namely the DBContext code.

First piece of the pie – ValueConverter

We add to the OnModelCreating

modelBuilder.Entity<Order>(entity =>
{
    entity.Property(e => e.MetaData)
        .HasConversion(
            v => JsonConvert.SerializeObject(v),
            v => JsonConvert.DeserializeObject<Dictionary<string, object>>(v) ?? new Dictionary<string, object>()
        );
});

but when the MetaData dictionary is modified SaveChanges does not save anything. The MetaData property is not marked as update. Hmmm now what.

Second piece of the pie – ValueComparer

The ValueComparer is used by the change tracker to determine if the property is dirty.

ValueComparer<T> comparer = new ValueComparer<T>
(
	(l, r) => JsonConvert.SerializeObject(l) == JsonConvert.SerializeObject(r),
	v => v == null ? 0 : JsonConvert.SerializeObject(v).GetHashCode(),
	v => JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(v))
);

Putting it all together

What we need is an extension method so we can use it over and over anywhere we need it.

public static PropertyBuilder<T> HasJsonConversion<T>(this PropertyBuilder<T> propertyBuilder) where T : class, new()
{
	ValueConverter<T, string> converter = new ValueConverter<T, string>
	(
		v => JsonConvert.SerializeObject(v),
		v => JsonConvert.DeserializeObject<T>(v) ?? new T()
	);

	ValueComparer<T> comparer = new ValueComparer<T>
	(
		(l, r) => JsonConvert.SerializeObject(l) == JsonConvert.SerializeObject(r),
		v => v == null ? 0 : JsonConvert.SerializeObject(v).GetHashCode(),
		v => JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(v))
	);

	propertyBuilder.HasConversion(converter);
	propertyBuilder.Metadata.SetValueConverter(converter);
	propertyBuilder.Metadata.SetValueComparer(comparer);
	propertyBuilder.HasColumnType("jsonb");

	return propertyBuilder;
}

We utilize the extension method in the OnModelCreate

modelBuilder.Entity<Order>(entity =>
{
    entity.Property(e => e.MetaData)
        .HasJsonConversion<Dictionary<string, object>>();
});

This same technique can be used to serialize the data using XML or encrypt / decrypt data into the database.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.