Entity Framework Core 知识库

Appearance

使用 SQLite In-Memory 做集成测试 ​

概述 ​

SQLite In-Memory 模式是一种将数据库完全存储在内存中的特殊模式,它提供了比 InMemory 提供程序更接近真实数据库的测试环境。与 EF Core InMemory 不同,SQLite In-Memory 是一个真正的关系型数据库引擎,会执行 SQL、强制执行约束、支持事务,并且行为与实际生产数据库(SQL Server、PostgreSQL 等)高度一致。

为什么选择 SQLite In-Memory? ​

特性InMemorySQLite In-MemoryTestcontainers
真实性❌ 低✅ 高✅ 最高
性能⚡ 极快🚀 快🐢 较慢
配置复杂度✅ 简单✅ 简单⚠️ 复杂
SQL生成❌ 否✅ 是✅ 是
约束检查❌ 否✅ 是✅ 是
并发控制❌ 否✅ 是✅ 是
事务语义⚠️ 简化✅ 完整✅ 完整
外部依赖✅ 无✅ 无❌ 需要Docker

快速开始 ​

1. 安装 NuGet 包 ​

bash
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

2. 基本配置 ​

csharp
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

public class SqliteInMemoryFixture : IDisposable
{
    private readonly SqliteConnection _connection;
    
    public SqliteInMemoryFixture()
    {
        // 创建内存连接
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open(); // 必须保持连接打开
    }
    
    public AppDbContext CreateContext()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connection)
            .Options;
        
        var context = new AppDbContext(options);
        context.Database.EnsureCreated(); // 创建架构
        return context;
    }
    
    public void Dispose()
    {
        _connection?.Close();
        _connection?.Dispose();
    }
}

3. xUnit 测试基类 ​

csharp
public abstract class IntegrationTestBase : IClassFixture<SqliteInMemoryFixture>, IDisposable
{
    protected readonly AppDbContext Context;
    private readonly SqliteInMemoryFixture _fixture;
    
    protected IntegrationTestBase(SqliteInMemoryFixture fixture)
    {
        _fixture = fixture;
        Context = fixture.CreateContext();
    }
    
    public virtual void Dispose()
    {
        // 清理数据但保留架构
        foreach (var entity in Context.ChangeTracker.Entries().Select(e => e.Entity).ToList())
        {
            Context.Remove(entity);
        }
        Context.SaveChanges();
        Context.Dispose();
    }
}

完整的测试示例 ​

场景 1: 实体关系测试 ​

csharp
public class RelationshipTests : IntegrationTestBase
{
    public RelationshipTests(SqliteInMemoryFixture fixture) : base(fixture) { }
    
    [Fact]
    public async Task OneToMany_ShouldEnforceFKConstraint()
    {
        // Arrange
        var category = new Category { Name = "Electronics" };
        Context.Categories.Add(category);
        await Context.SaveChangesAsync();
        
        // Act & Assert: 有效的外键
        var validProduct = new Product 
        { 
            Name = "Laptop", 
            Price = 999.99m, 
            CategoryId = category.Id 
        };
        Context.Products.Add(validProduct);
        await Context.SaveChangesAsync(); // 成功
        
        // Act & Assert: 无效的外键会抛出异常
        var invalidProduct = new Product 
        { 
            Name = "Orphan", 
            Price = 50m, 
            CategoryId = 999 // 不存在的分类
        };
        Context.Products.Add(invalidProduct);
        
        await Assert.ThrowsAsync<DbUpdateException>(() => 
            Context.SaveChangesAsync());
    }
    
    [Fact]
    public async Task CascadeDelete_ShouldWorkCorrectly()
    {
        // Arrange
        var category = new Category { Name = "Books" };
        category.Products = new List<Product>
        {
            new Product { Name = "Book 1", Price = 29.99m },
            new Product { Name = "Book 2", Price = 39.99m }
        };
        
        Context.Categories.Add(category);
        await Context.SaveChangesAsync();
        
        var categoryId = category.Id;
        
        // Act: 删除分类
        Context.Categories.Remove(category);
        await Context.SaveChangesAsync();
        
        // Assert: 产品也被删除(级联删除)
        var remainingProducts = await Context.Products
            .Where(p => p.CategoryId == categoryId)
            .ToListAsync();
        
        Assert.Empty(remainingProducts);
    }
    
    [Fact]
    public async Task ManyToMany_ShouldCreateJoinTable()
    {
        // Arrange
        var student = new Student { Name = "Alice" };
        var course = new Course { Name = "Mathematics" };
        
        student.Courses = new List<Course> { course };
        
        Context.Students.Add(student);
        await Context.SaveChangesAsync();
        
        // Act: 查询关联数据
        var loadedStudent = await Context.Students
            .Include(s => s.Courses)
            .FirstOrDefaultAsync(s => s.Id == student.Id);
        
        // Assert
        Assert.NotNull(loadedStudent);
        Assert.Single(loadedStudent.Courses);
        Assert.Equal("Mathematics", loadedStudent.Courses.First().Name);
    }
}

// 测试模型
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}

public class Course
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Student> Students { get; set; } = new List<Student>();
}

场景 2: 并发控制测试 ​

csharp
public class ConcurrencyTests : IntegrationTestBase
{
    public ConcurrencyTests(SqliteInMemoryFixture fixture) : base(fixture) { }
    
    [Fact]
    public async Task OptimisticConcurrency_ShouldDetectConflict()
    {
        // Arrange: 创建带版本号的实体
        var product = new ProductWithVersion 
        { 
            Name = "Original", 
            Price = 100m,
            RowVersion = DateTime.UtcNow.Ticks 
        };
        Context.Products.Add(product);
        await Context.SaveChangesAsync();
        
        // 模拟并发: 两个上下文读取同一记录
        using var context1 = _fixture.CreateContext();
        using var context2 = _fixture.CreateContext();
        
        var product1 = await context1.Products.FindAsync(product.Id);
        var product2 = await context2.Products.FindAsync(product.Id);
        
        // Act: context1 先更新
        product1!.Price = 120m;
        await context1.SaveChangesAsync(); // 成功
        
        // Act: context2 尝试更新(应该失败)
        product2!.Price = 150m;
        
        await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
            () => context2.SaveChangesAsync());
    }
}

public class ProductWithVersion
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public long RowVersion { get; set; } // 并发令牌
}

// 在 OnModelCreating 中配置
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductWithVersion>(entity =>
    {
        entity.Property(p => p.RowVersion)
            .IsConcurrencyToken();
    });
}

场景 3: 事务测试 ​

csharp
public class TransactionTests : IntegrationTestBase
{
    public TransactionTests(SqliteInMemoryFixture fixture) : base(fixture) { }
    
    [Fact]
    public async Task Transaction_ShouldRollbackOnError()
    {
        // Arrange
        var initialCount = await Context.Products.CountAsync();
        
        // Act
        using var transaction = await Context.Database.BeginTransactionAsync();
        
        try
        {
            Context.Products.Add(new Product 
            { 
                Name = "Product 1", 
                Price = 10m,
                CategoryId = 1 
            });
            await Context.SaveChangesAsync();
            
            // 故意引发异常
            throw new InvalidOperationException("Something went wrong");
            
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
        }
        
        // Assert: 数据应该回滚
        var finalCount = await Context.Products.CountAsync();
        Assert.Equal(initialCount, finalCount);
    }
    
    [Fact]
    public async Task MultipleOperations_InSingleTransaction()
    {
        // Arrange
        using var transaction = await Context.Database.BeginTransactionAsync();
        
        try
        {
            // 操作 1: 创建订单
            var order = new Order 
            { 
                CustomerId = 1, 
                OrderDate = DateTime.UtcNow,
                TotalAmount = 300m,
                Status = OrderStatus.Pending
            };
            Context.Orders.Add(order);
            await Context.SaveChangesAsync();
            
            // 操作 2: 扣减库存
            var product1 = await Context.Products.FindAsync(1);
            var product2 = await Context.Products.FindAsync(2);
            
            if (product1 != null && product2 != null)
            {
                product1.Stock -= 5;
                product2.Stock -= 3;
                await Context.SaveChangesAsync();
            }
            
            // 操作 3: 记录日志
            var log = new AuditLog 
            { 
                Action = "OrderCreated", 
                Timestamp = DateTime.UtcNow,
                Details = $"Order {order.Id} created"
            };
            Context.AuditLogs.Add(log);
            await Context.SaveChangesAsync();
            
            // 提交事务
            await transaction.CommitAsync();
            
            // Assert
            Assert.True(await Context.Orders.AnyAsync());
            Assert.True(await Context.AuditLogs.AnyAsync());
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

场景 4: 复杂查询测试 ​

csharp
public class ComplexQueryTests : IntegrationTestBase
{
    public ComplexQueryTests(SqliteInMemoryFixture fixture) : base(fixture) { }
    
    [Fact]
    public async Task LinqToSql_ShouldGenerateCorrectSQL()
    {
        // Arrange: 种子数据
        SeedTestData();
        
        // Act: 复杂 LINQ 查询
        var result = await Context.Orders
            .Where(o => o.OrderDate >= DateTime.UtcNow.AddDays(-30))
            .Where(o => o.Status == OrderStatus.Completed)
            .Include(o => o.Customer)
            .GroupBy(o => o.CustomerId)
            .Select(g => new 
            { 
                CustomerId = g.Key,
                TotalOrders = g.Count(),
                TotalSpent = g.Sum(o => o.TotalAmount),
                AvgOrderValue = g.Average(o => o.TotalAmount)
            })
            .OrderByDescending(x => x.TotalSpent)
            .Take(10)
            .ToListAsync();
        
        // Assert
        Assert.NotEmpty(result);
        
        // 可以查看生成的 SQL
        var sql = Context.Orders
            .Where(o => o.OrderDate >= DateTime.UtcNow.AddDays(-30))
            .ToQueryString();
        
        Console.WriteLine($"Generated SQL:\n{sql}");
    }
    
    [Fact]
    public async Task RawSql_ShouldExecuteCorrectly()
    {
        // Arrange
        SeedTestData();
        
        // Act: 原始 SQL 查询
        var expensiveProducts = await Context.Products
            .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
            .ToListAsync();
        
        // Assert
        Assert.All(expensiveProducts, p => Assert.True(p.Price > 100));
    }
    
    private void SeedTestData()
    {
        var categories = new[]
        {
            new Category { Id = 1, Name = "Electronics" },
            new Category { Id = 2, Name = "Books" }
        };
        Context.Categories.AddRange(categories);
        
        var products = new[]
        {
            new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1, Stock = 50 },
            new Product { Id = 2, Name = "Phone", Price = 599.99m, CategoryId = 1, Stock = 100 },
            new Product { Id = 3, Name = "C# Book", Price = 49.99m, CategoryId = 2, Stock = 200 }
        };
        Context.Products.AddRange(products);
        
        Context.SaveChanges();
    }
}

ASP.NET Core Web API 集成测试 ​

1. 测试 WebApplicationFactory ​

csharp
public class CustomWebApplicationFactory : WebApplicationFactory<Program>
{
    private readonly SqliteConnection _sqliteConnection;
    
    public CustomWebApplicationFactory()
    {
        // 创建共享的 SQLite 内存连接
        _sqliteConnection = new SqliteConnection("DataSource=:memory:");
        _sqliteConnection.Open();
    }
    
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureServices(services =>
        {
            // 移除原有的 DbContext 注册
            var descriptor = services.SingleOrDefault(
                d => d.ServiceType == typeof(DbContextOptions<AppDbContext>));
            
            if (descriptor != null)
                services.Remove(descriptor);
            
            // 添加 SQLite In-Memory
            services.AddDbContext<AppDbContext>(options =>
                options.UseSqlite(_sqliteConnection));
        });
    }
    
    public void InitializeDatabase()
    {
        using var scope = Services.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        context.Database.EnsureCreated();
        
        // 可选: 种子数据
        SeedData(context);
    }
    
    private static void SeedData(AppDbContext context)
    {
        context.Categories.AddRange(
            new Category { Name = "Electronics" },
            new Category { Name = "Books" }
        );
        context.SaveChanges();
    }
    
    public override async ValueTask DisposeAsync()
    {
        _sqliteConnection?.Close();
        _sqliteConnection?.Dispose();
        await base.DisposeAsync();
    }
}

2. API 端点测试 ​

csharp
public class ProductsApiTests : IClassFixture<CustomWebApplicationFactory>
{
    private readonly HttpClient _client;
    private readonly CustomWebApplicationFactory _factory;
    
    public ProductsApiTests(CustomWebApplicationFactory factory)
    {
        _factory = factory;
        _factory.InitializeDatabase();
        _client = factory.CreateClient();
    }
    
    [Fact]
    public async Task GetProducts_ShouldReturnAllProducts()
    {
        // Act
        var response = await _client.GetAsync("/api/products");
        
        // Assert
        response.EnsureSuccessStatusCode();
        var products = await JsonSerializer.DeserializeAsync<List<Product>>(
            await response.Content.ReadAsStreamAsync());
        
        Assert.NotNull(products);
    }
    
    [Fact]
    public async Task CreateProduct_ShouldSaveToDatabase()
    {
        // Arrange
        var newProduct = new 
        {
            Name = "New Product",
            Price = 99.99m,
            CategoryId = 1
        };
        
        var content = new StringContent(
            JsonSerializer.Serialize(newProduct),
            Encoding.UTF8,
            "application/json");
        
        // Act
        var response = await _client.PostAsync("/api/products", content);
        
        // Assert
        response.EnsureSuccessStatusCode();
        var createdProduct = await JsonSerializer.DeserializeAsync<Product>(
            await response.Content.ReadAsStreamAsync());
        
        Assert.NotNull(createdProduct);
        Assert.Equal("New Product", createdProduct.Name);
        Assert.Equal(99.99m, createdProduct.Price);
        
        // 验证数据库中确实存在
        using var scope = _factory.Services.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        var exists = await context.Products.AnyAsync(p => p.Id == createdProduct.Id);
        Assert.True(exists);
    }
    
    [Fact]
    public async Task UpdateProduct_WithInvalidData_ShouldReturnBadRequest()
    {
        // Arrange
        var invalidProduct = new { Name = "", Price = -10m }; // 无效数据
        var content = new StringContent(
            JsonSerializer.Serialize(invalidProduct),
            Encoding.UTF8,
            "application/json");
        
        // Act
        var response = await _client.PutAsync("/api/products/1", content);
        
        // Assert
        Assert.Equal(HttpStatusCode.BadRequest, response.StatusCode);
    }
}

高级技巧 ​

1. 每个测试独立数据库 ​

csharp
public class IsolatedSqliteTests
{
    [Fact]
    public async Task TestWithIsolatedDatabase()
    {
        // 每个测试使用唯一的内存数据库
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();
        
        try
        {
            var options = new DbContextOptionsBuilder<AppDbContext>()
                .UseSqlite(connection)
                .Options;
            
            await using var context = new AppDbContext(options);
            await context.Database.EnsureCreatedAsync();
            
            // 测试逻辑...
            var count = await context.Products.CountAsync();
            Assert.Equal(0, count);
        }
        finally
        {
            connection.Close();
            connection.Dispose();
        }
    }
}

2. 预填充测试数据 ​

csharp
public static class TestDataSeeder
{
    public static async Task SeedAsync(AppDbContext context)
    {
        if (await context.Categories.AnyAsync())
            return; // 已 seeding
        
        var categories = new[]
        {
            new Category { Id = 1, Name = "Electronics" },
            new Category { Id = 2, Name = "Books" },
            new Category { Id = 3, Name = "Clothing" }
        };
        
        await context.Categories.AddRangeAsync(categories);
        
        var products = new[]
        {
            new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1 },
            new Product { Id = 2, Name = "Phone", Price = 599.99m, CategoryId = 1 },
            new Product { Id = 3, Name = "C# Book", Price = 49.99m, CategoryId = 2 }
        };
        
        await context.Products.AddRangeAsync(products);
        await context.SaveChangesAsync();
    }
}

3. 测试后清理 ​

csharp
public class CleanupAfterTest : IntegrationTestBase
{
    public CleanupAfterTest(SqliteInMemoryFixture fixture) : base(fixture) { }
    
    public override void Dispose()
    {
        // 自定义清理逻辑
        Context.Database.ExecuteSqlRaw("DELETE FROM Orders");
        Context.Database.ExecuteSqlRaw("DELETE FROM Products");
        Context.Database.ExecuteSqlRaw("DELETE FROM Categories");
        
        base.Dispose();
    }
}

常见问题与解决方案 ​

问题 1: 连接已关闭 ​

错误: The connection is closed

原因: SQLite 内存数据库需要在整个测试生命周期中保持连接打开

解决方案:

csharp
// ❌ 错误做法
public AppDbContext CreateContext()
{
    using var connection = new SqliteConnection("DataSource=:memory:");
    connection.Open();
    // ... 方法结束时连接被关闭
}

// ✅ 正确做法
private readonly SqliteConnection _sharedConnection;

public SqliteInMemoryFixture()
{
    _sharedConnection = new SqliteConnection("DataSource=:memory:");
    _sharedConnection.Open(); // 保持打开
}

问题 2: 表不存在 ​

错误: SQLite Error 1: 'no such table: Products'

原因: 忘记调用 EnsureCreated()

解决方案:

csharp
public AppDbContext CreateContext()
{
    var context = new AppDbContext(options);
    context.Database.EnsureCreated(); // 创建表
    return context;
}

问题 3: 数据在测试间泄漏 ​

问题: 一个测试的数据影响了另一个测试

解决方案:

csharp
public override void Dispose()
{
    // 每个测试后清理数据
    var entities = Context.ChangeTracker.Entries().Select(e => e.Entity).ToList();
    Context.RemoveRange(entities);
    Context.SaveChanges();
    
    base.Dispose();
}

最佳实践总结 ​

✅ 推荐做法 ​

  1. 使用共享连接: 在整个测试套件中复用同一个连接
  2. 测试隔离: 每个测试使用独立的数据库或清理数据
  3. 调用 EnsureCreated(): 确保在使用前创建架构
  4. 保持连接打开: 内数据库需要连接持续打开
  5. 测试真实场景: 利用 SQLite 的真实性测试复杂查询和事务

❌ 避免的陷阱 ​

  1. 不要在测试间共享状态: 会导致测试相互影响
  2. 不要忘记清理: 内存泄漏会导致测试变慢
  3. 不要用于性能测试: 内存数据库的性能不代表真实数据库
  4. 不要测试 SQLite 特定功能: 如果目标是 SQL Server/PostgreSQL

总结 ​

SQLite In-Memory 是 EF Core 集成测试的理想选择:

  • ✅ 真实性: 真正的关系型数据库引擎
  • ✅ 快速: 内存操作,无磁盘 I/O
  • ✅ 零配置: 无需 Docker 或外部服务器
  • ✅ 完整功能: 支持 SQL、约束、事务、并发控制
  • ✅ 适合 CI/CD: 易于自动化

对于需要测试真实数据库行为的场景,SQLite In-Memory 比 InMemory 提供程序更可靠,比 Testcontainers 更轻量!

基于 MIT 许可发布

Copyright © 2026 EF Core 知识库