Data Access

Overview

Define reliable persistence patterns using EF Core while maintaining clean architecture boundaries between Infrastructure and Application layers.

Constraints

• .NET 8 with EF Core 8
• SQLite database at %LocalAppData%\3SC\3sc.db
• Clean architecture boundaries
• Repository pattern with Unit of Work

Definition of Done (DoD)

• Repository interfaces in Application layer, implementations in Infrastructure
• DbContext created per operation (factory pattern) - NOT singleton
• Read queries use AsNoTracking() for performance
• Write operations use explicit SaveChangesAsync() via Unit of Work
• No raw SQL outside Infrastructure layer
• Integration tests exist for repository operations
• Transient database errors are handled with retry logic

Database Configuration

DbContext Factory Pattern

// ✅ GOOD - Factory creates context per operation
public interface IDbContextFactory
{
    AppDbContext CreateDbContext();
}

public class SqliteDbContextFactory : IDbContextFactory
{
    private readonly string _connectionString;
    
    public SqliteDbContextFactory(string dbPath)
    {
        _connectionString = $"Data Source={dbPath}";
    }
    
    public AppDbContext CreateDbContext()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connectionString)
            .Options;
        return new AppDbContext(options);
    }
}

// Usage in repository
public async Task<Widget?> GetByKeyAsync(string key, CancellationToken ct)
{
    await using var context = _factory.CreateDbContext();
    return await context.Widgets
        .AsNoTracking()
        .FirstOrDefaultAsync(w => w.WidgetKey == key, ct);
}

Connection String

// Standard location
var dbPath = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),
    "3SC", "3sc.db");

Repository Pattern

Interface Definition (Application Layer)

public interface IWidgetRepository
{
    Task<Widget?> GetByKeyAsync(string widgetKey, CancellationToken ct = default);
    Task<IReadOnlyList<Widget>> GetAllAsync(CancellationToken ct = default);
    Task<IReadOnlyList<Widget>> SearchAsync(string query, CancellationToken ct = default);
    Task AddAsync(Widget widget, CancellationToken ct = default);
    Task UpdateAsync(Widget widget, CancellationToken ct = default);
    Task DeleteAsync(string widgetKey, CancellationToken ct = default);
    Task<bool> ExistsAsync(string widgetKey, CancellationToken ct = default);
}

Implementation (Infrastructure Layer)

public class WidgetRepository : IWidgetRepository
{
    private readonly IDbContextFactory _factory;
    
    public WidgetRepository(IDbContextFactory factory)
    {
        _factory = factory;
    }
    
    public async Task<IReadOnlyList<Widget>> GetAllAsync(CancellationToken ct = default)
    {
        await using var context = _factory.CreateDbContext();
        return await context.Widgets
            .AsNoTracking()
            .OrderBy(w => w.DisplayName)
            .ToListAsync(ct);
    }
    
    public async Task<IReadOnlyList<Widget>> SearchAsync(string query, CancellationToken ct = default)
    {
        await using var context = _factory.CreateDbContext();
        
        // Use EF.Functions for case-insensitive search
        return await context.Widgets
            .AsNoTracking()
            .Where(w => EF.Functions.Like(w.DisplayName, $"%{query}%") ||
                        EF.Functions.Like(w.Description ?? "", $"%{query}%"))
            .OrderBy(w => w.DisplayName)
            .ToListAsync(ct);
    }
    
    public async Task AddAsync(Widget widget, CancellationToken ct = default)
    {
        await using var context = _factory.CreateDbContext();
        context.Widgets.Add(widget);
        await context.SaveChangesAsync(ct);
    }
}

Unit of Work

For operations spanning multiple repositories:

public interface IUnitOfWork : IDisposable
{
    IWidgetRepository Widgets { get; }
    IWidgetInstanceRepository WidgetInstances { get; }
    ILayoutRepository Layouts { get; }
    
    Task<int> SaveChangesAsync(CancellationToken ct = default);
    Task BeginTransactionAsync(CancellationToken ct = default);
    Task CommitAsync(CancellationToken ct = default);
    Task RollbackAsync();
}

// Usage
await using var uow = _unitOfWorkFactory.Create();
await uow.Widgets.AddAsync(widget, ct);
await uow.WidgetInstances.AddAsync(instance, ct);
await uow.SaveChangesAsync(ct);

Query Patterns

Projections (Recommended for Read)

// ✅ GOOD - Select only needed fields
public async Task<IReadOnlyList<WidgetSummary>> GetSummariesAsync(CancellationToken ct)
{
    await using var context = _factory.CreateDbContext();
    return await context.Widgets
        .AsNoTracking()
        .Select(w => new WidgetSummary
        {
            WidgetKey = w.WidgetKey,
            DisplayName = w.DisplayName,
            InstanceCount = w.Instances.Count
        })
        .ToListAsync(ct);
}

Pagination

public async Task<PagedResult<Widget>> GetPagedAsync(
    int page, int pageSize, CancellationToken ct = default)
{
    await using var context = _factory.CreateDbContext();
    var query = context.Widgets.AsNoTracking();
    
    var totalCount = await query.CountAsync(ct);
    var items = await query
        .OrderBy(w => w.DisplayName)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync(ct);
    
    return new PagedResult<Widget>(items, totalCount, page, pageSize);
}

Includes (Use Sparingly)

// Only when you need related data
public async Task<Widget?> GetWithInstancesAsync(string key, CancellationToken ct)
{
    await using var context = _factory.CreateDbContext();
    return await context.Widgets
        .Include(w => w.Instances)
        .AsNoTracking()
        .FirstOrDefaultAsync(w => w.WidgetKey == key, ct);
}

SQLite Resilience

// Handle SQLITE_BUSY and SQLITE_LOCKED
public async Task<int> SaveChangesWithRetryAsync(
    DbContext context, CancellationToken ct, int maxRetries = 3)
{
    var delay = TimeSpan.FromMilliseconds(50);
    
    for (int attempt = 0; attempt <= maxRetries; attempt++)
    {
        try
        {
            return await context.SaveChangesAsync(ct);
        }
        catch (DbUpdateException ex) 
            when (ex.InnerException is SqliteException { SqliteErrorCode: 5 or 6 })
        {
            if (attempt == maxRetries) throw;
            
            Log.Warning("Database busy, retrying in {Delay}ms", delay.TotalMilliseconds);
            await Task.Delay(delay, ct);
            delay *= 2;  // Exponential backoff
        }
    }
    
    throw new InvalidOperationException("Should not reach here");
}

Anti-Patterns

Anti-Pattern	Problem	Solution
Singleton DbContext	Concurrency issues, stale data	Factory pattern, context per operation
Missing AsNoTracking	Unnecessary memory, slower reads	Add AsNoTracking for read queries
ToList() in Where clause	Loads all data to memory	Keep query as IQueryable
N+1 queries	Multiple DB roundtrips	Use Include or projections
String interpolation in queries	SQL injection risk	Use parameterized queries

Testing

public class WidgetRepositoryTests : IDisposable
{
    private readonly AppDbContext _context;
    private readonly WidgetRepository _repository;
    
    public WidgetRepositoryTests()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite("DataSource=:memory:")
            .Options;
        
        _context = new AppDbContext(options);
        _context.Database.OpenConnection();
        _context.Database.EnsureCreated();
        
        var factory = new TestDbContextFactory(_context);
        _repository = new WidgetRepository(factory);
    }
    
    public void Dispose()
    {
        _context.Database.CloseConnection();
        _context.Dispose();
    }
}

References

• references/ef-core.md for configuration and migrations
• references/repositories-uow.md for repository patterns
• EF Core Performance