Entity Framework Core Advanced

JSON columns

EF Core can map an object or a list to a single JSON column and still let you query inside it with LINQ. This lesson shows ToJson mapping, querying and updating JSON properties, primitive collections, and when a JSON column is better than another table.

From object to JSON column
  1. StartProduct.DetailsAn object with lists inside
  2. Step 2ToJson()Maps it to one column
  3. Step 3Saved as JSON{"Color":"blue","Specs":[...]}
  4. EndLINQ queryp.Details.Color == "blue" becomes JSON_VALUE

Mapping to JSON

Mark an owned type or complex type with ToJson and EF Core stores the whole object, including nested lists, in one nvarchar(max) column (or the native json type on newer SQL Server).

C#
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public ProductDetails Details { get; set; } = new();
    public List<string> Tags { get; set; } = [];   // primitive collection
}
public class ProductDetails
{
    public string? Color { get; set; }
    public List<Spec> Specs { get; set; } = [];
}
public class Spec { public string Key { get; set; } = ""; public string Value { get; set; } = ""; }

modelBuilder.Entity<Product>().OwnsOne(p => p.Details, d =>
{
    d.ToJson();
    d.OwnsMany(x => x.Specs);
});

Querying inside JSON

LINQ over JSON properties becomes JSON_VALUE and OPENJSON in SQL, so filtering happens in the database.

C#
var blue = await db.Products
    .Where(p => p.Details.Color == "blue")
    .ToListAsync();

var wireless = await db.Products
    .Where(p => p.Details.Specs.Any(s => s.Key == "connectivity" && s.Value == "wireless"))
    .ToListAsync();

var tagged = await db.Products
    .Where(p => p.Tags.Contains("sale"))     // List<string> stored as a JSON array
    .ToListAsync();

When to use JSON columns

  • Good: data whose shape differs per row, such as product specs, settings or form answers; data always loaded with its parent.
  • Not good: data you join, aggregate across rows or update often by itself. Use a normal table.
  • Filtering on JSON properties scans unless you add a computed column with an index (see the SQL JSON lesson).
  • Changing a property updates only that part when possible (EF Core 8+ uses JSON_MODIFY).

Interview question

When is a JSON column a better choice than a separate table in EF Core?

When the data belongs entirely to one parent row, is always loaded with it, and has a shape that varies, such as product specifications or user settings. A JSON column avoids extra tables and joins. When the data is shared, joined, aggregated across rows or updated on its own, a normal table with foreign keys is still the better design.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.