Dynamic Schema Design with EF Core JSON Columns

Guidance for implementing flexible content schemas using EF Core JSON columns, enabling dynamic custom fields without database migrations.

When to Use This Skill

• Designing custom field storage for CMS content types

• Implementing dynamic properties that vary per content instance

• Avoiding frequent database migrations for schema changes

• Querying JSON data with LINQ in EF Core

• Planning indexing strategies for JSON columns

• Migrating from EAV (Entity-Attribute-Value) to JSON storage

EF Core JSON Column Fundamentals

Basic Configuration (.NET 10 / EF Core 10)

// Entity with JSON-stored custom fields public class ContentItem { public Guid Id { get; set; } public string ContentType { get; set; } = string.Empty; public string Title { get; set; } = string.Empty; public DateTime CreatedUtc { get; set; }

// JSON column for dynamic fields
public CustomFieldsData CustomFields { get; set; } = new();

}

// Owned entity stored as JSON public class CustomFieldsData { public Dictionary<string, object?> Fields { get; set; } = new(); public Dictionary<string, FieldMetadata> Metadata { get; set; } = new(); }

public class FieldMetadata { public string FieldType { get; set; } = string.Empty; public bool IsRequired { get; set; } public string? DisplayName { get; set; } }

DbContext Configuration

public class ContentDbContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<ContentItem>(entity => { entity.HasKey(e => e.Id);

        // Configure JSON column with ToJson()
        entity.OwnsOne(e => e.CustomFields, builder =>
        {
            builder.ToJson();
        });
    });
}

}

JSON Column Patterns

Pattern 1: Typed Custom Fields

Best for when field schemas are known at compile time.

// Strongly-typed custom fields public class ArticleFields { public string? Subtitle { get; set; } public List<string> Tags { get; set; } = new(); public AuthorInfo? Author { get; set; } public int? ReadTimeMinutes { get; set; } public bool IsFeatured { get; set; } }

public class AuthorInfo { public Guid AuthorId { get; set; } public string DisplayName { get; set; } = string.Empty; public string? Bio { get; set; } }

// Entity using typed fields public class Article { public Guid Id { get; set; } public string Title { get; set; } = string.Empty; public string Body { get; set; } = string.Empty;

public ArticleFields Fields { get; set; } = new();

}

// Configuration modelBuilder.Entity<Article>(entity => { entity.OwnsOne(e => e.Fields, builder => { builder.ToJson(); builder.OwnsOne(f => f.Author); }); });

Pattern 2: Dynamic Property Bag

Best for fully dynamic schemas where fields vary per instance.

public class DynamicContent { public Guid Id { get; set; } public string ContentType { get; set; } = string.Empty;

// Flexible property bag
public JsonDocument? Properties { get; set; }

}

// Alternative using Dictionary public class FlexibleContent { public Guid Id { get; set; } public string ContentType { get; set; } = string.Empty;

public Dictionary<string, JsonElement> Fields { get; set; } = new();

}

Pattern 3: Hybrid Approach (Recommended)

Combine fixed columns for common fields with JSON for extensions.

public class ContentItem { // Fixed columns (indexed, frequently queried) public Guid Id { get; set; } public string ContentType { get; set; } = string.Empty; public string Title { get; set; } = string.Empty; public string? Slug { get; set; } public ContentStatus Status { get; set; } public DateTime CreatedUtc { get; set; } public DateTime? PublishedUtc { get; set; }

// JSON column for type-specific and custom fields
public ContentExtensions Extensions { get; set; } = new();

}

public class ContentExtensions { // Part-specific data stored as nested JSON public TitlePartData? TitlePart { get; set; } public SeoPartData? SeoPart { get; set; } public MediaPartData? MediaPart { get; set; }

// Fully dynamic custom fields
public Dictionary<string, object?> CustomFields { get; set; } = new();

}

Querying JSON Columns

LINQ Queries on JSON Properties

// Query nested JSON property var featuredArticles = await context.Articles .Where(a => a.Fields.IsFeatured == true) .ToListAsync();

// Query nested object property var articlesByAuthor = await context.Articles .Where(a => a.Fields.Author!.AuthorId == authorId) .ToListAsync();

// Query array contains var taggedArticles = await context.Articles .Where(a => a.Fields.Tags.Contains("technology")) .ToListAsync();

// Order by JSON property var orderedArticles = await context.Articles .OrderByDescending(a => a.Fields.ReadTimeMinutes) .ToListAsync();

Raw SQL for Complex JSON Queries

// SQL Server JSON_VALUE var results = await context.ContentItems .FromSqlRaw(@" SELECT * FROM ContentItems WHERE JSON_VALUE(Extensions, '$.CustomFields.rating') > 4 ") .ToListAsync();

// PostgreSQL jsonb operators var results = await context.ContentItems .FromSqlRaw(@" SELECT * FROM ""ContentItems"" WHERE ""Extensions""->>'CustomFields'->>'category' = 'tech' ") .ToListAsync();

Indexing Strategies

Computed Columns for Frequently Queried JSON Properties

-- SQL Server: Add computed column ALTER TABLE ContentItems ADD Status AS JSON_VALUE(Extensions, '$.status') PERSISTED;

-- Create index on computed column CREATE INDEX IX_ContentItems_Status ON ContentItems(Status);

PostgreSQL GIN Index for JSONB

-- Index entire JSON column CREATE INDEX IX_ContentItems_Extensions ON "ContentItems" USING GIN ("Extensions");

-- Index specific path CREATE INDEX IX_ContentItems_Tags ON "ContentItems" USING GIN (("Extensions"->'CustomFields'->'tags'));

EF Core Migration for Computed Column

migrationBuilder.Sql(@" ALTER TABLE ContentItems ADD ComputedStatus AS JSON_VALUE(Extensions, '$.SeoPart.noIndex') PERSISTED;

CREATE INDEX IX_ContentItems_ComputedStatus
ON ContentItems(ComputedStatus);

");

Schema Evolution

Adding New Fields

No migration required - just update the class and serialize:

// Before public class ArticleFields { public string? Subtitle { get; set; } }

// After - no migration needed public class ArticleFields { public string? Subtitle { get; set; } public string? Summary { get; set; } // New field public List<string> RelatedLinks { get; set; } = new(); // New field }

Handling Missing/Null Properties

// Use nullable types with defaults public class ContentFields { public string? OptionalField { get; set; } public int RequiredWithDefault { get; set; } = 0; public List<string> CollectionWithDefault { get; set; } = new(); }

// Query with null handling var items = await context.ContentItems .Where(c => c.Extensions.CustomFields != null && c.Extensions.CustomFields.ContainsKey("rating")) .ToListAsync();

Data Migration for Schema Changes

// Background job to migrate existing data public async Task MigrateContentSchema(ContentDbContext context) { var batchSize = 100; var skip = 0;

while (true)
{
    var items = await context.ContentItems
        .OrderBy(c => c.Id)
        .Skip(skip)
        .Take(batchSize)
        .ToListAsync();

    if (!items.Any()) break;

    foreach (var item in items)
    {
        // Transform old schema to new
        if (item.Extensions.CustomFields.TryGetValue("old_field", out var value))
        {
            item.Extensions.CustomFields["new_field"] = value;
            item.Extensions.CustomFields.Remove("old_field");
        }
    }

    await context.SaveChangesAsync();
    skip += batchSize;
}

}

Performance Considerations

When to Use JSON Columns

Scenario Use JSON Column Use Regular Column

Frequently filtered/sorted No Yes

Rarely queried, display only Yes No

Variable per content type Yes No

Fixed across all instances No Yes

Part of unique constraint No Yes

Full-text search needed Consider Yes

Best Practices

DO:

• Use hybrid approach (fixed + JSON)
• Index frequently queried JSON paths
• Use typed DTOs when schema is known
• Validate JSON structure in application layer
• Use pagination for large JSON arrays

DON'T:

• Store large binary data in JSON
• Use JSON for foreign key relationships
• Rely solely on JSON queries for performance-critical paths
• Store deeply nested hierarchies (flatten when possible)
• Skip schema validation for user-supplied data

Serialization Configuration

System.Text.Json Options

services.AddDbContext<ContentDbContext>(options => { options.UseSqlServer(connectionString, sqlOptions => { // Configure JSON serialization }); });

// Custom JsonSerializerOptions public static class JsonDefaults { public static JsonSerializerOptions ContentOptions { get; } = new() { PropertyNamingPolicy = JsonNamingPolicy.CamelCase, DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull, Converters = { new JsonStringEnumConverter(JsonNamingPolicy.CamelCase) } }; }

Custom Type Converters

// For complex types not directly serializable public class PolymorphicFieldConverter : JsonConverter<object> { public override object? Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options) { using var doc = JsonDocument.ParseValue(ref reader); var root = doc.RootElement;

    // Determine type from discriminator
    if (root.TryGetProperty("$type", out var typeElement))
    {
        var typeName = typeElement.GetString();
        // Resolve and deserialize appropriate type
    }

    return root.Clone();
}

public override void Write(Utf8JsonWriter writer,
    object value, JsonSerializerOptions options)
{
    JsonSerializer.Serialize(writer, value, value.GetType(), options);
}

}

Content Part JSON Storage

Storing Multiple Parts in Single JSON Column

public class ContentItem { public Guid Id { get; set; } public string ContentType { get; set; } = string.Empty;

// All parts stored in single JSON column
public ContentParts Parts { get; set; } = new();

}

public class ContentParts { public TitlePart? Title { get; set; } public BodyPart? Body { get; set; } public AutoroutePart? Autoroute { get; set; } public PublishLaterPart? PublishLater { get; set; } public SeoMetaPart? SeoMeta { get; set; }

// Extension point for custom parts
public Dictionary<string, JsonElement> CustomParts { get; set; } = new();

}

// Part definitions public record TitlePart(string Title, string? DisplayTitle = null); public record BodyPart(string Html, string? PlainText = null); public record AutoroutePart(string Path, bool IsCustom = false); public record PublishLaterPart(DateTime? ScheduledUtc, string? TimeZone = null); public record SeoMetaPart(string? MetaTitle, string? MetaDescription, bool NoIndex = false);

Validation Patterns

Fluent Validation for JSON Fields

public class ContentItemValidator : AbstractValidator<ContentItem> { public ContentItemValidator(IContentTypeRegistry typeRegistry) { RuleFor(x => x.Title).NotEmpty().MaximumLength(200);

    RuleFor(x => x.Extensions)
        .SetValidator(new ContentExtensionsValidator(typeRegistry));
}

}

public class ContentExtensionsValidator : AbstractValidator<ContentExtensions> { public ContentExtensionsValidator(IContentTypeRegistry typeRegistry) { When(x => x.SeoPart != null, () => { RuleFor(x => x.SeoPart!.MetaTitle) .MaximumLength(60) .When(x => x.SeoPart?.MetaTitle != null);

        RuleFor(x => x.SeoPart!.MetaDescription)
            .MaximumLength(160)
            .When(x => x.SeoPart?.MetaDescription != null);
    });
}

}

Related Skills

• content-type-modeling

• Content Type hierarchy and composition

• content-versioning

• Version history with JSON snapshots

• headless-api-design

• API contracts for JSON-based content