Map Structured Data to Relational JSON Columns and Reason About Partial Updates

Learning outcomes

Table-split complex values are transparent relational columns. Sometimes ServiceHub instead needs one cohesive document-shaped value—for example, integration-specific dispatch instructions that change together and contain nested contact/route data. EF Core 10 can map complex types to a relational JSON column with ToJson. The abstraction is provider-agnostic at the model level; the generated SQL and storage type are not.

1. JSON is a storage shape, not an escape from modeling

A JSON column can reduce column sprawl and preserve document locality, but constraints, indexing, query translation, migration compatibility, and partial-update behavior become provider-sensitive. The first decision is still the domain model; ToJson changes how a complex value is stored.

public sealed record DispatchContact(    string Name,    string Phone);public sealed record DispatchProfile(    string AccessInstructions,    DispatchContact Contact,    List<string> RequiredTools);public sealed partial class WorkOrder{    public DispatchProfile DispatchProfile { get; private set; } =        new("No special access", new("Dispatch", "000000"), []);}

2. Map the complex value to one JSON column

builder.ComplexProperty(x => x.DispatchProfile, json =>{    json.ToJson("dispatch_profile");    json.ComplexProperty(x => x.Contact);    json.PrimitiveCollection(x => x.RequiredTools);});

Provider support matters. EF Core 10's complex JSON mapping is the model-level API. SQLite stores JSON in a text column and translates through SQLite JSON functions. SQL Server 2025/Azure SQL at compatibility level 170 can use the native json data type. Third-party PostgreSQL/MySQL/Oracle providers have independent version/translation matrices and must be verified before copying this model into their production stack.

3. Query into JSON and inspect SQLite SQL

var gated = db.WorkOrders    .Where(x => x.DispatchProfile.AccessInstructions.Contains("gate"))    .Select(x => new    {        x.WorkOrderNumber,        x.DispatchProfile.Contact.Name    });Console.WriteLine(gated.ToQueryString());var rows = await gated.ToListAsync(ct);

SQLite JSON translations may use json_extract, ->>, or related JSON functions/operators depending on shape and EF/provider version. The exact SQL is evidence to capture, not text to memorize.

SELECT "w"."work_order_number",       json_extract("w"."dispatch_profile", '$.Contact.Name')FROM "work_orders" AS "w"WHERE instr(    json_extract("w"."dispatch_profile", '$.AccessInstructions'),    'gate') > 0;

4. SaveChanges can patch one JSON path

EF's SQLite JSON support can translate a single nested change into a json_set update rather than blindly replacing every document byte. Verify generated command logs because patch granularity depends on change shape/provider capabilities.

var order = await db.WorkOrders.SingleAsync(x => x.Id == id, ct);order.ReplaceDispatchProfile(    order.DispatchProfile with    {        Contact = order.DispatchProfile.Contact with { Phone = "+994-12-555-0101" }    });await db.SaveChangesAsync(ct);

UPDATE "work_orders"SET "dispatch_profile" = json_set(    "dispatch_profile",    '$.Contact.Phone',    json_extract(@p0, '$[0]'))WHERE "work_order_id" = @id  AND "revision" = @original_revisionRETURNING 1;

Chapter 04's Revision concurrency token remains part of the predicate. JSON storage does not remove concurrency concerns.

5. ExecuteUpdate support is an EF Core 10 capability—with an important boundary

EF Core 10 added ExecuteUpdateAsync support for relational JSON columns when the document is modeled as a complex type. The release documentation explicitly distinguishes this from owned JSON mapping.

await db.WorkOrders    .Where(x => x.Status == WorkOrderStatus.Queued)    .ExecuteUpdateAsync(setters => setters        .SetProperty(            x => x.DispatchProfile.AccessInstructions,            x => x.DispatchProfile.AccessInstructions + " | confirm access"), ct);

Because ExecuteUpdate is immediate set-based DML, it bypasses tracked-entity synchronization. Do not run it while assuming already-tracked objects will magically refresh. Chapter 12 covers those write semantics in depth.

6. Deliberately wrong claim: “JSON behaves the same on every relational provider”

Portability means testing the model and workload on each supported provider, not assuming identical SQL, indexes, collation, migrations, or update granularity.

7. JSON indexing is a separate database-design decision

Frequent filtering on DispatchProfile.Contact.Phone may require a provider-specific generated column/expression/JSON index strategy. EF Core 11 adds more complex-path/SQL Server JSON-index APIs, but this EF10 course does not present those preview features as stable. For EF10, review provider/database indexes explicitly in migrations or database DDL and keep portability boundaries visible.

8. Schema evolution inside documents still needs migration thinking

A JSON column reduces relational DDL for internal shape changes, but old documents may not contain newly required members. Defaults in CLR constructors do not automatically rewrite historical JSON. Plan backward-compatible materialization, backfills, validation, and deployment ordering. Treat open provider/framework bugs as reasons to test representative old documents, not as folklore to ignore JSON entirely.

9. Hands-on lab: measure the document boundary

1. Add DispatchProfile as an EF Core 10 complex type mapped with ToJson to the disposable SQLite database.
2. Generate/review migration SQL and inspect the stored column with the SQLite CLI.
3. Query by nested contact/access values and capture ToQueryString().
4. Modify only the phone number; capture EF command logs and verify whether SQLite uses a JSON path update.
5. Replace the whole DispatchProfile and compare command shape.
6. Run one ExecuteUpdateAsync against a nested JSON member and then clear/requery tracked state before verifying results.
7. Create an older JSON document missing a newly added optional member; verify materialization behavior.
8. Record the SQLite library/provider versions with the lab evidence.

Verification checklist

• The model uses EF Core 10 complex JSON mapping, not a hand-written serialization converter for this structured document.
• Generated SQL is captured for query and update.
• Concurrency predicates remain visible.
• Tracked objects are not assumed synchronized after ExecuteUpdate.
• Provider-specific JSON behavior is labeled.

10. Production judgment and bridge

Choose JSON when document locality and query patterns justify it. Keep query frequency, indexes, update granularity, document size, schema evolution, provider support, backup/inspection tooling, and portability in the decision. The next lesson maps single properties through value converters and explains when EF also needs a ValueComparer to snapshot and compare domain values correctly.

Authoritative references

• Complex Types - EF Core — EF Core 10 ToJson mapping, complex collections, querying/updating, and limitations
• What's New in EF Core 10 — native SQL Server json integration and ExecuteUpdate for complex JSON
• What's New in EF Core 8 — SQLite JSON mapping/query/update implementation and json_extract/json_set examples
• SQLite Provider Limitations — SQLite type/migration limits that remain relevant around JSON workloads
• SQL Server EF Provider — SQL Server provider configuration and compatibility-level boundaries