SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsdotnet-testing-advanced-testcontainers-database

dotnet-testing-advanced-testcontainers-database

Testing & quality

View repository

Updated 10-16 06:22

About this skill

Testcontainers Database Integration Testing Guide

Limitations of EF Core InMemory

Before choosing a testing strategy, you must understand the significant limitations of the EF Core InMemory database:

1. Transaction Behavior and Database Locking

  • Does not support database transactions (Transactions)：SaveChanges() data is saved immediately afterward, and Rollback cannot be performed
  • No database locking mechanism: Cannot simulate behavior under concurrency (Concurrency) scenarios

2. LINQ Query Differences

  • Query translation differences: Some LINQ queries (complex GroupBy, JOIN, custom functions) can execute in InMemory, but may fail when translated to SQL
  • Case Sensitivity: InMemory is case-insensitive by default, but real databases depend on collation rules (Collation)
  • Insufficient performance simulation: Cannot simulate real database performance bottlenecks or index issues

3. Database-Specific Features

InMemory mode cannot test: stored procedures, Triggers, Views, foreign key constraints, check constraints, data type precision, Concurrency Tokens, etc.

Conclusion: When you need to verify complex transaction logic, concurrency handling, or database-specific behavior, you should use Testcontainers for integration testing.

Testcontainers Core Concepts

Testcontainers is a testing library that provides a lightweight and easy-to-use API for starting Docker containers, specifically for integration testing.

Core Advantages

  1. Real environment testing: Use a real database to test actual SQL syntax and database limitations
  2. Environment consistency: Ensure the test environment and production environment use the same service versions
  3. Clean test environment: Each test has an independent, clean environment, and containers are automatically cleaned up
  4. Simplified development environment: Developers only need Docker and do not need to install various services

Required Packages

<ItemGroup>
  <!-- 測試框架 -->
  <PackageReference Include="Microsoft.NET.Test.Sdk" Version="18.3.0" />
  <PackageReference Include="xunit" Version="2.9.3" />
  <PackageReference Include="xunit.runner.visualstudio" Version="3.1.5" />
  <PackageReference Include="AwesomeAssertions" Version="9.4.0" />

  <!-- Testcontainers 核心套件 -->
  <PackageReference Include="Testcontainers" Version="4.11.0" />

  <!-- 資料庫容器 -->
  <PackageReference Include="Testcontainers.PostgreSql" Version="4.11.0" />
  <PackageReference Include="Testcontainers.MsSql" Version="4.11.0" />

  <!-- Entity Framework Core -->
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.0" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.0" />
  <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.0" />

  <!-- Dapper (可選) -->
  <PackageReference Include="Dapper" Version="2.1.72" />
  <PackageReference Include="Microsoft.Data.SqlClient" Version="7.0.0" />
</ItemGroup>

Important: Use Microsoft.Data.SqlClient instead of the older System.Data.SqlClient, which provides better performance and security.

Environment Requirements

  • Windows 10 version 2004+, WSL 2 enabled, 8GB RAM (16GB+ recommended), 64GB disk space
  • Recommended Docker Desktop settings: Memory 6GB, CPUs 4 cores, Swap 2GB, Disk 64GB

Basic Container Operation Patterns

Use IAsyncLifetime to manage the container lifecycle, start the container and create the DbContext in InitializeAsync , and release resources in DisposeAsync . Supports PostgreSQL (PostgreSqlBuilder) and SQL Server (MsSqlBuilder）。

For complete code examples, refer to references/container-basics.md

Collection Fixture Pattern: Container Sharing

In large projects, creating a new container for each test class causes a serious performance bottleneck. Collection Fixture allows all test classes to share the same container,test execution time can be reduced by about 67%。

Includes SQL script externalization strategy (separating SQL files from C# code) and Wait Strategy best practices.

For the complete Collection Fixture implementation, externalized SQL scripts, and Wait Strategy examples, please refer to references/collection-fixture-and-scripts.md

EF Core Advanced Feature Testing

Covers complete test examples including multi-level related queries with Include/ThenInclude, using AsSplitQuery to avoid Cartesian products, verifying N+1 query problems, and optimizing read-only queries with AsNoTracking.

For complete code examples, please refer to references/orm-advanced-testing.md

Dapper Advanced Feature Testing

Covers complete test examples including basic CRUD test class setup, one-to-many relationship handling with QueryMultiple, and dynamic query construction with DynamicParameters.

For complete code examples, please refer to references/orm-advanced-testing.md

Repository Pattern Design Principles

Application of the Interface Segregation Principle (ISP)

  • IProductRepository: basic CRUD operation interfaces (GetAll, GetById, Add, Update, Delete)
  • IProductByEFCoreRepository: EF Core-specific advanced features (SplitQuery, BatchUpdate, NoTracking)
  • IProductByDapperRepository: Dapper-specific advanced features (multi-table queries, dynamic parameters, stored procedures)

Design Advantages

  1. Single Responsibility Principle (SRP): each interface focuses on a specific responsibility
  2. Interface Segregation Principle (ISP): users only need to depend on the interfaces they need
  3. Dependency Inversion Principle (DIP): high-level modules depend on abstractions rather than concrete implementations
  4. Test Isolation: enables precise testing of specific features

Common Issue Handling

Docker Container Startup Failure

# 檢查連接埠是否被佔用
netstat -an | findstr :5432

# 清理未使用的映像檔
docker system prune -a

ContainerNotRunningException (added in 4.8.0+)

Starting with Testcontainers 4.8.0, the default wait strategy changed to waiting for the container to enter the Running state. If the container fails to start properly, this exception is thrown. Common causes: incorrect image name, insufficient Docker resources, port conflicts.

Insufficient Memory Issues

  • Adjust Docker Desktop memory allocation
  • Limit the number of containers running simultaneously
  • Use Collection Fixture to share containers

Test Data Isolation

In Dispose , clean up data in the order of foreign key constraints (OrderItems → Orders → Products → Categories).

Output Format

  • Generate xUnit integration test classes (.cs files) using Testcontainers
  • Include IAsyncLifetime container lifecycle management code
  • Include Collection Fixture shared container configuration
  • Generate externalized SQL script files (.sql) and corresponding .csproj configuration
  • Include Repository Pattern interface and implementation examples

Reference Resources

Original Articles

The content of this skill is distilled from the "Old-School Software Engineer's Testing Training - 30-Day Challenge" series:

Official Documentation

Related Skills

  • dotnet-testing-advanced-testcontainers-nosql - NoSQL container testing (MongoDB, Redis)
  • dotnet-testing-advanced-webapi-integration-testing - Complete WebAPI integration testing
  • dotnet-testing-advanced-aspnet-integration-testing - ASP.NET Core basic integration testing
  • dotnet-testing-advanced-xunit-upgrade-guide - xUnit v3 upgrade guide (including instructions for Testcontainers.XunitV3 integration)

Note for xUnit v3 users: When using xUnit v3, you can use the Testcontainers.XunitV3(4.9.0) package to automatically manage the container lifecycle, replacing the manual implementation of IAsyncLifetime。