---

name: data-access description: Implement data access for the .NET 8 WPF widget host app using EF Core or Dapper. Use when creating repositories, unit of work, migrations, DbContext configuration, and query patterns while keeping clean architecture boundaries.

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

Copy// ✅ 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

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

Repository Pattern

Interface Definition (Application Layer)

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

Copypublic 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:

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

Copy// ✅ 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

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

Copy// 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

Copy// 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

Testing

Copypublic 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