While other database providers have made their place known within the .NET ecosystem, Microsoft’s SQL Server (MSSQL) and Azure flavor are still likely the most popular choice for .NET developers. The engine’s popularity is because Microsoft’ s SQL Server has long been a reliable choice with Microsoft support and a large tooling ecosystem, making it a safe option for developers and database administrators. As a result, there are few surprises when choosing MSSQL, but don’t confuse that with being “boring”.

One of MSSQL’s hidden gems is its ability to allow you to store and query JSON data in existing tables. Storing information as JSON can help reduce interaction complexity between your application and SQL, with storage objects typically a combination of identifier, sorting columns, and then a JSON column. JSON also lets you be more flexible about what you store, allowing you to store dynamic data that isn’t easy with relational database schemas.

.NET Developers also utilize Entity Framework Core to access their database, allowing the library to generate SQL and map data into C# objects.

In this post, you’ll see how to modify Entity Framework Core to support SQL Server’s JSON_VALUE and JSON_QUERY in your LINQ queries.

What are JSON_VALUE and JSON_QUERY?

In MSSQL, you store JSON as nvarchar(max) or some variation of nvarchar, but storage is only half of the JSON story. What about querying your JSON or retrieving the data from the text column? While JSON is typically structured, it isn’t a rigid structure like you’d expect from a relational table. Your JSON data can have fields that are scalar values, arrays, or nested objects. With JSON’s infinite possibilities, you need mechanisms to access the data appropriately, and that’s when you use JSON_VALUE and JSON_QUERY.

JSON_VALUE allows you to extract a scalar value (think numbers, strings values, etc.) from an existing JSON column. You can use the database function to add JSON values to a current SQL query.

Our JSON column might store a JSON object like the following example.

{
  "info": {
    "type": 1,
    "address": {
      "town": "Bristol",
      "county": "Avon",
      "country": "England"
    },
    "tags": [
      "Sport",
      "Water polo"
    ]
  },
  "type": "Basic"
}

JSON

You can query the town value from the JSON column with JSON_VALUE.

SELECT FirstName, 
       LastName, 
       JSON_VALUE(jsonInfo, '$.info.address.town') AS Town
FROM Person.Person
WHERE JSON_VALUE(jsonInfo, '$.info.address.state') LIKE 'US%'
ORDER BY JSON_VALUE(jsonInfo, '$.info.address.town')

SQL

Note that Town is a string value, not a complex object. If you need complex objects or to access arrays, you’ll need to 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 see what 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

SQL

The result of our query is a JSON Array.

["Sport", "Water polo"]

Plain text

You typically won’t see JSON_QUERY in your Where clauses unless it’s matched with other functions to get to a precise set of values.

Select JSON_QUERY(@jsonInfo, '$.info.tags')
Where JSON_QUERY(@jsonInfo, '$.info.tags') like '%Sport%'

SQL

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’ll need.

In your DbContext implementation, Add the following lines of code, or modify your OnModelCreating method to include the 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();

C#

You must use the IsBuiltIn method, as you tell EF Core that this database function is not part of any existing schema like dbo. The static methods will allow you to use C# methods as markers for EF Core’s translation process. We don’t need any implementation in our methods, but you could certainly add validation logic that runs before the translation process 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 data models 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; }
}

C#

Now, what’s a LINQ Query going to look like in your codebase?

using Microsoft.EntityFrameworkCore;

// project namespace
using 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})");
}

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 into the appropriate SQL cast.

You’ll see the SQL query and the results running the sample. **Your results will depend on the data in your database instance.

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.0
ORDER BY CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) DESC

Sleek 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)

Console

As you’ll notice, the SQL query is quite readable, and you can convert a JSON value into any database type for use in filtering 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 can use 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) : "{}";
    }
}

C#

Note, you will need to create a new ProductInfo every time and call the set on your property to take advantage of serialization.

var newProduct = new Product {
    Info = new ProductInfo { Name = "Banana", Price = 3m }
};

// correct
newProduct.Info = new() { Name = "Banana", Price = 4m };

// incorrect (won't serialize when setting the value)
newProduct.Info.Price = 4m;

C#

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 the lack of first-class support. But, as you’ve seen in this post, it only takes a handful of lines to get JSON query support added to Entity Framework Core. Additionally, future versions of Entity Framework Core will likely support JSON without the additional need for configuration. Suppose you’re struggling with sprawling schemas that include many array-like tables. In that case, this may be a better approach to reduce database size, query joins complexity, and network 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 me on Twitter at @buhakmeh.