SqlServer, EFCore, JSON 👀

Sometimes we have been forced to work with json stored on table columns, it will have happened to you too!.

In this post I want to show you how work with that using EntityFramework Core

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.

Clearly this is one of many possible ways.

We could talk for a long time about the choice to store JSON into RDBMS is a good or bad choice, but the intent of this post isn't make rant.

Ok, well. First of all take a look to JSON that we want to persist into table column

	{
	"Color": "ff48cca1",
	"Weight": 1859.7198779041505,
	"Width": 0,
	"Length": 0,
	"Height": 0,
	"Retailer": {
	"Name": "Kirk Grady"
	}
	}

view raw metadata.json hosted with ❤ by GitHub

Our application has a requirement that makes it necessary to query the database with the name of the retailer that has stored in the JSON.

The retailer is the one who has the car we want to rent.
The car is represented by the class

	// See the LICENSE.TXT file in the project root for full license information.
	using System;
	using Sample.Dtos;
	namespace Sample.Entities
	{
	public class Car : IEntity<Guid>
	{
	public Car(string name, Metadata metadata)
	: this()
	{
	if (string.IsNullOrWhiteSpace(name))
	{
	throw new ArgumentException($"'{nameof(name)}' cannot be null or whitespace", nameof(name));
	}
	this.Name = name;
	this.Metadata = metadata ?? throw new ArgumentNullException(nameof(metadata));
	this.Id = Guid.NewGuid();
	}
	private Car()
	{
	}
	public string NameOfRetailer { get; }
	public Guid Id { get; }
	public string Name { get; }
	public Metadata Metadata { get; private set; }
	public void AddMetadata(Metadata metadata)
	{
	this.Metadata = metadata;
	}
	}
	}

view raw Car.cs hosted with ❤ by GitHub

The Car entity has a property public string NameOfRetailer { get; } that is computed by the Computed columns functionality.

	modelBuilder.Entity<Car>()
	.Property(car => car.NameOfRetailer)
	.HasComputedColumnSql("JSON_VALUE(Metadata, '$.Retailer.Name')");

view raw Car.NameOfRetailer.cs hosted with ❤ by GitHub

With this instruction efcore will inflate property with value returned by JSON_VALUE(Metadata, '$.Retailer.Name') expression, for more information about JSON_VALUE see at JSON_VALUE

To make it work, we need to persist the JSON into table column Metadata.

We can use the other usefull Value conversions functionality of efcore.

	modelBuilder.Entity<Car>()
	.Property(car => car.Metadata)
	.HasConversion(new JsonValueConverter<Metadata>());

view raw Car.Metadata.cs hosted with ❤ by GitHub

	// See the LICENSE.TXT file in the project root for full license information.
	using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
	using Newtonsoft.Json;
	namespace Sample.Infrastructure.Storage
	{
	internal class JsonValueConverter<T> : ValueConverter<T, string>
	{
	private static readonly JsonSerializerSettings SerializerOptions = new JsonSerializerSettings
	{
	ConstructorHandling = ConstructorHandling.AllowNonPublicDefaultConstructor
	};
	public JsonValueConverter(JsonSerializerSettings serializerOptions = null, ConverterMappingHints mappingHints = null)
	: base(value => JsonConvert.SerializeObject(value, serializerOptions ?? SerializerOptions), value => JsonConvert.DeserializeObject<T>(value, serializerOptions ?? SerializerOptions), mappingHints)
	{
	}
	}
	}

view raw JsonValueConverter.cs hosted with ❤ by GitHub

Now, after that model configurations we are able to resolve this simple query var car = await context.Cars.MaterializeAsync(car => car.NameOfRetailer == "Car Auto Orvieto").ConfigureAwait(false); without materialize the entire dataset on the client. 🚀

If you want to learn more you can find the sample on my github repo ef-core-json

Happy coding!🐱‍👤