Until recently, using JSON in SQL Server meant storing it as plain text. No more!
Now with the new json data type introduced in SQL Server (and available in Azure SQL Database), SQL Server finally provides native support for efficient, type safe JSON and querying.
EF Core 10 will let you persist rich aggregates as JSON while retaining LINQ, type safety and excellent performance.
Getting started
To enable EF Core 10's automatic JSON data type support, configure your DbContext with compatibility level 170 or higher:
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString, sqlServer =>
{
sqlServer.UseCompatibilityLevel(170);
}));Once enabled, EF Core automatically maps owned types and collections to the native json column type instead of nvarchar(max).
Here’s a simple model using value objects that will be stored as JSON:
public sealed class Order
{
public Guid Id { get; set; }
public CustomerInfo Customer { get; set; }
public List<OrderItem> Items { get; set; }
public DateTime PlacedAtUtc { get; set; }
}
public sealed class CustomerInfo
{
public string Name { get; set; }
public Address Address { get; set; }
}
public sealed class Address
{
public string Street { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
public sealed class OrderItem
{
public string Sku { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
}Order owns customer info, address and a collection of order items.
With the models defined, we now need to map them into JSON columns:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var order = modelBuilder.Entity<Order>();
order.ToTable("Orders");
order.OwnsOne(o => o.Customer, customerBuilder =>
{
customerBuilder.ToJson("Customer");
customerBuilder.Property(c => c.Name)
.HasJsonPropertyName("name")
.IsRequired();
customerBuilder.OwnsOne(c => c.Address, addressBuilder =>
{
addressBuilder.Property(a => a.Street)
.HasJsonPropertyName("street")
.IsRequired();
addressBuilder.Property(a => a.City)
.HasJsonPropertyName("city")
.IsRequired();
addressBuilder.Property(a => a.Country)
.HasJsonPropertyName("country")
.IsRequired();
});
});
order.OwnsMany(o => o.Items, itemsBuilder =>
{
itemsBuilder.ToJson("Items");
itemsBuilder.Property(i => i.Sku)
.HasJsonPropertyName("sku")
.IsRequired();
itemsBuilder.Property(i => i.Quantity)
.HasJsonPropertyName("quantity")
.IsRequired();
itemsBuilder.Property(i => i.UnitPrice)
.HasJsonPropertyName("unit_price")
.IsRequired();
});
}Each owned type is stored inside a JSON column and each inner property gets a stable JSON name.
EF Core 10 also supports mapping primitive collections (string[] or int[]) directly to JSON columns.
For example, if you have a Tags property of type string[], EF will automatically map it to a json column without requiring any explicit configuration:
public sealed class OrderItem
{
public string Sku { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
public string[] Tags { get; set; }
}Schema & migrations
With the mapping in place, EF Core generates the following table:
CREATE TABLE [Orders] (
[Id] uniqueidentifier NOT NULL PRIMARY KEY,
[Customer] json NOT NULL,
[Items] json NOT NULL,
[PlacedAtUtc] datetime2 NOT NULL
);The native json data type offers better storage efficiency, validation and query performance compared to nvarchar(max) columns.
If your existing application already stores JSON in text columns, EF Core 10 automatically upgrades them to the native json type during the first migration if compatibility level 170 is configured.
This migration happens seamlessly, preserving your existing data.
If you prefer to keep certain columns as nvarchar(max), you can override the type explicitly or simply use a compatibility level lower than 170:
order.Property(o => o.Customer)
.HasColumnType("nvarchar(max)");Querying JSON with LINQ
Once JSON mapping is enabled, EF Core translates member access directly into JSON path queries.
This allows you to filter and project nested fields without loading entire JSON documents.
var ordersInParis = dbContext.Orders
.Where(o => o.Customer.Address.City == "Paris")
.Select(o => new { o.Id, o.Customer.Name })
.ToListAsync(cancellationToken);
var highValue = dbContext.Orders
.Where(o => o.Items.Any(i => i.Quantity * i.UnitPrice > 500))
.CountAsync(cancellationToken);EF translates these into efficient SQL using JSON_VALUE():
SELECT JSON_VALUE([o].[Customer], '$.name') AS [Name]
FROM [Orders] AS [o]
WHERE JSON_VALUE([o].[Customer], '$.address.city') = N'Paris'Conclusion
EF Core 10 makes working with JSON in SQL Server straightforward and efficient.
With the native JSON data type support, you get better performance, type safety and storage efficiency.
Existing JSON stored in text columns can be migrated automatically, preserving all data.
If you want to check out examples I created, you can find the source code here:
I hope you enjoyed it, subscribe and get a notification when a new blog is up!