JSON Columns and Outbox Pattern

Before diving on the subject of this article I would like to thanks the sponsor of this article: ZZZ Project and his product Entity Framework Extension

In my previous article on the Outbox Pattern, we built a robust solution for reliable event publishing in distributed systems. But there's a game-changing enhancement that arrived with SQL Server 2025 and EF Core 10: native JSON columns with complex type support.

Today, I'll show you how to leverage these new features to:

  • ✅ Store rich, flexible event metadata without schema migrations
  • ✅ Improve query performance with native JSON indexing
  • ✅ Simplify event versioning and backward compatibility
  • ✅ Enable powerful event analytics and monitoring

Let's dive into what changes when you upgrade to JSON columns!

The Problem with Traditional Outbox

In the traditional outbox pattern, we stored events as plain JSON strings:

// ❌ Traditional Approach
public class OutboxMessage
{
    public Guid Id { get; set; }
    public string EventType { get; set; }
    public string EventData { get; set; }  // ❌ Plain string - hard to query
    public DateTime CreatedAt { get; set; }
    public DateTime? ProcessedAt { get; set; }
    public int RetryCount { get; set; }
    public string? Error { get; set; }
}

Problems:

  • 🔴 Can't query event properties without string parsing
  • 🔴 No type safety for event metadata
  • 🔴 Poor performance filtering by event attributes
  • 🔴 Difficult to build analytics
  • 🔴 Adding new metadata requires schema changes

What JSON Columns Change

With SQL Server 2025's native json data type and EF Core 10's complex types:

// ✅ JSON Columns Approach
public class OutboxMessage
{
    public Guid Id { get; set; }
    public string EventType { get; set; }
    
    // ✨ NEW: Complex types mapped to JSON columns
    public required EventMetadata Metadata { get; set; }
    public required ProcessingState State { get; set; }
    
    public string PayloadJson { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

// Type-safe metadata structure
public class EventMetadata
{
    public Guid EventId { get; set; }
    public string Version { get; set; } = "1.0";
    public string? CorrelationId { get; set; }
    public string? TenantId { get; set; }
    public string? InitiatedBy { get; set; }
    public DateTimeOffset OccurredAt { get; set; }
}

// Type-safe processing state
public class ProcessingState
{
    public OutboxMessageStatus Status { get; set; }
    public int RetryCount { get; set; }
    public DateTimeOffset? ProcessedAt { get; set; }
    public ErrorDetails? LastError { get; set; }
}

Benefits:

  • ✅ Type-safe queries on nested properties
  • ✅ 40-60% faster reads with native JSON parsing
  • ✅ Index JSON properties for performance
  • ✅ Add properties without schema migrations
  • ✅ ExecuteUpdate support for bulk operations

Key Change #1: EF Core Configuration

Before (Traditional)

modelBuilder.Entity<OutboxMessage>(entity =>
{
    entity.HasKey(e => e.Id);
    entity.Property(e => e.EventData).IsRequired();
    entity.Property(e => e.Error).HasMaxLength(2000);
    
    // Limited indexing - can only index scalar columns
    entity.HasIndex(e => e.ProcessedAt)
          .HasFilter("[ProcessedAt] IS NULL");
});

After (JSON Columns)

modelBuilder.Entity<OutboxMessage>(entity =>
{
    entity.HasKey(e => e.Id);
    
    // ✨ NEW: Map complex types to JSON columns
    entity.ComplexProperty(e => e.Metadata, c => c.ToJson());
    entity.ComplexProperty(e => e.State, c => c.ToJson());
    
    // ✨ NEW: Index JSON properties directly!
    entity.HasIndex(e => e.State.Status);
    entity.HasIndex(e => e.Metadata.CorrelationId);
    entity.HasIndex(e => new { e.State.Status, e.CreatedAt });
});

What changed:

  • .ComplexProperty() with .ToJson() tells EF Core to use JSON columns
  • Can create indexes on properties inside JSON columns
  • EF Core handles JSON serialization automatically

Key Change #2: Querying

Before (Traditional)

// ❌ Can't filter on JSON properties - must load all and filter in memory
var messages = await dbContext.OutboxMessages
    .Where(m => m.ProcessedAt == null)
    .ToListAsync();

// Then deserialize and filter in memory (inefficient!)
var filtered = messages
    .Where(m => {
        var data = JsonSerializer.Deserialize<EventData>(m.EventData);
        return data?.CorrelationId == "abc-123";
    })
    .ToList();

After (JSON Columns)

// ✅ Query JSON properties directly in the database!
var messages = await dbContext.OutboxMessages
    .Where(m => m.State.Status == OutboxMessageStatus.Pending &&
                m.Metadata.CorrelationId == "abc-123")
    .ToListAsync();

// ✅ Complex queries work seamlessly
var problematic = await dbContext.OutboxMessages
    .Where(m => m.State.RetryCount > 3 &&
                m.Metadata.TenantId == "tenant-1")
    .OrderByDescending(m => m.State.RetryCount)
    .ToListAsync();

What changed:

  • Direct access to nested JSON properties in LINQ
  • Database-side filtering (not in-memory)
  • Index support makes queries fast

Key Change #3: Inserting Data

Before (Traditional)

// ❌ Manual JSON serialization
var eventData = JsonSerializer.Serialize(domainEvent);

var message = new OutboxMessage
{
    Id = Guid.NewGuid(),
    EventType = typeof(TEvent).FullName,
    EventData = eventData,  // Manual string
    CreatedAt = DateTime.UtcNow,
    ProcessedAt = null,
    Error = null,
    RetryCount = 0
};

await dbContext.OutboxMessages.AddAsync(message);

After (JSON Columns)

// ✅ Type-safe object creation
var message = new OutboxMessage
{
    Id = Guid.NewGuid(),
    EventType = typeof(TEvent).FullName,
    Metadata = new EventMetadata  // Type-safe!
    {
        EventId = domainEvent.EventId,
        CorrelationId = httpContext.TraceIdentifier,
        TenantId = GetTenantId(),
        InitiatedBy = GetUserId(),
        OccurredAt = DateTimeOffset.UtcNow
    },
    State = new ProcessingState
    {
        Status = OutboxMessageStatus.Pending,
        RetryCount = 0
    },
    PayloadJson = JsonSerializer.Serialize(domainEvent),
    CreatedAt = DateTimeOffset.UtcNow
};

await dbContext.OutboxMessages.AddAsync(message);
// EF Core serializes Metadata and State to JSON automatically!

What changed:

  • No manual JSON serialization for metadata
  • Type-safe property access
  • IntelliSense support
  • Compiler checks for errors

Key Change #4: Updating Data

Before (Traditional)

// ❌ Update scalar fields only
var message = await dbContext.OutboxMessages.FindAsync(id);
message.ProcessedAt = DateTime.UtcNow;
message.Error = null;
message.RetryCount++;

await dbContext.SaveChangesAsync();

After (JSON Columns)

// ✅ Update nested JSON properties
var message = await dbContext.OutboxMessages.FindAsync(id);
message.State.Status = OutboxMessageStatus.Processed;
message.State.ProcessedAt = DateTimeOffset.UtcNow;
message.State.RetryCount++;
message.State.LastError = new ErrorDetails
{
    Message = ex.Message,
    StackTrace = ex.StackTrace,
    Timestamp = DateTimeOffset.UtcNow
};

await dbContext.SaveChangesAsync();

// ✨ NEW: Bulk updates on JSON properties (EF Core 10!)
await dbContext.OutboxMessages
    .Where(m => m.State.Status == OutboxMessageStatus.Failed)
    .ExecuteUpdateAsync(s => s
        .SetProperty(m => m.State.Status, OutboxMessageStatus.Pending)
        .SetProperty(m => m.State.LastError, (ErrorDetails?)null));

What changed:

  • Update nested properties with type safety
  • EF Core tracks changes in JSON columns
  • ExecuteUpdateAsync now supports JSON properties for efficient bulk operations
  • No need to load entities into memory for updates

Conclusion: Should You Upgrade?

The combination of SQL Server 2025's native JSON data type and EF Core 10's complex types represents a significant leap forward for the Outbox Pattern. Here's what you gain:

Performance Wins

The performance improvements are substantial. Queries on event metadata are 40-60% faster thanks to SQL Server's native JSON parsing capabilities. Native JSON indexing eliminates table scans, allowing the database to efficiently locate events based on metadata properties. Bulk operations with ExecuteUpdate on JSON properties enable high-throughput scenarios, and database-side filtering reduces memory usage by eliminating the need to load and filter data in application memory.

Developer Experience

From a development perspective, the benefits are transformative. You get type-safe metadata and state management with full IntelliSense and compile-time checking, catching errors before they reach production. Manual JSON serialization and deserialization become a thing of the past, as EF Core handles it automatically. The result is cleaner, more maintainable code that's easier to understand and refactor.

Operational Benefits

Operationally, JSON columns provide remarkable flexibility. You can add metadata fields without schema migrations, enabling rapid iteration and deployment. Event versioning and backward compatibility become simpler to manage since you can evolve your event structure without breaking existing data. Rich analytics queries on event properties unlock powerful monitoring and debugging capabilities, making it easier to understand system behavior and troubleshoot issues in production.

When to Migrate

Migrate if:

  • ✅ You're starting a new project with .NET 9+
  • ✅ You query event metadata frequently
  • ✅ You need analytics on event properties
  • ✅ You have multi-tenant scenarios requiring filtering
  • ✅ Your event metadata evolves frequently

Wait if:

  • ⏸️ You're on SQL Server 2022 or earlier (no native JSON support)
  • ⏸️ Your events are simple and rarely queried
  • ⏸️ Migration complexity outweighs benefits

See you on www.devskillsunlock.com for more !