Simplifying Entity Framework Core Usage of JSON ColumnType for Dynamic Data

31.05.2020 — Code, C#, EF — 1 min read

With the advent of JSON ColumnType support in SQL Server since Microsoft SQL Server 2016, managing dynamic data within tables has become more flexible and efficient. When dealing with scenarios where additional properties need to be stored beyond the defined columns, utilizing JSON becomes a compelling solution.

Consider a situation where a table requires the storage of extra properties that may vary from one entry to another. Instead of creating separate fields for each potential property or resorting to XML storage, leveraging JSON offers a more streamlined approach.

In the model class, define a property to hold the dynamic properties using a dictionary:

public Dictionary<string, string> Properties { get; set; } = new Dictionary<string, string>();

To configure the data storage format in the database context, utilize Entity Framework Core's fluent API within the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var valueComparer = new ValueComparer<Dictionary<string, string>>(
        (c1, c2) => c1.SequenceEqual(c2),
        c => c.GetHashCode(),
        c => c.ToDictionary(entry => entry.Key, entry => entry.Value));

    modelBuilder.Entity<ModelClassName>()
        .Property(b => b.Properties)
        .HasConversion(
            v => JsonConvert.SerializeObject(v),
            v => JsonConvert.DeserializeObject<Dictionary<string, string>>(v))
        .Metadata.SetValueComparer(valueComparer);
}

However, while implementing this, you may encounter an error indicating the absence of a value comparer for the collection or enumeration type property. To resolve this, define a value comparer to ensure correct comparison of collection/enumeration elements.

var valueComparer = new ValueComparer<Dictionary<string, string>>(
    (c1, c2) => c1.SequenceEqual(c2),
    c => c.GetHashCode(),
    c => c.ToDictionary(entry => entry.Key, entry => entry.Value));

By incorporating this value comparer, you ensure that Entity Framework Core can correctly handle comparisons of the dynamic property dictionaries.

In summary, by harnessing the JSON ColumnType along with appropriate configuration in Entity Framework Core, you can effectively manage dynamic data within your SQL Server tables, enhancing flexibility and simplifying data storage.