EF Core 7.0: Save and Query JSON in Relational Databases

In this post, let's have a look at a new feature that's going to be available with EF Core 7.0 and that's the support for saving and querying JSON columns in Relational Databases. 

You can try this feature with the latest RC builds or using daily builds.

Tip: How to get .NET Daily Builds

Add a NuGet.config file to your project with the following content.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <packageSources>
    <clear />
    <add key="dotnet7" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet7/nuget/v3/index.json" />
    <add key="NuGet.org" value="https://api.nuget.org/v3/index.json" />
  </packageSources>
</configuration>

For the purpose of this post, I am targeting Microsoft SQL Server as the relational database provider.

Consider the following scenario. Say we have a Customer entity, and a Customer has a Contact record that contains his/her Address and List of PhoneNumbers. 

public class Customer
{
    public int Id { get; init; }
 
    public string Name { get; init; }
 
    public Contact Contact { get; set; } = null!;
 
    public Customer(string name)
    {
        Name = name;
    }
}
 
public class Contact
{
    public required Address Address { get; set; }
 
    public List<PhoneNumber> PhoneNumbers { get; set; } = new();
}
 
public class Address
{
    public required string Street { get; set; }
 
    public required string City { get; set; }
 
    public required string State { get; set; }
 
    public required string PostalCode { get; set; }
}
 
public class PhoneNumber
{
    public PhoneNumberType Type { get; set; }
 
    public string Number { get; set; }
 
    public PhoneNumber(PhoneNumberType type, string number)
    {
        Type = type;
        Number = number;
    }
}
 
public enum PhoneNumberType
{
    Mobile,
    Home
}

And whenever we load a Customer, we want his/hers Contact details to be loaded automatically because we don't want to do explicit Includes. 

In order to achieve this, we can configure the Customer entity as follows.

public class MyDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .OwnsOne(x => x.Contact, contactOptions =>
            {
                contactOptions.OwnsOne(x => x.Address);
                
                // Since PhoneNumbers is a Collection, it needs to be a separate table
                // Here we are just customizing the table
                contactOptions.OwnsMany(x => x.PhoneNumbers, phoneNumberOptions =>
                {
                    phoneNumberOptions
                        .Property(x => x.Type)
                        .HasConversion<string>();
 
                    phoneNumberOptions.ToTable("CustomerPhoneNumbers");
                });
            });
    }
}

And above model configuration will create a table structure as follows.

Table Structure

Since PhoneNumbers is a Collection, it needs to be in a separate table. With the JSON column support, we can store the Contact as JSON and avoid data being split across multiple tables.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .OwnsOne(x => x.Contact, contactOptions =>
        {
            contactOptions.ToJson();
 
            contactOptions.OwnsOne(x => x.Address);
 
            contactOptions.OwnsMany(x => x.PhoneNumbers);
        });
}

And this will produce a single table as follows.

Table Structure

When we store the data, it will be as follows.

JSON Column

Contact is basically stored as a JSON.

{
  "Address": {
    "City": "Seattle",
    "PostalCode": "98052",
    "State": "WA",
    "Street": "123 Main St"
  },
  "PhoneNumbers": [
    {
      "Number": "111-123-4567",
      "Type": "Mobile"
    },
    {
      "Number": "222-123-4568",
      "Type": "Home"
    }
  ]
}

Querying

We can do queries on JSON data, for example, consider the following.

List<Customer> customersInWA = await context.Customers
    .Where(x => x.Contact.Address.State == "WA")
    .ToListAsync();

The generated SQL statements will be as follows. The nice thing is EF uses JSON capabilities of SQL Server. 

SELECT [c].[Id], [c].[Name], JSON_QUERY([c].[Contact],'$')
FROM [Customers] AS [c]
WHERE CAST(JSON_VALUE([c].[Contact],'$.Address.State') AS nvarchar(max)) = N'WA'

Projection

List<string> distinctStates = await context.Customers
    .Select(x => x.Contact.Address.State)
    .Distinct()
    .ToListAsync();

The generated SQL statement:

SELECT DISTINCT CAST(JSON_VALUE([c].[Contact],'$.Address.State') AS nvarchar(max))
FROM [Customers] AS [c]

Update

Customer janeDoe = await context.Customers.SingleAsync(x => x.Name == "Jane Doe");
janeDoe.Contact.Address.PostalCode = "20877";
await context.SaveChangesAsync();

The generated SQL statement:

[Parameters=[@p0='["20877"]' (Nullable = false) (Size = 9), @p1='2'], CommandType='Text', CommandTimeout='30']

SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [Customers] SET [Contact] = JSON_MODIFY([Contact], 'strict $.Address.PostalCode', JSON_VALUE(@p0, '$[0]'))
OUTPUT 1
WHERE [Id] = @p1;

Current Limitations (to my knowledge):

• Complex queries that query through JSON arrays, still can't be translated into SQL. As far as I know, it's going to be available in EF Core 8.0. Basically, something like below won't work.

// Following query will be failed to translate into SQL
List<Customer> customersHavingHomePhoneNumbers = await context.Customers
    .Where(x => x.Contact.PhoneNumbers.Any(x => x.Type == PhoneNumberType.Home))
    .ToListAsync();

More Read:
   JSON Columns

Hope this helps.

Happy Coding.

Regards,
Jaliya