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