Testcontainers — Real Databases in Docker for Tests

Behavioral differences between in-memory and SQL Server:
  SQL constraints:    In-memory ignores FK constraints — referential integrity bugs pass
  Case sensitivity:   In-memory is case-sensitive, SQL Server is not by default
  String operations:  LIKE, Contains behavior differs between providers
  Transactions:       In-memory has no real transaction isolation
  Stored procedures:  In-memory cannot run them
  JSON columns:       In-memory does not support them
  EF Core raw SQL:    In-memory ignores ExecuteSqlRaw calls
  Migrations:         In-memory database has no schema — migrations not tested

<!-- tests/Integration.Tests/Integration.Tests.csproj -->
<PackageReference Include="Testcontainers.MsSql"  Version="3.*" />
<PackageReference Include="Testcontainers.Redis"   Version="3.*" />
<PackageReference Include="Microsoft.NET.Test.Sdk" Version="17.*" />

// tests/Integration.Tests/Patients/PatientRepositoryTests.cs
public class PatientRepositoryTests : IAsyncLifetime
{
    private readonly MsSqlContainer _sql = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
        .WithPassword("YourStrong@Passw0rd!")
        .Build();

    private AppDbContext      _context  = null!;
    private PatientRepository _sut      = null!;

    public async Task InitializeAsync()
    {
        await _sql.StartAsync();

        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_sql.GetConnectionString())
            .Options;

        _context = new AppDbContext(options, new NoOpPublisher());

        // Run real migrations — same as production
        await _context.Database.MigrateAsync();

        _sut = new PatientRepository(_context);
    }

    [Fact]
    public async Task AddAsync_should_persist_and_GetById_should_retrieve()
    {
        // Arrange
        var patient = Patient.Create(
            "John Smith", new DateOnly(1985, 3, 15), "MRN-001").Value;

        // Act
        await _sut.AddAsync(patient, CancellationToken.None);
        await _context.SaveChangesAsync();

        // Fresh context — proves data is actually in DB, not EF's change tracker
        await using var freshCtx = new AppDbContext(
            new DbContextOptionsBuilder<AppDbContext>()
                .UseSqlServer(_sql.GetConnectionString()).Options,
            new NoOpPublisher());
        var retrieved = await new PatientRepository(freshCtx)
            .GetByIdAsync(patient.Id, CancellationToken.None);

        // Assert
        retrieved.Should().NotBeNull();
        retrieved!.MRN.Should().Be("MRN-001");
        retrieved.Name.Should().Be("John Smith");
    }

    [Fact]
    public async Task ExistsByMRNAsync_should_return_true_for_existing_mrn()
    {
        // Arrange
        var patient = Patient.Create(
            "Jane Doe", new DateOnly(1990, 7, 22), "MRN-002").Value;
        await _sut.AddAsync(patient, CancellationToken.None);
        await _context.SaveChangesAsync();

        // Act
        var exists = await _sut.ExistsByMRNAsync("MRN-002", CancellationToken.None);

        // Assert
        exists.Should().BeTrue();
    }

    public async Task DisposeAsync()
    {
        await _context.DisposeAsync();
        await _sql.DisposeAsync();
    }
}

// Shared factory with Testcontainers — one container per test class
public sealed class SystemForgeWebApplicationFactory
    : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly MsSqlContainer _sql = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
        .WithPassword("Test@123Strong!")
        .Build();

    private readonly RedisContainer _redis = new RedisBuilder()
        .WithImage("redis:7-alpine")
        .Build();

    public async Task InitializeAsync()
    {
        await Task.WhenAll(_sql.StartAsync(), _redis.StartAsync());
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureTestServices(services =>
        {
            // Replace DB connection string with Testcontainers'
            services.RemoveAll<DbContextOptions<AppDbContext>>();
            services.AddDbContext<AppDbContext>(options =>
                options.UseSqlServer(_sql.GetConnectionString()));

            // Replace Redis with Testcontainers' Redis
            services.Configure<RedisCacheOptions>(opts =>
                opts.Configuration = _redis.GetConnectionString());

            // Run migrations as part of test setup
            using var scope = services.BuildServiceProvider().CreateScope();
            var db    = scope.ServiceProvider.GetRequiredService<AppDbContext>();
            db.Database.Migrate();
        });
    }

    public new async Task DisposeAsync()
    {
        await _sql.DisposeAsync();
        await _redis.DisposeAsync();
        await base.DisposeAsync();
    }
}

Per-test (IAsyncLifetime on test class):
  Container starts and stops for each test class
  Pros: perfect isolation (fresh DB)
  Cons: slow — container startup adds 10-20 seconds per class

Per-collection (ICollectionFixture):
  One container shared across all tests in the collection
  Pros: fast — one startup per test run
  Cons: tests must manage their own data isolation (unique IDs, cleanup)

Per-test-run (global fixture):
  One container for the entire test suite
  Pros: fastest
  Cons: highest risk of test interference — requires careful isolation

// Recommended: collection fixture — shared but controlled
[CollectionDefinition("Integration")]
public class IntegrationCollection
    : ICollectionFixture<SystemForgeWebApplicationFactory> { }

[Collection("Integration")]
public class PatientsApiTests
{
    private readonly SystemForgeWebApplicationFactory _factory;
    // ...
}

// Option 1: Unique data per test (no cleanup needed)
[Fact]
public async Task Create_patient_with_unique_mrn()
{
    var uniqueMRN = $"MRN-{Guid.NewGuid():N}";  // guaranteed unique
    var response = await _client.PostAsJsonAsync("/api/patients", new
    {
        Name = "Test Patient", DateOfBirth = "1985-01-01", MRN = uniqueMRN
    });
    response.StatusCode.Should().Be(HttpStatusCode.Created);
}

// Option 2: Transaction rollback per test
public async Task InitializeAsync()
{
    _transaction = await _context.Database.BeginTransactionAsync();
}

public async Task DisposeAsync()
{
    await _transaction.RollbackAsync();  // undo all changes from this test
}

# .github/workflows/ci.yml
- name: Integration Tests
  run: dotnet test tests/Integration.Tests --configuration Release
  # Docker is available on GitHub Actions ubuntu runners
  # Testcontainers auto-discovers the Docker socket
  env:
    TESTCONTAINERS_RYUK_DISABLED: "true"   # optional: disable resource reaper