xUnit Theory测试：如何在测试间清理SQLite数据库

我用xUnit的[Theory]编写测试用例时碰到了问题：第一个测试用例能运行通过，但第二个测试用例会抛出异常Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'UNIQUE constraint failed: CONTRAHENT.ID'。单独运行每个测试用例时全部都能通过，所以我推测第二个测试运行前数据库没有被清理/释放。

public class TestDatabaseFixture
{
    private const string ConnectionString = "DataSource=file::memory:?cache=shared";
    private static readonly object _lock = new();
    private static bool _databaseInitialized;

    public TestDatabaseFixture()
    {
        lock (_lock)
        {
            if (!_databaseInitialized)
            {
                using (var context = CreateContext())
                {
                    context.Database.EnsureDeleted();
                    context.Database.EnsureCreated();
                }

                _databaseInitialized = true;
            }
        }
    }

    public DbTrancheTest CreateContext(bool sensitiveDataLoggingEnabled = false)
        => new DbTrancheTest(
            new DbContextOptionsBuilder<DbTranche>()
            .EnableSensitiveDataLogging(sensitiveDataLoggingEnabled)
            .UseSqlite(ConnectionString)
            .Options);
}

public class TrancheServiceTest : IClassFixture<TestDatabaseFixture>
{
    private TestDatabaseFixture Fixture { get; }
    // private readonly ITestOutputHelper output;
    public TrancheServiceTest(TestDatabaseFixture fixture)
    {
        Fixture = fixture;
    }

    [Theory]
    [InlineData(TrancheStatus.created, true)] // passed
    [InlineData(TrancheStatus.accepted, false)] // failed
    [InlineData(TrancheStatus.chanaged, true)] // failed
    [InlineData(TrancheStatus.waitforconf, false)] // failed
    [InlineData(TrancheStatus.rejected, false)] //failed
   
    public void Should_Set_Can_Edit_Flag_For_User(TrancheStatus status, bool result)
    {
        // Arrange
        using var context = Fixture.CreateContext();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
  
        var orderRepository = new TrancheOrderRepository(context);
        var userRepo = new UserRepository(context);
        var contrahentRepository = new ContrahentRepository(context);

        contrahentRepository.Add(new CONTRAHENT { ID = 1, NAME = "Contrahent1" });
        contrahentRepository.Save();

        var trancheOrder = new TRANCHE_ORDER
        {
            ID = 1,
            ISSUE_DATE = DateTime.Parse("2023-02-01"),
            LAST_UPDATE = DateTime.Parse("2023-02-01"),
            STATUS = (short)status,
            VOLUME = 0,
            USER = "",
        };
        orderRepository.Add(trancheOrder);
        orderRepository.Save();

        userRepo.Add(new USER { ID = 1, CAN_MODIFY = 1, DESCRIPTION = "Test user", FK_CONTRAHENT_ID = 1, NAME = "User1", USER_ID = 123 });
        userRepo.Save();

        var service = new TrancheService(contrahentRepository, orderRepository, userRepo);
        DateTime dateFrom = DateTime.Parse("2023-01-01");
        DateTime dateTo = DateTime.Parse("2023-12-01");

        // Act
        var trancheList = service.GetTrancheList(dateFrom, dateTo, 123, true);

        // Assert
        Assert.Collection(trancheList,
            e => Assert.Equal(result, e.CanEdit));
    }
}

原因分析 #

你使用的是共享内存SQLite数据库（连接字符串中的cache=shared），且夹具仅初始化一次数据库。虽然每个测试方法中调用了EnsureDeleted()和EnsureCreated()，但SQLite共享内存数据库只要存在活跃连接，就不会被真正删除。第一个测试的连接可能未完全释放，导致第二个测试运行时数据库残留数据，插入重复ID违反唯一约束。另外，IClassFixture实例为整个测试类共享，夹具构造函数仅执行一次，后续测试不会重新初始化数据库。

解决办法 #

方案1：每个测试使用独立内存数据库（推荐）

去掉连接字符串中的cache=shared，让每个DbContext拥有独立的内存数据库：

private const string ConnectionString = "DataSource=:memory:";

同时，每个测试方法创建上下文后需打开连接才能使用内存数据库：

using var context = Fixture.CreateContext();
context.Database.OpenConnection(); // 新增该行
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

方案2：用事务回滚隔离测试数据

如果要保留共享内存模式，可在每个测试中开启事务，测试结束后回滚：

public void Should_Set_Can_Edit_Flag_For_User(TrancheStatus status, bool result)
{
    using var context = Fixture.CreateContext();
    using var transaction = context.Database.BeginTransaction();
    
    try
    {
        // 原有Arrange、Act、Assert代码
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

方案3：测试前清空所有表数据

在每个测试开始前手动清空数据库所有表：

// Arrange
using var context = Fixture.CreateContext();
// 清空所有表
foreach (var entityType in context.Model.GetEntityTypes())
{
    var tableName = entityType.GetTableName();
    context.Database.ExecuteSqlRaw($"DELETE FROM {tableName};");
}
// 后续插入测试数据

内容的提问来源于stack exchange，提问作者miuosh