skip to content

In EF Core 10, how do you map an order's delivery address and price adjustments to JSON columns, and how do LINQ filters on them translate?

level: seniorimportance: should knowfreq 38%

answer

  1. one column, one document
  2. ComplexProperty(..., b => b.ToJson())
  3. ComplexCollection needs ToJson
  4. Npgsql: jsonb and the ->> operator
  5. HasJsonPropertyName renames a key

basics

~20 s

Call ToJson on a complex property or complex collection: ComplexProperty(o => o.DeliveryAddress, b => b.ToJson()). EF Core 10 stores each in one JSON column and translates filters on members into JSON path SQL, such as the ->> operator on PostgreSQL's jsonb or JSON_VALUE on SQL Server.

solid answer

~40 s

In EF Core 10 the preferred mapping is a complex type: `ComplexProperty(o => o.DeliveryAddress, b => b.ToJson())` for one value and `ComplexCollection(o => o.Adjustments, b => b.ToJson())` for a list. A complex collection on a relational provider must be mapped to JSON; table splitting cannot hold it. Owned types have offered the same through `OwnsOne(..., b => b.ToJson())` since EF Core 7, but EF Core 10 recommends complex types. Queries reach inside the document: `Where(o => o.DeliveryAddress.City == "Leeds")` becomes `o."DeliveryAddress" ->> 'City'` with Npgsql (a `jsonb` column) and `JSON_VALUE` on SQL Server, and `Any` over the adjustments becomes `jsonb_to_recordset` or `OPENJSON`. `HasJsonPropertyName` renames a key inside the document, and a nested complex type inside a JSON-mapped one is stored in the same document.

code

csharp · 14 lines
csharp
public record Money(decimal Amount, string Currency);
public record DeliveryAddress(string Street, string City, string PostCode, string? Note);
public record PriceAdjustment(string Reason, Money Amount);

modelBuilder.Entity<Order>(b =>
{
    b.ComplexProperty(o => o.DeliveryAddress, a => a.ToJson());
    b.ComplexCollection(o => o.Adjustments, a => a.ToJson());
});

var leeds = await db.Orders
    .Where(o => o.DeliveryAddress.City == "Leeds")
    .Where(o => o.Adjustments.Any(a => a.Reason == "damaged"))
    .ToListAsync(ct);

go deeper

for a junior

Recall that ToJson stores a value object as one JSON column and that EF Core can still filter on the values inside it.

for a middle

Explain ComplexProperty versus ComplexCollection with ToJson, why collections need JSON, and how HasJsonPropertyName and ToJson(name) differ.

for a senior

Read the SQL a filter on a JSON member produces on your provider, and decide when a document beats a child table: no foreign keys, read with the owner, no cross-row reporting.

for a principal

Judge where documents belong in a relational model: fewer joins and simpler schemas against weaker constraints, harder reporting and provider-specific SQL.

## What a JSON column buys you Mapping a value to a **JSON column** stores the whole object, nested values and lists included, as one document in one column of the owner's row. Reading an order no longer needs a join to a child table, the schema does not grow a column per nested field, and the shape can hold collections that table splitting cannot. EF Core still understands the document: it materializes it into typed .NET objects, translates LINQ over its members, and tracks changes to the values inside it. In a furniture shop, an `Order` might carry a `DeliveryAddress` (street, city, post code, a delivery note) and a list of `PriceAdjustment` values (a reason and a `Money` amount for each discount or surcharge). Neither has an identity of its own, and both are always read with the order. ## Mapping with complex types (EF Core 10) 1. Model the values as complex types: `record DeliveryAddress(...)`, `record PriceAdjustment(string Reason, Money Amount)`. 2. Map the single value with `ComplexProperty(o => o.DeliveryAddress, b => b.ToJson())`. 3. Map the list with `ComplexCollection(o => o.Adjustments, b => b.ToJson())`. 4. Optionally rename the column with `ToJson("delivery")` and a key inside the document with `HasJsonPropertyName("postcode")`; EF Core also honours `[JsonPropertyName]` on the property. Rules the model validator enforces: - A **complex collection** on a relational provider must use `ToJson()`; otherwise validation fails with "must be mapped to a JSON column". - A nested complex type inside a JSON-mapped type is stored inside the same document. The reverse is rejected: a complex property cannot be mapped to JSON while its containing complex type is table-split. - A property cannot have both a JSON column name and a JSON property name. ## Owned types did this first EF Core 7 introduced JSON columns for owned types: `OwnsOne(o => o.DeliveryAddress, b => b.ToJson())` and `OwnsMany(...)` with `ToJson()`. They still work in EF Core 10, but owned types keep their hidden keys and reference semantics, and only complex types support `ExecuteUpdate` into the document. The EF Core 10 documentation recommends complex types for new JSON mappings. ## The column type The provider picks the store type. Npgsql maps the document to **`jsonb`**. SQL Server uses a text column, or its native `json` type when the database supports it; EF Core 10 changed that default for Azure SQL and compatibility level 170, which is a provider topic of its own. `HasColumnType` overrides the choice. ## How queries translate EF Core turns member access into the provider's JSON path functions, so a filter runs in the database instead of in memory: | LINQ | Npgsql (`jsonb`) | SQL Server | |---|---|---| | `o.DeliveryAddress.City == "Leeds"` | `o."DeliveryAddress" ->> 'City'` compared with the value | `JSON_VALUE([o].[DeliveryAddress], '$.City')` | | an integer member | `CAST(... ->> 'Floor' AS integer)` | `JSON_VALUE` with a conversion | | `o.Adjustments.Any(a => a.Reason == "damaged")` | `jsonb_to_recordset` over the array | `OPENJSON` over the array | | `o.Adjustments.Count` | `count(*)` over `jsonb_to_recordset` | `COUNT` over `OPENJSON` | Projections work the same way: `Select(o => o.DeliveryAddress.City)` reads one key out of the document. ## Saving changes EF Core tracks the scalars inside the document like any other property. When only part of the document changed, the providers can patch that part instead of rewriting the column, with `JSON_MODIFY` on SQL Server and `jsonb_set` on PostgreSQL. Replacing a whole complex value still copies values; there is no identity to keep. ## When not to use JSON - The values need **foreign keys** or constraints to other tables; a document cannot hold a navigation to an entity. - Other entities must reference individual elements; they need keys, so they are entities. - Reports join or aggregate across the elements of every row; a child table is easier to index and to query. For value objects read with their owner, such as delivery details, adjustments or a product's dimensions, a JSON column keeps the model and the schema simple.

  • Why does EF Core 10 reject a complex collection mapped without ToJson on SQL Server or PostgreSQL?
    Table splitting adds a fixed set of columns to the owner's row, so it cannot hold a variable number of elements. A complex type has no key, so it cannot become rows in a child table either. The only relational storage left is a JSON array in one column, and the model validator says so: the collection "must be mapped to a JSON column".
  • An existing model maps the delivery address with OwnsOne(...).ToJson(). What do you gain by moving it to a complex type?
    Value semantics: one address can be copied to another property, and LINQ equality compares contents rather than hidden keys. You also gain `ExecuteUpdate` into the JSON document, which owned types do not support. Plan the change like any model change and review the generated migration, but the column stays one JSON document.

  • Filtering on a member inside a JSON column always loads the rows and filters in memory.
  • A complex collection can be mapped to columns of the owner's table without ToJson.
  • ToJson is new in EF Core 10 and never worked with owned types.
  • HasColumnName renames a key inside the JSON document.
  • A JSON-mapped complex type can hold a navigation to another entity.

Assumes EF Core 10, which added ToJson for complex types and complex collections; owned types have had ToJson since EF Core 7.