Integration test locking up on transaction

Question

We are creating integration tests using Playwright.
We get a copy of the DB context and

• Begin a transaction
• Set up the data
• Run the test
• Verify the proper action took place
• Rollback the transaction

But the open transaction is preventing the test from completing.

public T getContext<T>() where T : DbContext {
    T context = Scope.ServiceProvider.GetRequiredService<T>();
    Contexts.Add(context);
    IDbContextTransaction trans = context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);            
    Transactions.Add(trans); // remember all transactions so we can roll them back later
    return context;
}

The SUT locks up whenever it hits the database.

UPDATE: We are getting back a DbContext, but not the same instance that the SUT is using.
This page suggests UseTransaction so we added that to the constructor.

public DBContextBase(DbContextOptions options) : base(options) {
    if (transaction != null) {
        Database.UseTransaction(transaction.GetDbTransaction());
    }
}

We do see it hit the constructor and use the transaction but it still blocks the second context.

In startup, we try to force the context to use the same instance of the database by

builder.Services.AddDbContext<DocumentsContext>(options => {
    options.UseSqlServer(new SqlConnection(connection));
});

I thought that would work. Still blocking.

Answer 1

There are several methods that claim to let transaction work here. UseTransaction, TransactionScope, etc, but we couldn't get any to work. The database access was blocked until the test timed out then continued, too late.

In the end, we went with snapshots.

if (File.Exists("c:\\temp\\snapshots\\snapshot01.snp")) {
   dbContext.Database.ExecuteSql($"DROP DATABASE Snapshot01");
   File.Delete("c:\\temp\\snapshots\\snapshot01.snp");
}
dbContext.Database.ExecuteSql($"CREATE DATABASE Snapshot01 ON(NAME={databasename}, FILENAME= 'c:\\temp\\snapshots\\snapshot01.snp') AS SNAPSHOT OF {databasename}");
----
context.Database.ExecuteSql($"USE MASTER; ALTER DATABASE {databasename} SET Single_User WITH ROLLBACK IMMEDIATE");
context.Database.ExecuteSql($"USE MASTER; RESTORE DATABASE {databasename} FROM DATABASE_SNAPSHOT = 'Snapshot01'");
context.Database.ExecuteSql($"USE MASTER; ALTER DATABASE {databasename} SET Multi_User");