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 to work with that using EntityFramework Core
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 making a rant.
Ok, well. First of all, take a look to JSON that we want to persist into a 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 useful 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! 🐱👤