Exploring SQL Server JSON mapping support in Entity Framework Core 9

JSON is a ubiquitous format for defining structured data, and it is increasingly common to see it being stored directly in databases that are primarily relational in nature. Over the past several years, Entity Framework Core has steadily improved its support for JSON, culminating in first-class JSON mapping support in EF Core 8 and EF Core 9.

In this article, we’ll look at how to map and query JSON data using the latest version of EF Core available at the time of writing, and I’ll showcase how it works in the context of a simple Todo entity. The focus will be on SQL Server, but the approach generally applies to PostgreSQL and other providers with JSON support.

Background

Earlier versions of EF Core (before version 8) offered limited support for JSON via either raw SQL queries or by registering custom methods that map to built-in SQL server functions.

Raw SQL queries

With Entity Framework Core, we can map the result of a raw SQL query to entities using the code shown below.

var todosByBob = await db.Todos
    .FromSqlRaw(@"SELECT * FROM dbo.Todos WHERE JSON_VALUE(Metadata, '$.CreatedBy.Email') = 'bob@example.com'")
    .ToListAsync();

Note that it is critical to know how to avoid being exploited by SQL injection attacks when executing raw SQL queries.

The above example executes a raw SQL query that uses the SQL Server JSON_VALUE function to extract all todo records where the email address of the ‘CreatedBy’ user is bob@example.com.

While it’s all well and good to use raw SQL queries, it can make other aspects of development, such as testing, more difficult. It can also be trickier to refactor the database schema without accidentally breaking the application. Due to the loss of compile-time checks, applications that use raw SQL queries are inherently more brittle, and great care must be taken to ensure that mistakes are not made in the query text.

Note that despite some disadvantages, working with raw SQL queries from application code is a widely adopted pattern. Its primary benefits are flexibility and performance, with libraries such as Dapper remaining very popular.

Database Functions

Compared to using raw SQL queries for working with JSON, the developer experience can be improved quite significantly by registering custom database functions.

The code below demonstrates how to map a C# method to a database function.

public static class JsonDbFunctions
{
    [DbFunction("JSON_VALUE", IsBuiltIn = true)]
    public static string JsonValue(string expression, string path) =>
        throw new NotImplementedException();
}

In the above example, a custom JsonValue method has been defined that maps to the SQL Server JSON_VALUE function. Notice that the method has been decorated with the DbFunction attribute.

Since JsonValue is a static method that has been defined outside of the DbContext, it needs to be registered within the OnModelCreating method of the DbContext class for it to be available when executing EF Core queries, as follows.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(() => JsonDbFunctions.JsonValue(default!, default!))
        .HasName("JSON_VALUE")
        .IsBuiltIn();
}

The custom method can then be used in a LINQ query as shown below.

var result = await db.Todos
    .Where(e => JsonDbFunctions.JsonValue(e.Metadata, "$.CreatedBy.Email") == "bob@example.com")
    .ToListAsync();

This can be considered an improvement on completely raw SQL queries, but it is still not an ideal approach, as we have to specify the JSON expression path as a string.

JSON mapping

The latest versions of EF Core solve the inherent problems of the approaches shown in the previous two sections by introducing the ability to map complex JSON objects directly to .NET types via owned entity mapping. This means that you can now create, update, and query JSON data just like any other mapped entity property using standard LINQ queries, which is a huge improvement!

Let’s move on and consider a scenario where JSON data is being stored in a SQL Server database, covering the prerequisites first and then moving on to the example.

Prerequisites

Before getting stuck into things, I recommend that you start by creating a simple C# Console App using the latest version of Visual Studio or Visual Studio Code and targeting the latest .NET version, which is 9.0 at the time of writing.

You’ll also need to install the following NuGet package into your C# Console App project.

Microsoft.EntityFrameworkCore.SqlServer

I recommend that you install the latest version of SQL Server or LocalDB (usually, you will already have access to LocalDB if you have Visual Studio installed) and the latest version of SQL Server Management Studio (SSMS) or Azure Data Studio for interacting with the SQL Server database to check the results.

Scenario

Let’s imagine we’re building a Todo application and we have a Todo entity that has some primary properties, such as Id and Title that are stored in regular INT and NVARCHAR columns respectively in a SQL Server database.

However, for the Todo entity, we want to store some additional data in a Metadata column, and this will be stored as a JSON string so that we don’t have to spread the data across multiple tables/columns. The metadata will include a priority level, a collection of tags, and information about the user who created the todo record.

Note that this scenario has been constructed purely for demonstration purposes and is not a recommendation on the type of data you should store in a JSON column for an entity like the one described. It is important to be aware of considerations such as performance, since querying based on JSON data will typically be slower, and indexes cannot be added directly to the JSON properties in SQL Server.

With EF Core 9, we can map JSON data cleanly to an owned type and query it just like any other C# object. Let’s see how we can do this in the next subsection.

Code example

Below you can find the entire code example for reference. To try it out, you can paste the code into the ‘Program.cs’ file of a simple C# Console App after installing the Microsoft.EntityFrameworkCore.SqlServer NuGet package.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
 
// Entity.
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public TodoMetadata Metadata { get; set; } = new();
}
 
// Enum.
public enum TodoPriority
{
    Low,
    Medium,
    High
}
 
// Owned type.
public class TodoMetadata
{
    public TodoPriority Priority { get; set; }
    public List<string> Tags { get; set; } = [];
    public CreatedByInfo CreatedBy { get; set; } = new();
}
 
// Nested owned type.
public class CreatedByInfo
{
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
}
 
// Entity configuration.
public class TodoConfiguration : IEntityTypeConfiguration<Todo>
{
    public void Configure(EntityTypeBuilder<Todo> builder)
    {
        builder.OwnsOne(t => t.Metadata, b =>
        {
            b.ToJson(); // Store as JSON column.
            b.OwnsOne(m => m.CreatedBy);
        });
    }
}
 
// DbContext.
public class AppDbContext : DbContext
{
    public DbSet<Todo> Todos { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EfJsonTodoDemo;Integrated Security=True;");
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new TodoConfiguration());
    }
}
 
// Program entry point.
class Program
{
    static async Task Main()
    {
        using var db = new AppDbContext();
        await db.Database.EnsureDeletedAsync();
        await db.Database.EnsureCreatedAsync();
 
        // Seed data.
        db.Todos.AddRange(
            new Todo
            {
                Title = "Buy milk",
                Metadata = new TodoMetadata
                {
                    Priority = TodoPriority.High,
                    Tags = ["shopping", "groceries"],
                    CreatedBy = new CreatedByInfo
                    {
                        Name = "Alice",
                        Email = "alice@example.com"
                    }
                }
            },
            new Todo
            {
                Title = "Leave out trash",
                Metadata = new TodoMetadata
                {
                    Priority = TodoPriority.High,
                    Tags = ["home", "chores"],
                    CreatedBy = new CreatedByInfo
                    {
                        Name = "Bob",
                        Email = "bob@example.com"
                    }
                }
            },
            new Todo
            {
                Title = "Clean room",
                Metadata = new TodoMetadata
                {
                    Priority = TodoPriority.Medium,
                    Tags = ["home", "cleaning"],
                    CreatedBy = new CreatedByInfo
                    {
                        Name = "Bob",
                        Email = "bob@example.com"
                    }
                }
            }
        );
 
        await db.SaveChangesAsync();
 
        // Query using JSON-mapped properties.
        var highPriorityTodos = await db.Todos
            .Where(t => t.Metadata.Priority == TodoPriority.High)
            .ToListAsync();
 
        string message = $"Found {highPriorityTodos.Count} high priority todos";
 
        Console.WriteLine(message);
        Console.WriteLine("".PadLeft(message.Length, '='));
        Console.WriteLine();
 
        foreach (var todo in highPriorityTodos)
        {
            Console.WriteLine($"{todo.Title} (by {todo.Metadata.CreatedBy.Name})");
        }
    }
}

Note that in a real-world application, you should split classes into separate files for maintainability. All code has been included in a single file above for the sake of simplicity.

In the code example, after the initial using statements for Entity Framework Core, all of the classes that make up the Todo entity are listed. This includes the Metadata object which will be an owned type of the Todo entity and the CreatedByInfo object, a nested owned type, which is owned by the Metadata object.

The Configure method within the TodoConfiguration class configures the ‘Owned’ object relationships, and importantly, calls the ToJson method to indicate that the Metadata object is to be mapped to and from JSON.

Next, the AppDbContext class is defined, and holds a reference to a DbSet of Todo entities. In the OnConfiguring method, the connection string for the SQL Server database is configured, and in the OnModelCreating method the TodoConfiguration is applied.

Note that in a real-world application, the database connection string should be made configurable.

In the Main method of the Program class, a new instance of the AppDbContext is created. The EnsureDeletedAsync and EnsureCreatedAsync methods are then called to ensure that a fresh copy of the database is generated each time the sample application is run.

Note that this is only for the convenience of demoing EF Core capabilities and testing changes. You should not recreate your database each time in a real-world application!

After creating/recreating the database, three sample todos are added to the context and the SaveChangesAsync method on the DbContext is then called to persist the new records, automatically mapping the JSON data.

The next part is the cool bit, the code is using a standard LINQ query to fetch todos where the value of the Priority property within the JSON Metadata column is High. The result is exactly what you would expect, with Entity Framework Core automatically translating the LINQ query into SQL that is similar to the following.

SELECT [t].[Id], [t].[Title], [t].[Metadata]
FROM [Todos] AS [t]
WHERE CAST(JSON_VALUE([t].[Metadata], '$.Priority') AS int) = 2

Lastly, the results of the query are output to the Console for clarity, as shown below.

Found 2 high priority todos
===========================
 
Buy milk (by Alice)
Leave out trash (by Bob)

As expected, only the high-priority todos are returned by the query.

What’s happening under the hood?

EF Core stores the entire Metadata object as a single JSON string in the Todos table, according to the entity configuration mapping that was specified. This JSON string is automatically deserialised into a strongly typed C# object when read, and re-serialised to JSON on write.

This means:-

• You get full IntelliSense and compile-time checking.
• You don’t need to use raw SQL or custom database functions.
• LINQ just works!

As you saw in the previous section, behind the scenes, EF Core translates LINQ expressions into native SQL Server JSON functions, such as JSON_VALUE and JSON_QUERY without needing to worry about how things are working on the database side.

Advantages of JSON mapping

Here are some key advantages of mapping your data as JSON using the latest versions of EF Core.

• ✅ Abstraction: There’s no need to worry about how the JSON mapping is implemented under the hood. You can rely on a solid implementation from the Entity Framework Core team, and when new JSON capabilities are added to SQL Server, you can expect to benefit from these without needing to change your code.
• ✅ Productivity: Since there’s no perceivable difference in how you work with data points that are mapped to JSON, other than needing to add the mapping configuration code, you can be as productive as possible, since you don’t need to consider how to read/write/query data in an alternative way.
• ✅ Type safety: Using strongly typed objects and LINQ reduces the risk of mistakes and means that most issues will be caught at compile-time.

Additionally, the general benefits of JSON mapping include the following.

• ✅ Cleaner schemas: You can consolidate additional data into a single column without creating multiple related tables, which can be very useful for storing metadata that doesn’t need to be queried frequently.
• ✅ Flexibility: JSON data storage is increasingly popular in modern systems where flexibility is important, and it can be very useful for storing complex configuration or third-party JSON data that you don’t want to remap into a relational database structure.

Caveats

Before wrapping up, here are a few important things to be aware of.

• EF Core does not currently support partial updates to JSON values; a change to any property in the owned type will trigger a full column rewrite.
• Full-text indexing or filtering on JSON fields may require custom indexes or computed columns.
• JSON column mapping works best when used thoughtfully. Don’t abuse it as a replacement for normalised relational design when data relationships matter.

Conclusion

The native support for JSON mapping in the latest versions of Entity Framework Core is a welcome improvement that makes working with JSON data much easier, opening the door to flexible data models and making application code both easier to write and safer. Whether you’re storing user preferences, dynamic form configurations, or general metadata, this feature will help make your life that bit easier.

Entity Framework Core 8 and 9 make working with JSON feel like a natural part of the ORM, rather than requiring a bolt-on or workaround to make storing and querying JSON possible.

If you’re using SQL Server (or Azure SQL) and can upgrade to EF Core 8 or later, I highly recommend giving it a try.