Entity Framework Core 7 JSON columns

In this article, I will show a brief introduction about EF Core 7 and JSON columns and also a practical example that enables the operation of mapping, querying and updating JSON data.

Support for JSON columns allows relational database systems to adopt some of the characteristics of document database systems.

The JSON in these columns can be drilled down with queries. This allows, for example, filtering and sorting by specific document elements, as well as projection of document elements into the results.

EF7 has vendor-independent support for JSON columns, with an implementation for SQL Server. This support enables mapping of aggregates (created from .NET types) to JSON documents.

Standard LINQ queries can be used in aggregates and will be converted to the proper query constructs needed to drill down into the JSON.

Additionally, EF7 allows updating and storing changes to JSON documents.

Let’s go to code👇

Mapping to JSON columns

In EF Core, aggregate types are defined using  “OwnsOne” and  “OwnsMany”. Let’s use the Address example for this example.

The aggregate type is set  “OnModelCreating” using  OwnsOne:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //User
    ...
    modelBuilder.Entity<User>().OwnsOne(b => b.Address);
    ...
}

By default, EF Core’s relational database providers map aggregate types like this to the same table as the owning entity type. That is, each property of the classes  “Address” is assigned to a column of the table  “User”:

If desired, each type of entity that makes up the aggregate can be assigned to its own table using ToTable:

//User
...
modelBuilder.Entity<User>()
    .OwnsOne(b => b.Address, ownedNavigationBuilder =>
    {
        ownedNavigationBuilder.ToTable("Address");
    });

...

With this change a new table called Address is generated:

Finally, let’s look at the cool new feature of EF7, the aggregate Address can be assigned to a JSON column. This requires only one call when setting the aggregate type: ToJson()

//User
...
modelBuilder.Entity<User>()
    .OwnsOne(b => b.Address, ownedNavigationBuilder =>
    {
        ownedNavigationBuilder.ToJson();
    });

...

The table  User will now contain a JSON column for  “Address”, in which we will store a JSON document for each user:

Las columnas JSON brindan las capacidades de usar EF Core contra bases de datos de documentos a documentos incrustados en una base de datos relacional.

Los documentos JSON que se muestran arriba son muy simples, pero esta capacidad de mapeo también se puede usar con estructuras de documentos más complejas.

Query JSON columns

Queries against JSON columns work just like queries against any other type of aggregate in EF Core.

var user = await context.Users
    .Where(user => user.Address.City == "Madrid")
    .ToListAsync();

In this example it does a bit more filtering and projection, and also sorts by email in the JSON document:

var orderedAddresses = await context.Users
    .Where(
        user => (user.Address.City == "Chigley"
                   && user.Email != null)
                  || user.Name.StartsWith("D"))
    .OrderBy(user => user.Email)
    .Select(
        user => user.Name + " (" + user.Address.Street
                  + ", " + user.Address.City
                  + " " + user.Address.Postcode + ")")
    .ToListAsync();

Consider creating indexes to improve query performance on JSON documents. For example, see Index Json data when using SQL Server.

JSON column update

You use SaveChanges and SaveChangesAsync in the normal way to make updates to a JSON column. For extensive changes, the entire document will be updated.

var user = await context.Users.SingleAsync(u => u.Name.StartsWith("Alberto"));

user.Contact = new() { Address = new("2 Riverside", "Trimbridge", "TB1 5ZS", "UK"), Phone = "01632 88346" };

await context.SaveChangesAsync();

However, if only one subdocument is changed, EF Core will use a “JSON_MODIFY” command to update only the subdocument. For example, changing the inside of  “Address” a document “Contact”:

var user = await context.Users.SingleAsync(u => u.Name.StartsWith("Luis"));

user.Contact.Address = new("4 Riverside", "Trimbridge", "TB1 5ZS", "UK");

await context.SaveChangesAsync();

Finally, if only a single property is changed, EF Core will again use a “JSON_MODIFY” command, this time to patch only the changed property value. For example:

var user = await context.Authors.SingleAsync(u => u.Name.StartsWith("Antonio"));

user.Contact.Address.Country = "España";

await context.SaveChangesAsync();

Summary

EF Core 7.0 (EF7) added support for assigning aggregate types to JSON documents stored in relational database “JSON columns”. As a result, relational databases can store documents directly while maintaining the overall relational structure of the data. JSON columns are supported in EF7 with an implementation for SQL Server that is vendor independent. The JSON contained in these columns can be queried using LINQ, which allows filtering and sorting by document element, as well as projection of the document element into the results. Also, when SaveChanges is invoked, EF7 allows partial updates only for changed items and item-level change tracking of documents.

That’s it, for now, I’ll keep updating and adding content in this post. I hope you found it interesting😉

Download

The source code for this article can be found on  GitHub