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

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

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

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.

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!🐱‍👤