Add MS SQL Server JSON Support To Entity Framework Core | Khalid Abuhakmeh_Code (2023)

SELECT FirstName, LastName, JSON_VALUE(jsonInfo, '$.info.address.town') AS TownFROM Person.PersonWHERE JSON_VALUE(jsonInfo, '$.info.address.state') LIKE 'US%'ORDER BY JSON_VALUE(jsonInfo, '$.info.address.town')
Note that Town is a string value, not a complex object. If you need complex objects or to access arrays, you’ll needto use JSON_QUERY.
JSON_QUERY allows you to return a complex JSON structure so your consuming client can process that complexity,typically in the form of deserialization. In our previous JSON sample, you’ll note that tags is an array. Let’s seewhat the results of querying that value would look like in our results.
DECLARE @jsonInfo NVARCHAR(MAX)SET @jsonInfo=N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" "tags":["Sport", "Water polo"] "type":"Basic" Select JSON_QUERY(@jsonInfo, '$.info.tags') as Tags
The result of our query is a JSON Array.
["Sport", "Water polo"]
You typically won’t see JSON_QUERY in your Where clauses unless it’s matched with other functions to get to aprecise set of values.
Select JSON_QUERY(@jsonInfo, '$.info.tags')Where JSON_QUERY(@jsonInfo, '$.info.tags') like '%Sport%'
Now that you know how JSON_VALUE and JSON_QUERY work, how do we get access to this MSSQL from Entity Framework Core?
Entity Framework Core Configuration With JSON Columns
I’ve previously written about using DbFunctions with Entity Framework Core, so I suggest reading that at some point,but for brevity, I’ll show you want my OnModelCreating method looks like and what additional static methods you’llneed.
In your DbContext implementation, Add the following lines of code, or modify your OnModelCreating method to includethe additional configuration lines.
protected override void OnModelCreating(ModelBuilder modelBuilder) modelBuilder.HasDbFunction( typeof(Database).GetMethod(nameof(JsonValue))! ).HasName("JSON_VALUE").IsBuiltIn(); modelBuilder.HasDbFunction( typeof(Database).GetMethod(nameof(JsonQuery))! ).HasName("JSON_QUERY").IsBuiltIn();public static string JsonValue(string column, [NotParameterized] string path) => throw new NotSupportedException();public static string JsonQuery(string column, [NotParameterized] string path) => throw new NotSupportedException();
You must use the IsBuiltIn method, as you tell EF Core that this database function is not part of any existing schemalike dbo. The static methods will allow you to use C# methods as markers for EF Core’s translation process. We don’tneed any implementation in our methods, but you could certainly add validation logic that runs before the translationprocess occurs. I don’t see the need to do so, but you are welcome to modify it yourself.
Let’s see what writing a LINQ query is like using the JSON_VALUE method. But, first, let’s get an idea of our datamodels used in the query.
public class Product public int Id { get; set; } // serialized ProductInfo public string Json { get; set; } = "{ }";public class ProductInfo public string Name { get; set; } = ""; public decimal Price { get; set; }
Now, what’s a LINQ Query going to look like in your codebase?
using Microsoft.EntityFrameworkCore;// project namespaceusing SqlServerJson;using static SqlServerJson.Database;using PI = SqlServerJson.ProductInfo;var database = new Database().TrySeed();var expensive = database.Products .Select(p => new { p.Id, Name = JsonValue(p.Json, $"$.{nameof(PI.Name)}"), Price = Convert.ToDecimal(JsonValue(p.Json, $"$.{nameof(PI.Price)}")) .Where(x => x.Price > 800) .OrderByDescending(x => x.Price) .Take(10);Console.WriteLine(expensive.ToQueryString() + "\n");foreach (var product in expensive) Console.WriteLine($"{product.Name} ({product.Price:C})");
Note that the JsonValue method takes the string property on our model, and not a string of the column name. The EF Core translator will use the property name when converting LINQ to our SQL Query. An essential part of the query is the usage of Convert. EF Core’s query syntax can translate the Convert call intothe appropriate SQL cast.
You’ll see the SQL query and the results running the sample. **Your results will depend on the data in your databaseinstance.
DECLARE @__p_0 int = 10;SELECT TOP(@__p_0) [p].[Id], JSON_VALUE([p].[Json], N'$.Name') AS [Name], CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) AS [Price]FROM [Products] AS [p]WHERE CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) > 800.0ORDER BY CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) DESCSleek Steel Table ($999.74)Ergonomic Fresh Cheese ($999.73)Awesome Frozen Gloves ($999.62)Incredible Concrete Mouse ($998.12)Intelligent Plastic Computer ($997.78)Generic Steel Car ($996.51)Intelligent Fresh Keyboard ($995.12)Sleek Granite Hat ($994.15)Rustic Rubber Ball ($992.98)Generic Soft Cheese ($992.04)
As you’ll notice, the SQL query is quite readable, and you can convert a JSON value into any database type for use infiltering and ordering your results. Awesome!
I decided to use a projection, but if you’d prefer to pull back the complete JSON object as part of your model, you canuse the following approach.
public class Product public int Id { get; set; } // serialized ProductInfo public string Json { get; set; } = "{ }"; [NotMapped] public ProductInfo? Info get => JsonSerializer.Deserialize<ProductInfo>(Json); set => Json = value is {} ? JsonSerializer.Serialize(value) : "{}";
Note, you will need to create a new ProductInfo every time and call the set on your property to take advantage ofserialization.
var newProduct = new Product { Info = new ProductInfo { Name = "Banana", Price = 3m }// correctnewProduct.Info = new() { Name = "Banana", Price = 4m };// incorrect (won't serialize when setting the value)newProduct.Info.Price = 4m;
You also won’t be able to use the ProductInfo property in your queries if you use NotMappedAttribute.
👋 To see a complete sample of this blog post, head to my GitHub repository.
Conclusion and Thoughts about JSON with SQL Server
While many folks use Entity Framework Core with SQL Server, I suspect many don’t use its JSON capabilities due to thelack of first-class support. But, as you’ve seen in this post, it only takes a handful of lines to get JSON querysupport added to Entity Framework Core. Additionally, future versions of Entity Framework Core will likely support JSONwithout the additional need for configuration. Suppose you’re struggling with sprawling schemas that include manyarray-like tables. In that case, this may be a better approach to reduce database size, query joins complexity, andnetwork IO. I recommend you give it a try, and let me know your thoughts.
Thank you for reading my posts and sharing them with your colleagues. If you have any thoughts or questions, follow meon Twitter at @buhakmeh.