asp-net core integration tests rollback transaction after each tests

Question

I've used this documentation for my integration tests: asp.net core integration tests and this is my simplified integration test:

 public class ApplicationControllerTests : IClassFixture<CustomWebApplicationFactory<Startup>>
 {
     private readonly CustomWebApplicationFactory<Startup> _factory;

     public ApplicationControllerTests(CustomWebApplicationFactory<Startup> factory)
     {
           _factory = factory;
     }

    [Fact]
    public async Task AcceptOffer_WhenCalled_ShouldReturnSuccess()
    {
        var httpClient = factory.CreateClient();

        var acceptOfferRequest = new AcceptOfferRequest
        {
            OfferId = 1,
            OfferType = 1
        }.ToJsonStringContent();

        var response = await httpClient.PostAsync("/api/v1/Application/AcceptOffer", acceptOfferRequest);

        response.EnsureSuccess();

        Assert.Equal(HttpStatusCode.OK, response.StatusCode);
    }
}

As you can see I am sending an HTTP request to "/api/v1/Application/AcceptOffer" API, which updates some entities in the database and returns status code 200 if everything is ok.

After the test, I want to clean the database as it was before the test. For that, I want to put my tests inside the transaction and rollback after each test finishes. Is it possible with the current implementation? Dropping and recreating a database is costly, that's why I want to put my tests inside transactions.

I am using MS SQL database with entity framework ORM.

One solution is to use a singleton of DbContext and then wrap all tests inside transactions but the problem is SQL does not support nested transactions and if API uses transactions too it will throw a runtime exception. Another solution is to use C#'s TransactionScope but it does not work with SQL transactions and does not work with different threads.

Answer 1

Okay, As I find out there is no nice way or possible to solve this problem using transactions. Instead of transactions, I wrote global scripts where I manually truncate all tables and execute it after each test. It's obviously faster than dropping and recreating the database.