dynamic-schema-design

What this skill does

# 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)

```csharp
// 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

```csharp
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.

```csharp
// 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.

```csharp
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.

```csharp
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

```csharp
// 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

```csharp
// 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
-- 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

```sql
-- 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

```csharp
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:

```csharp
// 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

```csharp
// 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

```csharp
// 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))