PostgreSQL jsonb with EF Core 10: Mapping, Indexing and Querying Document Columns
EF Core 10 with Npgsql 10 maps a .NET complex type to a PostgreSQL jsonb column using ToJson(). The article shows how LINQ property access becomes ->> SQL, which index types serve which queries, and when a jsonb column beats a separate EAV table. It is a practical guide for models with many sparse optional fields.
Key points
- Map the complex type to a jsonb column with ComplexProperty(...).ToJson("specs") on EF Core 10 and Npgsql 10.
- Give the column a lowercase name in ToJson so raw SQL does not need quoting.
- EF.Functions.JsonContains does not translate on this mapping; use FromSql for @> and ?.
- For LINQ's ->> comparisons, create an expression B-tree index; a GIN index does not serve them.
- Test presence with specs->>'Field' IS NOT NULL, since ? also matches keys stored as null.
- For single-property updates, use ExecuteUpdate with SetProperty; EF 10 translates it to jsonb_set.
Product specifications that are sparse and change often are awkward to model as one column per field, since each new field means a migration. The author shows how EF Core 10 maps a .NET complex type to a jsonb column with ComplexProperty(...).ToJson("specs"), and how LINQ property access turns into ->> SQL. Containment and existence functions such as EF.Functions.JsonContains do not translate on complex types, so @> and ? queries need FromSql, which keeps parameters safe.
On indexing, the key point is that a GIN index serves @> and ? but not the ->> comparisons EF generates. For those, an expression B-tree index matching the generated SQL is needed. The article also notes that PostgreSQL 18 virtual generated columns cannot be indexed, and that ExecuteUpdate with SetProperty on a document property is translated to jsonb_set, leaving other properties untouched. The full article has the test setup and query plans.
Real-world use
- A B2B SaaS product where each tenant adds custom fields without schema migrations.
- An e-commerce catalog API filtering products by category-specific specifications.
- A service storing event payloads or configuration blobs whose shape changes often in PostgreSQL.
Why it matters
Keeping sparse fields in jsonb only works well if you match each LINQ predicate with the right index type. A GIN index alone will not speed up the ->> comparisons EF generates.