Dynamic Schema Design

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

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

Converted and distributed by TomeVault — claim your Tome and manage your conversions.