Querying Semi-Structured Data

Utilize EF Core support for JSONB to query deeply nested data efficiently.

In this lesson, you will learn how to leverage EF Core’s native JSONB support to query deeply nested semi-structured data without sacrificing performance.

While relational databases excel at structured tables, modern applications often store semi-structured data (like user preferences or configuration metadata) as JSON. If you are using PostgreSQL, EF Core allows you to map these JSON blobs to C# objects while maintaining the ability to query inside them as if they were standard relational columns.

Mapping JSONB

First, you define your entity and ensure the property is mapped as a JSON document in your DbContext using the Fluent API:

modelBuilder.Entity<UserPreference>()
    .OwnsOne(u => u.Settings, builder => {
        builder.ToJson();
    });

By calling .ToJson(), EF Core instructs the database provider to store the Settings object as a jsonb column. The magic happens when you need to access specific fields within that document.

Querying Deeply Nested Data

Because EF Core translates LINQ expressions into SQL, you can query directly into the nested properties. If your Settings object has a deeply nested structure like Theme -> Colors -> Background, you can write a standard LINQ query:

var darkThemeUsers = context.UserPreferences
    .Where(u => u.Settings.Theme.Colors.Background == "DarkSlate")
    .ToList();

EF Core converts this into a JSON path query (e.g., WHERE settings->'Theme'->'Colors'->>'Background' = 'DarkSlate'). Because this is native JSONB, the database can use GIN (Generalized Inverted Index) indexes to find these records instantaneously, even if the JSON structure is complex or varies between records.

Why this matters

The alternative—fetching the entire JSON blob into memory and parsing it with System.Text.Json—is expensive and prevents the database from filtering the data. By using EF Core’s mapping, you keep the flexibility of semi-structured data while offloading the heavy lifting of searching to the database engine. This allows you to treat your JSON properties like "virtual" columns that are both searchable and performant.

Key Takeaway: Use the .ToJson() configuration in Fluent API to map complex objects as JSONB, allowing you to execute type-safe, performant LINQ queries directly against nested JSON properties.