entity-framework

Entity Framework Core Patterns

Data Access Patterns

• MUST use repository and unit of work patterns for data access abstraction
• MUST use eager loading with Include() to prevent N+1 query problems
• MUST apply AsNoTracking() for read-only queries to optimize performance
• MUST configure entities using Fluent API (AVOID data annotations)
• SHOULD implement compiled queries for frequently executed operations

GUID Primary Keys

MUST use Guid.CreateVersion7() for all GUID generation (NOT Guid.NewGuid())

Why Version 7 GUIDs?

• Time-ordered (sequential) - better database performance
• Improved indexing and clustering in PostgreSQL
• Reduces index fragmentation
• Better page splits behavior

Entity Configuration:

public class ApplicationUser
{
    public Guid Id { get; init; }  // Don't set default value here
    // ... other properties
}

// In entity configuration
builder.HasKey(x => x.Id);
builder.Property(x => x.Id).ValueGeneratedNever();  // Application controls GUID generation

Creating Entities:

// ✅ CORRECT: Use CreateVersion7()
var user = new ApplicationUser
{
    Id = Guid.CreateVersion7(),  // Time-ordered GUID
    Email = "user@example.com",
    // ...
};

// ❌ WRONG: Don't use NewGuid()
var user = new ApplicationUser
{
    Id = Guid.NewGuid(),  // Random GUID - worse performance
    // ...
};

Type-Safe JSON Properties (CRITICAL)

NEVER use string properties for JSON data

❌ WRONG:

public class Feed {
    public string Selectors { get; set; }  // Don't do this!
}

✅ CORRECT:

// Create strongly-typed model in Models/ directory
public class FeedSelectors {
    public string Title { get; set; }
    public string Content { get; set; }
}

public class Feed {
    public FeedSelectors Selectors { get; set; }  // Type-safe!
}

// Configure with Fluent API
builder.OwnsOne(x => x.Selectors, b => b.ToJson());

Benefits: Compile-time type safety, IntelliSense support, better maintainability

Entity Configuration Organization

MUST create separate configuration classes for each entity

Location: src/RSSVibe.Data/Configurations/ (note: plural "Configurations")

Pattern:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using RSSVibe.Data.Entities;

namespace RSSVibe.Data.Configurations;

/// <summary>
/// Entity Framework configuration for MyEntity.
/// </summary>
internal sealed class MyEntityConfiguration : IEntityTypeConfiguration<MyEntity>
{
    public void Configure(EntityTypeBuilder<MyEntity> builder)
    {
        builder.ToTable("MyEntities");

        // Configure properties, indexes, relationships, etc.
    }
}

DbContext Integration:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    // Automatically discovers and applies all IEntityTypeConfiguration implementations
    builder.ApplyConfigurationsFromAssembly(typeof(RssVibeDbContext).Assembly);
}

Key Points:

• Configuration classes are internal sealed
• Located in Configurations folder (plural, not "Configuration")
• Automatically discovered via ApplyConfigurationsFromAssembly()
• No manual registration needed in DbContext

Minimal Configuration Approach

ONLY configure what EF Core cannot infer automatically

❌ AVOID (EF Core infers these automatically):

.HasColumnName("id")
.HasColumnType("text")
.IsRequired()  // for non-nullable properties
.ToTable("feed")

✅ DO (meaningful business rules only):

.HasMaxLength(200)
.HasDefaultValue(60)
.HasDefaultValueSql("now()")
.HasCheckConstraint("check_positive", "value > 0")
.ValueGeneratedNever()  // for GUIDs

• ONLY specify column types for database-specific features (e.g., jsonb, text[] for PostgreSQL)

Transaction Handling with PostgreSQL

CRITICAL: MUST use execution strategy for all manual transactions in PostgreSQL

PostgreSQL provider requires wrapping transactions in an execution strategy for proper retry handling and transaction management.

Pattern: Execution Strategy with Transactions

✅ CORRECT (recommended - with state parameters to avoid closures):

// 1. Create execution strategy from DbContext
var strategy = dbContext.Database.CreateExecutionStrategy();

// 2. Prepare state tuple to avoid closures
var state = (entity, newEntity, dbContext, logger);

// 3. Use ExecuteAsync with state parameters
return await strategy.ExecuteAsync(
    state,
    static async (state, ct) =>
    {
        var (entity, newEntity, dbContext, logger) = state;

        await using var transaction = await dbContext.Database.BeginTransactionAsync(ct);

        try
        {
            // Perform operations
            entity.SomeProperty = newValue;
            dbContext.SomeEntities.Add(newEntity);

            await dbContext.SaveChangesAsync(ct);
            await transaction.CommitAsync(ct);

            logger.LogInformation("Transaction completed successfully");
            return successResult;
        }
        catch
        {
            await transaction.RollbackAsync(ct);
            throw;
        }
    },
    cancellationToken);

✅ ALSO CORRECT (without state parameters - uses closures):

var strategy = dbContext.Database.CreateExecutionStrategy();

return await strategy.ExecuteAsync(async () =>
{
    await using var transaction = await dbContext.Database.BeginTransactionAsync(cancellationToken);

    try
    {
        // Perform operations
        entity.SomeProperty = newValue;
        dbContext.SomeEntities.Add(newEntity);

        await dbContext.SaveChangesAsync(cancellationToken);
        await transaction.CommitAsync(cancellationToken);

        return successResult;
    }
    catch
    {
        await transaction.RollbackAsync(cancellationToken);
        throw;
    }
});

❌ WRONG (will cause issues in PostgreSQL):

// Don't use transactions directly without execution strategy
await using var transaction = await dbContext.Database.BeginTransactionAsync(cancellationToken);
try
{
    // operations...
    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

Why Execution Strategy?

• PostgreSQL-specific requirement: The Npgsql provider needs execution strategy for transaction retry logic
• Handles transient failures: Automatically retries on connection issues
• Prevents deadlocks: Proper transaction scope management
• Testing compatibility: Works correctly in test scenarios with in-memory database state

Best Practices

Always use state parameters to avoid closures:

• Pass dependencies as state tuple
• Use static async lambda to prevent accidental closure capture
• Improves performance by avoiding closure allocations
• Makes dependencies explicit

Use ExecuteAsync with manual transactions when returning values:

• ExecuteInTransactionAsync is ideal for void operations (no return value)
• For operations that return results, use ExecuteAsync with manual transaction handling
• Always wrap in await using var transaction with try-catch-rollback pattern
• Use await using for proper async disposal of IAsyncDisposable resources
• This is the recommended approach for PostgreSQL with Npgsql provider

When to Use

MUST use execution strategy when:

• Creating manual transactions with BeginTransactionAsync()
• Performing multiple operations that must be atomic
• Implementing complex business logic requiring rollback capability

NOT needed for:

• Simple SaveChangesAsync() calls (already wrapped in implicit transaction)
• Read-only operations
• Single entity modifications

Migration Management

MUST use the migration script: src/RSSVibe.Data/add_migration.sh

# Navigate to Data project directory
cd src/RSSVibe.Data

# Create migration (use PascalCase)
bash add_migration.sh AddUserPreferences

Important:

• MUST execute script from src/RSSVibe.Data/ directory
• MUST review generated migration file before applying
• NEVER modify migration files manually (remove and regenerate instead)
• Script handles correct project and startup project paths automatically