昨天我們學了 Testcontainers 的基礎，用 Docker 容器來建立測試環境。今天要深入實際應用場景，學習如何在真實的資料庫測試中使用 Testcontainers，並且分別展示 EF Core 和 Dapper 兩種不同資料存取技術的測試策略。
在實際的專案中，技術選擇通常在一開始就會決定。選擇 EF Core 還是 Dapper，主要會考量資料關聯的複雜度、是否有複雜的 SQL 查詢需求，以及效能要求。今天我們要學習如何在相同的測試環境下，為這兩種不同的技術建立完整的測試策略。
從 Day 20 的挑戰談起：為何需要容器共享？
MSSQL Testcontainers 環境建置
Repository Pattern 設計原則
EF Core Repository 進階測試
Dapper Repository 進階測試
在 Day 20 的範例中，我們為每一個測試類別都建立了一個新的 Testcontainer 容器。這種做法雖然確保了測試之間的完全隔離，但在大型專案中會遇到嚴重的效能瓶頸。
想像一下，如果一個測試專案中有十幾個需要資料庫的測試類別，每次執行測試時，Testcontainers 都會重複進行以下流程：
這個過程非常耗時，特別是對於像 MSSQL 這樣較為龐大的服務。正如您在 Day 20 範例中所觀察到的，一個包含 MSSQL 的測試案例，其執行時間可能輕易超過 10 秒。如果專案中有數十個這樣的測試，總執行時間將會變得難以接受，嚴重影響開發效率和 CI/CD 流程。
為了解決這個效能問題，我們需要一種機制來讓多個測試類別共享同一個容器實例。在 xUnit 測試框架中，這個機制的完美實現就是 Collection Fixture。
Collection Fixture 的核心價值：
效能大幅提升：
3 * 10 秒 = 30 秒。1 * 10 秒 = 10 秒。測試執行時間大幅減少約 67%。資源使用最佳化：
測試環境一致性：
IDisposable 或類似機制）來確保測試之間的獨立性。透過 Collection Fixture，我們可以在享受 Testcontainers 帶來便利的同時，兼顧大型專案所需的執行效率。這也是真實世界專案中整合測試的標準實踐。
在企業環境中，MSSQL 是最常見的資料庫選擇，特別是在 .NET 技術棧中。使用 Testcontainers.MsSql 可以讓我們建立與正式環境完全一致的測試環境。
為什麼選擇 MSSQL？
建立新的測試專案並安裝必要的套件：
MSSQL + EF Core + Dapper 必要套件：
xunit (2.9.3)、AwesomeAssertions (9.1.0)Microsoft.EntityFrameworkCore.SqlServer (9.0.0)Testcontainers.MsSql (4.0.0)Dapper (2.1.35)、Microsoft.Data.SqlClient (5.2.2)重要提醒：使用 Microsoft.Data.SqlClient 而不是舊版的 System.Data.SqlClient，新版本提供更好的效能、安全性和 .NET 5+ 支援。
我們設計一個基本的電商資料模型，涵蓋常見的測試場景：
核心實體與關聯：
本文使用包含 Product、Order 等多個實體的電商資料模型。為求簡潔，此處僅展示 Category 實體作為範例。
// 分類實體
public class Category
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string? Description { get; set; }
public bool IsActive { get; set; } = true;
public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
public DateTime? UpdatedAt { get; set; }
public virtual ICollection<Product> Products { get; set; } = new List<Product>();
}
// 完整實體定義請參考範例專案
實體關聯設計：
這個設計涵蓋了常見的測試場景：CRUD 操作、關聯查詢、聚合統計等。
展示 DbContext 的核心設定，重點在實體關聯和索引配置：
public class ECommerceDbContext : DbContext
{
public ECommerceDbContext(DbContextOptions<ECommerceDbContext> options) : base(options) { }
public DbSet<Category> Categories { get; set; }
public DbSet<Product> Products { get; set; }
public DbSet<ProductTag> ProductTags { get; set; }
public DbSet<Order> Orders { get; set; }
public DbSet<OrderItem> OrderItems { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// 僅展示 Product 實體的關鍵設定作為範例
modelBuilder.Entity<Product>(entity =>
{
entity.HasKey(e => e.Id);
entity.Property(e => e.SKU).IsRequired().HasMaxLength(50);
entity.Property(e => e.Price).HasColumnType("decimal(18,2)");
entity.HasIndex(e => e.SKU).IsUnique();
entity.HasOne(e => e.Category)
.WithMany(c => c.Products)
.HasForeignKey(e => e.CategoryId)
.OnDelete(DeleteBehavior.Restrict);
});
// ... 其他實體的設定，請參考範例專案 ...
}
// ... SaveChangesAsync 攔截器 ...
}
使用 Collection Fixture 模式可以讓多個測試類別共享同一個容器實例，提升測試效能。以下是範例專案中的實際實作：
/// <summary>
/// MSSQL 容器的 Collection Fixture，用於在多個測試類別間共享同一個容器實例。
/// </summary>
public class SqlServerContainerFixture : IAsyncLifetime
{
private readonly MsSqlContainer _container;
public SqlServerContainerFixture()
{
_container = new MsSqlBuilder()
.WithImage("mcr.microsoft.com/mssql/server:2022-latest")
.WithPassword("Test123456!")
.WithCleanUp(true)
.Build();
}
public static string ConnectionString { get; private set; } = string.Empty;
/// <summary>
/// 初始化容器
/// </summary>
public async Task InitializeAsync()
{
await _container.StartAsync();
ConnectionString = _container.GetConnectionString();
// 等待容器完全啟動
await Task.Delay(2000);
Console.WriteLine($"SQL Server 容器已啟動，連線字串：{ConnectionString}");
}
/// <summary>
/// 清理容器
/// </summary>
public async Task DisposeAsync()
{
await _container.DisposeAsync();
Console.WriteLine("SQL Server 容器已清理");
}
}
/// <summary>
/// 定義測試集合，讓多個測試類別可以共享同一個 SqlServerContainerFixture。
/// </summary>
[CollectionDefinition(nameof(SqlServerCollectionFixture))]
public class SqlServerCollectionFixture : ICollectionFixture<SqlServerContainerFixture>
{
// 此類別只是用來定義 Collection，不需要實作內容
}
重要特點：
範例專案採用簡潔的設計模式，每個測試類別都透過 Collection Fixture 共享同一個 MSSQL 容器：
/// <summary>
/// EF Core 進階功能測試類別，展示 Repository Pattern 整合。
/// </summary>
[Collection(nameof(SqlServerCollectionFixture))]
public class EfCoreAdvancedTests : IDisposable
{
private readonly ECommerceDbContext _dbContext;
private readonly IProductByEFCoreRepository _advancedRepository;
private readonly ITestOutputHelper _testOutputHelper;
public EfCoreAdvancedTests(ITestOutputHelper testOutputHelper)
{
_testOutputHelper = testOutputHelper;
var connectionString = SqlServerContainerFixture.ConnectionString;
_testOutputHelper.WriteLine($"使用連線字串：{connectionString}");
// 建立 EF Core DbContext
var options = new DbContextOptionsBuilder<ECommerceDbContext>()
.UseSqlServer(connectionString)
.EnableSensitiveDataLogging()
.LogTo(_testOutputHelper.WriteLine, LogLevel.Information)
.Options;
_dbContext = new ECommerceDbContext(options);
// 建立 Repository 實例
_advancedRepository = new EfCoreProductRepository(_dbContext);
// 確保資料庫已建立
_dbContext.Database.EnsureCreated();
}
public void Dispose()
{
// 按照外鍵約束順序清理資料，確保測試隔離
_dbContext.Database.ExecuteSqlRaw("DELETE FROM ProductTags");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM OrderItems");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Orders");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Products");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Categories");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Tags");
_dbContext.Dispose();
}
// 測試方法將在後續章節展示...
}
重點特色：
IProductByEFCoreRepository 進行測試Dispose 方法確保每個測試後清理資料在進入實際的測試實作之前，我們需要先了解本專案採用的 Repository Pattern 設計架構。這個設計遵循 Interface Segregation Principle (ISP)，將不同職責的資料存取操作分離到不同的介面中。
我們的 Repository 設計分為三個層次的介面：
/// <summary>
/// 定義產品相關的基礎 CRUD 資料存取操作
/// </summary>
public interface IProductRepository
{
Task<IEnumerable<Product>> GetAllAsync();
Task<Product?> GetByIdAsync(int id);
Task AddAsync(Product product);
Task UpdateAsync(Product product);
Task DeleteAsync(int id);
}
/// <summary>
/// 定義 EF Core 特有的進階資料存取操作
/// </summary>
public interface IProductByEFCoreRepository
{
// EF Core 特有功能：Include、AsSplitQuery、ExecuteUpdate、AsNoTracking 等
Task<Product?> GetProductWithCategoryAndTagsAsync(int productId);
Task<IEnumerable<Product>> GetProductsByCategoryWithSplitQueryAsync(int categoryId);
Task<int> BatchUpdateProductPricesAsync(int categoryId, decimal priceMultiplier);
Task<int> BatchDeleteInactiveProductsAsync(int categoryId);
Task<IEnumerable<Product>> GetProductsWithNoTrackingAsync(decimal minPrice);
// N+1 查詢問題驗證：提供有問題和已最佳化的不同實作
Task<IEnumerable<Category>> GetCategoriesWithN1ProblemAsync(); // 示範錯誤做法（會產生 N+1 查詢）
Task<IEnumerable<Category>> GetCategoriesWithProductsOptimizedAsync(); // 正確做法（使用 Include 最佳化）
Task<IEnumerable<Product>> GetProductsByCategoryIdAsync(int categoryId); // 輔助方法
}
/// <summary>
/// 定義 Dapper 特有的進階資料存取操作
/// </summary>
public interface IProductByDapperRepository
{
// Dapper 特有功能：QueryMultiple、DynamicParameters、預存程序呼叫等
Task<Product?> GetProductWithTagsAsync(int productId);
Task<IEnumerable<Product>> SearchProductsAsync(int? categoryId = null, decimal? minPrice = null, bool? isActive = null);
Task<IEnumerable<ProductSalesReport>> GetProductSalesReportAsync(decimal minPrice);
}
1. 單一職責原則 (SRP)：
IProductRepository 專注於基本的資料存取操作IProductByEFCoreRepository 專注於 EF Core 特有的進階功能IProductByDapperRepository 專注於 Dapper 特有的進階功能2. 介面隔離原則 (ISP)：
3. 依賴反轉原則 (DIP)：
這種設計帶來以下測試優勢：
1. 測試隔離性：
2. Mock 的精確性：
3. 技術特性驗證：
在進入具體的測試實作之前，我們需要先了解一個重要的測試策略：SQL 指令碼外部化。在真實世界的專案中，將大量的 SQL 指令碼（例如資料表建立指令）直接寫在 C# 程式碼中，會導致程式碼變得臃腫且難以維護。一個更好的做法是將這些 SQL 指令碼儲存為獨立的 .sql 檔案，然後在測試執行時讀取這些檔案。
優點：
.sql 檔案，不需重新編譯程式碼。在測試專案中建立以下資料夾結構：
tests/DatabaseTesting.Tests/
├── SqlScripts/
│ ├── Tables/
│ │ ├── CreateCategoriesTable.sql
│ │ ├── CreateTagsTable.sql
│ │ ├── CreateCustomersTable.sql
│ │ ├── CreateProductsTable.sql
│ │ ├── CreateOrdersTable.sql
│ │ ├── CreateOrderItemsTable.sql
│ │ └── CreateProductTagsTable.sql
│ └── StoredProcedures/
│ └── GetProductSalesReport.sql
將 .sql 檔案設定為在建置時複製到輸出目錄：
<Project Sdk="Microsoft.NET.Sdk">
<!-- ... 其他設定 ... -->
<ItemGroup>
<!-- Tables -->
<Content Include="SqlScripts\Tables\CreateCategoriesTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateTagsTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateCustomersTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateProductsTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateOrdersTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateOrderItemsTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<Content Include="SqlScripts\Tables\CreateProductTagsTable.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
<!-- Stored Procedures -->
<Content Include="SqlScripts\StoredProcedures\GetProductSalesReport.sql">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</Content>
</ItemGroup>
</Project>
建立一個可重用的方法來按照依賴順序載入 SQL 腳本。這個方法需要加到測試類別中，以下是 EF Core 版本的實作：
/// <summary>
/// 確保資料表存在，使用外部 SQL 腳本建立
/// </summary>
private void EnsureTablesExist()
{
var scriptDirectory = Path.Combine(AppContext.BaseDirectory, "SqlScripts");
if (!Directory.Exists(scriptDirectory))
{
return;
}
// 按照依賴順序執行表格建立腳本
var orderedScripts = new[]
{
"Tables/CreateCategoriesTable.sql",
"Tables/CreateTagsTable.sql",
"Tables/CreateCustomersTable.sql",
"Tables/CreateProductsTable.sql",
"Tables/CreateOrdersTable.sql",
"Tables/CreateOrderItemsTable.sql",
"Tables/CreateProductTagsTable.sql"
};
foreach (var scriptPath in orderedScripts)
{
var fullPath = Path.Combine(scriptDirectory, scriptPath);
if (File.Exists(fullPath))
{
var script = File.ReadAllText(fullPath);
_dbContext.Database.ExecuteSqlRaw(script);
}
}
}
重要注意事項：
AppContext.BaseDirectory：確保在不同的執行環境下都能正確找到 SQL 檔案EnsureTablesExist() 方法現在我們來看 EF Core 的實作。接下來會展示如何對使用 EF Core 實作的 Repository 進行整合測試，包含基礎 CRUD 操作和 EF Core 特有的進階功能。
測試類別 EfCoreCrudTests 的設定與之前類似，但關鍵的區別在於，我們現在注入並測試 IProductRepository 的實作，而不是直接操作 DbContext。
[Collection(nameof(SqlServerCollectionFixture))]
public class EfCoreCrudTests : IDisposable
{
private readonly ECommerceDbContext _dbContext;
private readonly IProductRepository _productRepository;
private readonly ITestOutputHelper _testOutputHelper;
public EfCoreCrudTests(ITestOutputHelper testOutputHelper)
{
_testOutputHelper = testOutputHelper;
var connectionString = SqlServerContainerFixture.ConnectionString;
// 建立 EF Core DbContext
var options = new DbContextOptionsBuilder<ECommerceDbContext>()
.UseSqlServer(connectionString)
.EnableSensitiveDataLogging()
.LogTo(_testOutputHelper.WriteLine, LogLevel.Information)
.Options;
_dbContext = new ECommerceDbContext(options);
_productRepository = new EfCoreProductRepository(_dbContext);
// 使用第 3 章介紹的 SQL 指令碼外部化策略
EnsureTablesExist();
}
/// <summary>
/// 確保資料表存在，使用外部 SQL 腳本建立
/// </summary>
private void EnsureTablesExist()
{
var scriptDirectory = Path.Combine(AppContext.BaseDirectory, "SqlScripts");
var orderedScripts = new[]
{
"Tables/CreateCategoriesTable.sql",
"Tables/CreateTagsTable.sql",
"Tables/CreateCustomersTable.sql",
"Tables/CreateProductsTable.sql",
"Tables/CreateOrdersTable.sql",
"Tables/CreateOrderItemsTable.sql",
"Tables/CreateProductTagsTable.sql"
};
foreach (var scriptPath in orderedScripts)
{
var fullPath = Path.Combine(scriptDirectory, scriptPath);
if (File.Exists(fullPath))
{
var script = File.ReadAllText(fullPath);
_dbContext.Database.ExecuteSqlRaw(script);
}
}
}
public void Dispose()
{
// 按照外鍵約束順序清理資料，確保測試隔離
_dbContext.Database.ExecuteSqlRaw("DELETE FROM ProductTags");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM OrderItems");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Orders");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Products");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Categories");
_dbContext.Database.ExecuteSqlRaw("DELETE FROM Tags");
_dbContext.Dispose();
}
}
以下測試案例完整驗證 EfCoreProductRepository 中基本的 Create、Read、Update、Delete 操作：
[Fact]
public async Task AddAsync_使用EfCoreRepository新增商品_應該成功儲存()
{
// Arrange
await SeedCategoryAsync();
var category = await _dbContext.Categories.FirstAsync();
var product = new Product
{
Name = "EF Core Repo 測試商品",
Description = "這是一個測試商品",
Price = 1500,
Stock = 25,
CategoryId = category.Id,
SKU = "EFCORE-REPO-001",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
// Act
await _productRepository.AddAsync(product);
// Assert
product.Id.Should().BeGreaterThan(0);
var saved = await _dbContext.Products.FindAsync(product.Id);
saved.Should().NotBeNull();
saved.Name.Should().Be("EF Core Repo 測試商品");
}
[Fact]
public async Task GetByIdAsync_查詢存在的商品_應該返回正確的商品資料()
{
// Arrange
await SeedCategoryAsync();
var category = await _dbContext.Categories.FirstAsync();
var product = new Product
{
Name = "查詢測試商品",
Price = 999,
CategoryId = category.Id,
SKU = "GET-TEST-001",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(product);
// Act
var result = await _productRepository.GetByIdAsync(product.Id);
// Assert
result.Should().NotBeNull();
result!.Id.Should().Be(product.Id);
result.Name.Should().Be("查詢測試商品");
}
[Fact]
public async Task UpdateAsync_修改商品價格_應該正確更新()
{
// Arrange
await SeedCategoryAsync();
var category = await _dbContext.Categories.FirstAsync();
var product = new Product
{
Name = "更新測試商品",
Price = 500,
CategoryId = category.Id,
SKU = "UPDATE-TEST-001",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(product);
var newPrice = 800;
// Act
product.Price = newPrice;
await _productRepository.UpdateAsync(product);
// Assert
var updatedProduct = await _productRepository.GetByIdAsync(product.Id);
updatedProduct.Should().NotBeNull();
updatedProduct!.Price.Should().Be(newPrice);
}
[Fact]
public async Task DeleteAsync_刪除商品_應該找不到該商品()
{
// Arrange
await SeedCategoryAsync();
var category = await _dbContext.Categories.FirstAsync();
var product = new Product
{
Name = "刪除測試商品",
Price = 300,
CategoryId = category.Id,
SKU = "DELETE-TEST-001",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(product);
// Act
await _productRepository.DeleteAsync(product.Id);
// Assert
var deletedProduct = await _productRepository.GetByIdAsync(product.Id);
deletedProduct.Should().BeNull();
}
/// <summary>
/// 預先建立一個分類，供商品測試使用
/// </summary>
private async Task SeedCategoryAsync()
{
if (!await _dbContext.Categories.AnyAsync())
{
_dbContext.Categories.Add(new Category
{
Name = "電子產品",
Description = "各種電子設備",
IsActive = true
});
await _dbContext.SaveChangesAsync();
}
}
EF Core 的強項在於其強型別的 LINQ 查詢、變更追蹤機制，以及各種查詢最佳化功能。以下我們將測試這些進階功能：
/// <summary>
/// EF Core 進階功能的整合測試
/// </summary>
[Collection(nameof(SqlServerCollectionFixture))]
public class EfCoreAdvancedTests : IDisposable
{
private readonly ECommerceDbContext _dbContext;
private readonly IProductByEFCoreRepository _advancedRepository;
private readonly ITestOutputHelper _testOutputHelper;
public EfCoreAdvancedTests(ITestOutputHelper testOutputHelper)
{
_testOutputHelper = testOutputHelper;
var connectionString = SqlServerContainerFixture.ConnectionString;
var options = new DbContextOptionsBuilder<ECommerceDbContext>()
.UseSqlServer(connectionString)
.EnableSensitiveDataLogging()
.LogTo(_testOutputHelper.WriteLine, LogLevel.Information)
.Options;
_dbContext = new ECommerceDbContext(options);
_advancedRepository = new EfCoreProductRepository(_dbContext);
EnsureTablesExist();
}
// Dispose 和 EnsureTablesExist 方法同基礎測試
}
[Fact]
public async Task GetProductWithCategoryAndTagsAsync_載入完整關聯資料_應該正確載入所有相關資料()
{
// Arrange
await CreateProductWithCategoryAndTagsAsync();
// Act
var product = await _advancedRepository.GetProductWithCategoryAndTagsAsync(1);
// Assert
product.Should().NotBeNull();
product!.Category.Should().NotBeNull();
product.ProductTags.Should().NotBeEmpty();
_testOutputHelper.WriteLine($"產品：{product.Name}，分類：{product.Category.Name}，標籤數量：{product.ProductTags.Count}");
}
AsSplitQuery)當一個查詢中包含多個一對多 Include 時，可能會導致效能問題，這就是所謂的「笛卡兒積爆炸 (Cartesian Explosion)」。EF Core 提供了 AsSplitQuery() 方法來將一個 LINQ 查詢分解成多個 SQL 查詢，以避免這個問題。
[Fact]
public async Task GetProductsByCategoryWithSplitQueryAsync_使用分割查詢_應該避免笛卡兒積問題()
{
// Arrange
await CreateMultipleProductsWithTagsAsync();
// Act
var products = await _advancedRepository.GetProductsByCategoryWithSplitQueryAsync(1);
// Assert
products.Should().NotBeEmpty();
products.All(p => p.ProductTags.Any()).Should().BeTrue();
_testOutputHelper.WriteLine($"使用 AsSplitQuery 查詢到 {products.Count()} 個產品");
}
什麼是笛卡兒積 (Cartesian Explosion)？
在資料庫查詢中，當您試圖通過
JOIN一次性載入一個主實體以及它的多個關聯集合時（例如，一個Product同時Include它的Tags和Reviews），資料庫會為每一個可能的組合產生一列資料。如果一個產品有 10 個標籤和 5 條評論，查詢結果會返回1 * 10 * 5 = 50列，即使您只想要一個產品。這會導致大量的冗餘資料在資料庫和應用程式之間傳輸，嚴重影響效能。
AsSplitQuery()的作用是將一個 LINQ 查詢分解成多個獨立的 SQL 查詢。EF Core 會先查詢主實體，然後為每個Include的關聯集合產生一個額外的查詢，最後在記憶體中將這些結果組合起來。這樣就避免了單一查詢中的笛卡兒積問題，大幅提升了複雜關聯查詢的效率。
[Fact]
public async Task BatchApplyDiscountAsync_ExecuteUpdate批次更新關聯資料_應該高效更新()
{
// Arrange
await CreateMultipleProductsAsync();
var discountPercentage = 0.8m; // 8 折
// Act
var affectedRows = await _advancedRepository.BatchApplyDiscountAsync(1, discountPercentage);
// Assert
affectedRows.Should().BeGreaterThan(0);
var products = await _dbContext.Products.Where(p => p.CategoryId == 1).ToListAsync();
products.All(p => p.Price <= 800).Should().BeTrue(); // 確認價格已調整
_testOutputHelper.WriteLine($"批次更新了 {affectedRows} 個商品價格");
}
測試目標：驗證 Repository 實作是否正確解決了 N+1 查詢問題
測試場景說明：
GetCategoriesWithN1ProblemAsync() 方法會產生 N+1 查詢GetCategoriesWithProductsOptimizedAsync() 方法只產生少量查詢Repository 實作說明：
在實際的 EfCoreProductRepository 類別中，這兩個方法的實作差異如下：
/// <summary>
/// 取得所有分類及其產品資料（會產生 N+1 查詢問題的錯誤做法）。
/// 此方法故意不使用 Include，導致每個分類都會額外查詢一次產品資料。
/// </summary>
public async Task<IEnumerable<Category>> GetCategoriesWithN1ProblemAsync()
{
var categories = await context.Categories.ToListAsync();
// 故意觸發 N+1 查詢：每個分類都會產生一次額外的資料庫查詢
foreach (var category in categories)
{
// 存取 Products 屬性會觸發 lazy loading 或額外查詢
_ = category.Products.Count();
}
return categories;
}
/// <summary>
/// 取得所有分類及其產品資料（正確做法，使用 Include 最佳化）。
/// 使用 Include 預載入產品資料，避免 N+1 查詢問題。
/// </summary>
public async Task<IEnumerable<Category>> GetCategoriesWithProductsOptimizedAsync()
{
return await context.Categories
.Include(c => c.Products)
.ToListAsync();
}
以下測試方法的重點是驗證 Repository 方法的實作正確性，而不是在測試程式碼中示範問題。
[Fact]
public async Task N1QueryProblemVerification_對比有問題與最佳化的Repository方法_應該展示查詢效率差異()
{
// Arrange - 建立測試資料
await CreateCategoriesWithProductsAsync();
var stopwatch = new Stopwatch();
// Act 1: 測試有問題的方法
stopwatch.Start();
var categoriesWithProblem = await _advancedRepository.GetCategoriesWithN1ProblemAsync();
stopwatch.Stop();
var problemTime = stopwatch.ElapsedMilliseconds;
// Act 2: 測試最佳化方法
stopwatch.Restart();
var categoriesOptimized = await _advancedRepository.GetCategoriesWithProductsOptimizedAsync();
stopwatch.Stop();
var optimizedTime = stopwatch.ElapsedMilliseconds;
// Assert - 驗證結果正確性和效能差異
categoriesWithProblem.Should().HaveCount(3, "有問題的方法也要回傳正確的資料數量");
categoriesOptimized.Should().HaveCount(3, "最佳化方法要回傳正確的資料數量");
// 最佳化方法包含完整的關聯資料
foreach (var category in categoriesOptimized)
{
category.Products.Should().NotBeEmpty("最佳化方法應該預載入產品資料");
}
// 記錄效能差異
_testOutputHelper.WriteLine($"有問題的方法: {problemTime}ms");
_testOutputHelper.WriteLine($"最佳化方法: {optimizedTime}ms");
}
執行的輸出內容（擷取關鍵部分）
有問題的方法: 487ms
最佳化方法: 165ms
什麼是 N+1 查詢問題？
N+1 查詢問題是 ORM 中常見的效能陷阱。當您查詢一個主實體列表（1 次查詢），然後在迴圈中為每個主實體查詢其關聯資料（N 次查詢），就會產生 1+N 次資料庫往返。
錯誤做法：Repository 方法不使用 Include，導致在迴圈中產生額外查詢
正確做法：Repository 方法使用Include()或ThenInclude()在一次查詢中預載入所有需要的關聯資料這個問題在有大量關聯資料時會嚴重影響效能，是整合測試中必須驗證的重要場景。
[Fact]
public async Task GetProductsWithNoTrackingAsync_唯讀查詢_應該提升效能並減少記憶體使用()
{
// Arrange
await CreateMultipleProductsAsync();
var minPrice = 500m;
// Act
var products = await _advancedRepository.GetProductsWithNoTrackingAsync(minPrice);
// Assert
products.Should().NotBeEmpty();
products.All(p => p.Price >= minPrice).Should().BeTrue();
// 驗證這些實體不被 ChangeTracker 追蹤
var trackedEntities = _dbContext.ChangeTracker.Entries<Product>().Count();
trackedEntities.Should().Be(0, "AsNoTracking 查詢不應該追蹤實體");
_testOutputHelper.WriteLine($"查詢到 {products.Count()} 個產品，無追蹤狀態");
}
接下來，我們轉向 Dapper。Dapper 是一個輕量級的 Micro-ORM，它給予開發者完全的 SQL 控制權。本章節將展示如何對使用 Dapper 實作的 DapperProductRepository 進行整合測試。測試的重點在於驗證我們手寫的 SQL 語句是否能正確地與資料庫互動。
我們延續第 3 章介紹的 SQL 指令碼外部化策略，在 Dapper 測試中採用相同的 SqlScripts 資料夾結構和 .csproj 設定。
唯一的差異在於 EnsureDatabaseObjectsExist() 方法的實作方式，因為 Dapper 使用 IDbConnection.Execute() 而非 EF Core 的 ExecuteSqlRaw()。
在測試專案中建立以下資料夾結構：
/// <summary>
/// Dapper 版本的腳本載入方法，使用 IDbConnection.Execute() 執行 SQL
/// </summary>
private void EnsureDatabaseObjectsExist()
{
var scriptDirectory = Path.Combine(AppContext.BaseDirectory, "SqlScripts");
if (!Directory.Exists(scriptDirectory))
{
return;
}
// 按照依賴順序執行表格建立腳本（與第 3 章相同的順序）
var orderedScripts = new[]
{
"Tables/CreateCategoriesTable.sql",
"Tables/CreateTagsTable.sql",
"Tables/CreateCustomersTable.sql",
"Tables/CreateProductsTable.sql",
"Tables/CreateOrdersTable.sql",
"Tables/CreateOrderItemsTable.sql",
"Tables/CreateProductTagsTable.sql"
};
foreach (var scriptPath in orderedScripts)
{
var fullPath = Path.Combine(scriptDirectory, scriptPath);
if (File.Exists(fullPath))
{
var script = File.ReadAllText(fullPath);
_connection.Execute(script); // 使用 Dapper 的 Execute 方法
}
}
}
現在我們已經了解了 SQL 指令碼外部化策略，接下來實際應用到 Dapper Repository 的測試中。Dapper 作為輕量級的 Micro-ORM，讓開發者完全控制 SQL 語句的撰寫。我們的 DapperProductRepository 實作了 IProductRepository 介面，提供基本的 CRUD 操作。
以下是完整的 DapperCrudTests 測試類別實作：
/// <summary>
/// Dapper Repository CRUD 操作測試
/// </summary>
[Collection(nameof(SqlServerCollectionFixture))]
public class DapperCrudTests : IDisposable
{
private readonly IDbConnection _connection;
private readonly IProductRepository _productRepository;
private readonly ITestOutputHelper _testOutputHelper;
public DapperCrudTests(ITestOutputHelper testOutputHelper)
{
_testOutputHelper = testOutputHelper;
var connectionString = SqlServerContainerFixture.ConnectionString;
_connection = new SqlConnection(connectionString);
_connection.Open();
_productRepository = new DapperProductRepository(connectionString);
// 確保測試資料表存在 (使用第 3 章介紹的 SQL 指令碼外部化策略)
EnsureTablesExist();
}
public void Dispose()
{
// 清理測試資料
_connection.Execute("DELETE FROM ProductTags");
_connection.Execute("DELETE FROM OrderItems");
_connection.Execute("DELETE FROM Orders");
_connection.Execute("DELETE FROM Products");
_connection.Execute("DELETE FROM Categories");
_connection.Close();
_connection.Dispose();
GC.SuppressFinalize(this);
}
/// <summary>
/// 實作第 3 章介紹的 SQL 指令碼外部化策略。
/// 按照依賴順序載入所有必要的資料表腳本。
/// </summary>
private void EnsureTablesExist()
{
var scriptDirectory = Path.Combine(AppContext.BaseDirectory, "SqlScripts");
if (!Directory.Exists(scriptDirectory))
{
return;
}
// 按照依賴順序執行表格建立腳本（參考第 3 章的實作）
var orderedScripts = new[]
{
"Tables/CreateCategoriesTable.sql",
"Tables/CreateTagsTable.sql",
"Tables/CreateCustomersTable.sql",
"Tables/CreateProductsTable.sql",
"Tables/CreateOrdersTable.sql",
"Tables/CreateOrderItemsTable.sql",
"Tables/CreateProductTagsTable.sql"
};
foreach (var scriptPath in orderedScripts)
{
var fullPath = Path.Combine(scriptDirectory, scriptPath);
if (!File.Exists(fullPath))
{
continue;
}
var script = File.ReadAllText(fullPath);
_connection.Execute(script);
}
// 建立測試分類
var categoryExists = _connection.QuerySingle<int>("SELECT COUNT(*) FROM Categories");
if (categoryExists == 0)
{
_connection.Execute("""
INSERT INTO Categories (Name, Description, IsActive)
VALUES ('電子產品', '各種電子設備', 1), ('書籍', '各類書籍', 1)
""");
}
}
[Fact]
public async Task AddAsync_使用DapperRepository新增商品_應該成功儲存()
{
// Arrange
var categoryId = await _connection.QuerySingleAsync<int>("SELECT TOP 1 Id FROM Categories WHERE IsActive = 1");
var product = new Product
{
Name = "Dapper Repository 測試商品",
Description = "Dapper Repo 測試用",
Price = 2500m,
Stock = 15,
CategoryId = categoryId,
SKU = "DAPPER-REPO-001",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
// Act
await _productRepository.AddAsync(product);
// Assert
product.Id.Should().BeGreaterThan(0);
var savedProduct = await _productRepository.GetByIdAsync(product.Id);
savedProduct.Should().NotBeNull();
savedProduct.Name.Should().Be(product.Name);
}
[Fact]
public async Task GetAllAsync_使用DapperRepository查詢所有商品_應該回傳所有商品()
{
// Arrange
var categoryId = await _connection.QuerySingleAsync<int>("SELECT TOP 1 Id FROM Categories WHERE IsActive = 1");
await _productRepository.AddAsync(new Product
{
Name = "商品1", Price = 100m, CategoryId = categoryId, SKU = "SKU1", IsActive = true, CreatedAt = DateTime.UtcNow
});
await _productRepository.AddAsync(new Product
{
Name = "商品2", Price = 200m, CategoryId = categoryId, SKU = "SKU2", IsActive = true, CreatedAt = DateTime.UtcNow
});
// Act
var products = await _productRepository.GetAllAsync();
// Assert
products.Should().HaveCount(2);
}
[Fact]
public async Task GetByIdAsync_使用DapperRepository查詢單一商品_應該回傳正確商品()
{
// Arrange
var categoryId = await _connection.QuerySingleAsync<int>("SELECT TOP 1 Id FROM Categories WHERE IsActive = 1");
var newProduct = new Product
{
Name = "查詢用商品",
Price = 150m,
CategoryId = categoryId,
SKU = "SKU3",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(newProduct);
// Act
var product = await _productRepository.GetByIdAsync(newProduct.Id);
// Assert
product.Should().NotBeNull();
product!.Id.Should().Be(newProduct.Id);
product.Name.Should().Be("查詢用商品");
}
[Fact]
public async Task UpdateAsync_使用DapperRepository更新商品_應該成功更新()
{
// Arrange
var categoryId = await _connection.QuerySingleAsync<int>("SELECT TOP 1 Id FROM Categories WHERE IsActive = 1");
var productToUpdate = new Product
{
Name = "待更新商品",
Price = 300m,
CategoryId = categoryId,
SKU = "SKU4",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(productToUpdate);
var product = await _productRepository.GetByIdAsync(productToUpdate.Id);
product!.Name = "已更新商品";
product.Price = 350m;
// Act
await _productRepository.UpdateAsync(product);
// Assert
var updatedProduct = await _productRepository.GetByIdAsync(productToUpdate.Id);
updatedProduct.Should().NotBeNull();
updatedProduct.Name.Should().Be("已更新商品");
updatedProduct.Price.Should().Be(350m);
}
[Fact]
public async Task DeleteAsync_使用DapperRepository刪除商品_應該成功刪除()
{
// Arrange
var categoryId = await _connection.QuerySingleAsync<int>("SELECT TOP 1 Id FROM Categories WHERE IsActive = 1");
var productToDelete = new Product
{
Name = "待刪除商品",
Price = 400m,
CategoryId = categoryId,
SKU = "SKU5",
IsActive = true,
CreatedAt = DateTime.UtcNow
};
await _productRepository.AddAsync(productToDelete);
// Act
await _productRepository.DeleteAsync(productToDelete.Id);
// Assert
var deletedProduct = await _productRepository.GetByIdAsync(productToDelete.Id);
deletedProduct.Should().BeNull();
}
}
Dapper 的強項在於它給予開發者完全的 SQL 控制權，這使得針對複雜場景進行效能最佳化變得直接而有效。我們的樣本專案實作了 IProductByDapperRepository 介面，提供進階的資料存取功能。
/// <summary>
/// Dapper 進階功能的整合測試。
/// </summary>
[Collection(nameof(SqlServerCollectionFixture))]
public class DapperAdvancedTests : IDisposable
{
private readonly IDbConnection _connection;
private readonly IProductByDapperRepository _advancedRepository;
private readonly IProductRepository _basicRepository;
private readonly string _connectionString;
private readonly ITestOutputHelper _testOutputHelper;
public DapperAdvancedTests(ITestOutputHelper testOutputHelper)
{
_testOutputHelper = testOutputHelper;
_connectionString = SqlServerContainerFixture.ConnectionString;
_connection = new SqlConnection(_connectionString);
_connection.Open();
// 注入 Dapper 的 Repository 實作
_advancedRepository = new DapperProductRepository(_connectionString);
_basicRepository = new DapperProductRepository(_connectionString);
// 確保測試資料庫物件存在
EnsureDatabaseObjectsExist();
}
/// <summary>
/// 清理測試資料庫中的資料，確保每次測試後資料庫狀態一致。
/// </summary>
public void Dispose()
{
_connection.Execute("DELETE FROM ProductTags");
_connection.Execute("DELETE FROM OrderItems");
_connection.Execute("DELETE FROM Orders");
_connection.Execute("DELETE FROM Products");
_connection.Execute("DELETE FROM Categories");
_connection.Execute("DELETE FROM Tags");
_connection.Execute("DELETE FROM Customers");
_connection.Dispose();
}
/// <summary>
/// 確保資料庫中的必要物件（表格、預存程序等）存在。
/// </summary>
private void EnsureDatabaseObjectsExist()
{
var scriptDirectory = Path.Combine(AppContext.BaseDirectory, "SqlScripts");
if (!Directory.Exists(scriptDirectory))
{
return;
}
// 按照依賴順序執行表格建立腳本
var orderedScripts = new[]
{
"Tables/CreateCategoriesTable.sql",
"Tables/CreateTagsTable.sql",
"Tables/CreateCustomersTable.sql",
"Tables/CreateProductsTable.sql",
"Tables/CreateOrdersTable.sql",
"Tables/CreateOrderItemsTable.sql",
"Tables/CreateProductTagsTable.sql"
};
foreach (var scriptPath in orderedScripts)
{
var fullPath = Path.Combine(scriptDirectory, scriptPath);
if (!File.Exists(fullPath))
{
continue;
}
var script = File.ReadAllText(fullPath);
_connection.Execute(script);
}
// 建立預存程序
var storedProceduresDirectory = Path.Combine(scriptDirectory, "StoredProcedures");
if (Directory.Exists(storedProceduresDirectory))
{
var spScriptFiles = Directory.GetFiles(storedProceduresDirectory, "*.sql");
foreach (var scriptFile in spScriptFiles)
{
var script = File.ReadAllText(scriptFile);
_connection.Execute(script);
}
}
}
}
QueryMultiple 處理一對多關聯當需要從資料庫載入一個主物件及其關聯的多個集合時（例如，一個產品和它的所有標籤），如果使用傳統的 JOIN，會產生與 EF Core 相同的笛卡兒積問題。
在 Dapper 中，最佳的解決方案是使用 QueryMultiple。這個功能允許我們在一次資料庫往返中執行多個 SELECT 查詢，然後在程式碼中將結果集手動組合起來。
[Fact]
public async Task GetProductWithTagsAsync_使用QueryMultiple_應該正確組合資料()
{
// Arrange
var categoryId = await CreateTestCategoryAsync("QueryMultiple 分類");
var product = await CreateAndAddTestProductAsync("多查詢商品", "MULTI-001", 100, categoryId, true);
// 使用 Dapper 建立 Tag 和關聯
var tagId1 = await CreateTestTagAsync("標籤A");
var tagId2 = await CreateTestTagAsync("標籤B");
await LinkProductAndTagAsync(product.Id, tagId1);
await LinkProductAndTagAsync(product.Id, tagId2);
// Act
var result = await _advancedRepository.GetProductWithTagsAsync(product.Id);
// Assert
result.Should().NotBeNull();
result!.Id.Should().Be(product.Id);
result.Name.Should().Be("多查詢商品");
result.ProductTags.Should().HaveCount(2);
result.ProductTags.Should().AllSatisfy(pt => pt.Tag.Should().NotBeNull());
result.ProductTags.Select(pt => pt.Tag.Name).Should().Contain(new[] { "標籤A", "標籤B" });
}
這種方法雖然需要手動撰寫 SQL 和組合物件，但它極其高效，因為它避免了冗餘資料的傳輸，並且給予了開發者對查詢效能的完全控制。
DynamicParameters 處理動態查詢在某些情況下，我們需要根據不同的條件動態地建立查詢。Dapper 的 DynamicParameters 物件讓我們可以輕鬆地建立參數化的 SQL 查詢，有效防止 SQL 注入攻擊。
[Fact]
public async Task SearchProductsAsync_使用動態條件查詢_應該返回符合條件的商品()
{
// Arrange
var categoryId = await CreateTestCategoryAsync("動態查詢分類");
await CreateAndAddTestProductAsync("動態商品A", "DYN-A", 800, categoryId, true);
await CreateAndAddTestProductAsync("動態商品B", "DYN-B", 1200, categoryId, true);
await CreateAndAddTestProductAsync("動態商品C", "DYN-C", 1500, categoryId, false);
// Act - 測試多重條件查詢
var results = await _advancedRepository.SearchProductsAsync(
categoryId: categoryId,
minPrice: 1000,
isActive: true
);
// Assert
results.Should().HaveCount(1);
var product = results.First();
product.Name.Should().Be("動態商品B");
product.Price.Should().Be(1200);
product.IsActive.Should().BeTrue();
product.CategoryId.Should().Be(categoryId);
}
[Fact]
public async Task SearchProductsAsync_使用部分條件_應該返回符合條件的商品()
{
// Arrange
var categoryId = await CreateTestCategoryAsync("部分條件分類");
await CreateAndAddTestProductAsync("部分條件商品A", "PARTIAL-A", 500, categoryId, true);
await CreateAndAddTestProductAsync("部分條件商品B", "PARTIAL-B", 1500, categoryId, true);
// Act - 只使用價格條件
var results = await _advancedRepository.SearchProductsAsync(minPrice: 1000);
// Assert
results.Should().HaveCount(1);
results.First().Name.Should().Be("部分條件商品B");
}
這些測試驗證了我們可以根據不同的輸入參數安全地建立和執行 SQL 查詢，這是許多真實世界應用程式中不可或缺的功能。
Dapper 也能輕易地呼叫資料庫預存程序，這對於處理複雜的報表查詢或業務邏輯特別有用。
[Fact]
public async Task GetProductSalesReportAsync_呼叫預存程序_應該返回正確的銷售報表()
{
// Arrange
var categoryId = await CreateTestCategoryAsync("銷售報表分類");
var customerId = await CreateTestCustomerAsync("測試客戶");
// 建立產品
var product1 = await CreateAndAddTestProductAsync("高價商品", "SALES-HIGH", 1500, categoryId, true);
var product2 = await CreateAndAddTestProductAsync("低價商品", "SALES-LOW", 500, categoryId, true);
// 建立訂單和訂單項目
var orderId = await CreateTestOrderAsync(customerId);
await CreateTestOrderItemAsync(orderId, product1.Id, 2, 1500); // 數量 2, 單價 1500
await CreateTestOrderItemAsync(orderId, product2.Id, 5, 500); // 數量 5, 單價 500
// Act
var report = await _advancedRepository.GetProductSalesReportAsync(1000m);
// Assert
report.Should().NotBeEmpty();
var highPriceProductReport = report.FirstOrDefault(r => r.Name == "高價商品");
highPriceProductReport.Should().NotBeNull();
highPriceProductReport!.TotalQuantity.Should().Be(2);
highPriceProductReport.TotalRevenue.Should().Be(3000m);
}
為了讓 Dapper 的測試程式碼更簡潔且可重用，我們實作了一系列輔助方法來準備測試資料：
/// <summary>
/// 建立測試用的分類，並回傳其 Id。
/// </summary>
private async Task<int> CreateTestCategoryAsync(string name)
{
var sql = """
INSERT INTO Categories (
Name,
IsActive,
CreatedAt
)
OUTPUT INSERTED.Id
VALUES (
@Name,
1,
GETUTCDATE()
)
""";
return await _connection.QuerySingleAsync<int>(sql, new { Name = name });
}
/// <summary>
/// 建立並新增測試用的商品，回傳該商品實體。
/// </summary>
private async Task<Product> CreateAndAddTestProductAsync(string name, string sku, decimal price, int categoryId, bool isActive)
{
var product = new Product
{
Name = name,
Price = price,
CategoryId = categoryId,
SKU = sku,
IsActive = isActive,
CreatedAt = DateTime.UtcNow
};
await _basicRepository.AddAsync(product);
return product;
}
/// <summary>
/// 建立測試用的標籤，並回傳其 Id。
/// </summary>
private async Task<int> CreateTestTagAsync(string name)
{
var sql = """
INSERT INTO Tags (
Name,
IsActive,
CreatedAt
)
OUTPUT INSERTED.Id
VALUES (
@Name,
1,
GETUTCDATE()
)
""";
return await _connection.QuerySingleAsync<int>(sql, new { Name = name });
}
/// <summary>
/// 建立產品與標籤的關聯。
/// </summary>
private async Task LinkProductAndTagAsync(int productId, int tagId)
{
var sql = """
INSERT INTO ProductTags (
ProductId,
TagId
)
VALUES (
@ProductId,
@TagId
)
""";
await _connection.ExecuteAsync(sql, new { ProductId = productId, TagId = tagId });
}
今天我們深入探討了如何使用 Testcontainers 進行資料庫整合測試，並針對 EF Core 和 Dapper 這兩種主流的資料存取技術，實作了基於 Repository Pattern 的測試策略。
容器共享是效能關鍵：
Collection Fixture 模式來共享單一的 MSSQL 容器實例。Repository Pattern 提升了測試的抽象層次：
IProductRepository 介面，我們將測試程式碼與具體的資料存取技術（EF Core 或 Dapper）解耦。SQL 指令碼外部化是企業級實踐：
.sql 檔案，並透過 .csproj 設定自動複製到輸出目錄。EnsureDatabaseObjectsExist() 方法，可以按照依賴順序載入 SQL 腳本，避免外鍵約束錯誤。EF Core 與 Dapper 的測試策略差異：
Include）、以及效能最佳化（AsSplitQuery, ExecuteUpdateAsync）是否能正確地轉換為預期的資料庫操作。QueryMultiple 和動態查詢的建構。測試環境的一致性與隔離：
Dispose 方法中清理資料表的方式，確保了每個測試案例的獨立性。在實際專案中應用今天學到的技術時，建議遵循以下最佳實踐：
專案結構規劃：
SqlScripts 目錄，按照功能分類管理 SQL 檔案（Tables、StoredProcedures、Views 等）程式碼組織策略：
EfCoreTests、DapperTests）EnsureDatabaseObjectsExist() 和資料清理邏輯測試範圍設計：
總結來說，掌握 Testcontainers + Repository Pattern + SQL 指令碼外部化 的組合，能讓我們為任何資料存取技術建立一個高效、可靠且易於維護的自動化整合測試流程。這些技術不僅提升了測試品質，也為團隊協作和長期維護奠定了堅實的基礎。
Testcontainers 相關資源：
測試模式與架構：
資料存取技術：
專案組態管理：
範例程式碼：
這是「重啟挑戰：老派軟體工程師的測試修練」的第二十一天。明天會介紹 Day 22 – Testcontainers 整合測試：MongoDB 及 Redis 基礎應用。
iThome鐵人賽
看影片追技術