Integration testing with .NET 6, EF Core 6, and xUnit

Question

Has anyone had any success in setting up integration testing for web applications written in .NET 6 and EntityFramework Core 6, and using SQLite in-memory database? I am having issues with test setup/teardown, so tests which run fine in isolation, start randomly failing when running all tests together.

My test context is set up based on the Microsoft examples:

public class TestApplication : AutofacWebApplicationFactory<Startup>
{
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureTestServices(services =>
        {
            services.AddAuthentication(options =>
            {
                options.DefaultAuthenticateScheme = FakeJwtBearerDefaults.AuthenticationScheme;
                options.DefaultChallengeScheme = FakeJwtBearerDefaults.AuthenticationScheme;
            }).AddFakeJwtBearer();

            services.AddAuthorization(options =>
            {
                options.DefaultPolicy = new AuthorizationPolicyBuilder()
                    .AddAuthenticationSchemes(FakeJwtBearerDefaults.AuthenticationScheme)
                    .RequireAuthenticatedUser()
                    .Build();
            });

            var connectionString = new SqliteConnectionStringBuilder($"DataSource=file:{Guid.NewGuid()}?cache=shared&mode=memory");
            var connection = new SqliteConnection(connectionString.ToString());
            connection.Open();

            var descriptor = services.SingleOrDefault(d => d.ServiceType == typeof(DbContextOptions<MyDbContext>));
            if (descriptor != null)
            {
                services.Remove(descriptor);
            }

            services.AddDbContext<MyDbContext>(options =>
            {
                options.UseSqlite(connection);
            });

            var sp = services.BuildServiceProvider();

            using var scope = sp.CreateScope();
            var scopedServices = scope.ServiceProvider;
            var db = scopedServices.GetRequiredService<MyDbContext>();
            var logger = scopedServices.GetRequiredService<ILogger<TestApplication>>();

            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();

            try
            {
                logger.LogInformation("Initialising in-memory database with test data");
                TestData.Initialise(db);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "An error occurred seeding the database with test messages. Error: {Message}", ex.Message);
            }
        });
    }
}

which will be used in my test fixtures something like this:

public class WhenGettingPartners
{
    [Fact]
    public async Task ItShouldAcceptValidRequests()
    {
        await using var app = new TestApplication();
                
        var client = app.CreateClient().WithRoles(Scopes.PartnerRead);
        
        var result = await client.GetAsync("/Partners");
        result.Should().BeSuccessful();

        var data = await result.Content.ReadAsType<Partner[]>();
        data.Should().NotBeEmpty();
    }

    [Fact]
    public async Task ItShouldRejectUnauthorisedRequests()
    {
        await using var app = new TestApplication();
                
        var client = app.CreateClient();
        
        var result = await client.GetAsync("/Partners");
        result.Should().HaveStatusCode(HttpStatusCode.Unauthorized);
    }
}

If I select an individual test case and run it, it works fine; if I select the entire text fixture and run it, it works fine. But when I run the entire test project, then tests will fail randomly, usually when it tries to recreate the database and finds the tables already exist.

I've tried using xUnit's IClassFixture interface to share one instance of TestApplication across all tests in the fixture:

public abstract class ApiTestFixture : IClassFixture<TestApplication>
{
    public ApiTestFixture(TestApplication application)
    {
        App = application;
    }

    public TestApplication App { get; }
}

public class WhenGettingClients : ApiTestFixture
{
    public WhenGettingClients(TestApplication app) : base(app)
    {
    }

    [Fact]
    public async Task ItShouldAcceptValidRequests()
    {
        var client = App.CreateClient().WithRoles(Scopes.ClientRead);
        
        var result = await client.GetAsync("/Clients");
        result.Should().BeSuccessful();

        var data = await result.Content.ReadAsType<Client[]>();
        data.Should().HaveCount(2);
    }

    [Fact]
    public async Task ItShouldRejectUnauthorisedRequests()
    {
        var client = App.CreateClient();
        
        var result = await client.GetAsync("/Clients");
        result.Should().HaveStatusCode(HttpStatusCode.Unauthorized);
    }
}

but that fails in exactly the same way.

Update

I am developing using the Rider IDE from JetBrains; the same issue occurs if I run the tests in Visual Studio. However, I noticed that if I test with code coverage in Rider, then the tests all pass! Similarly, if I test with profiling in Rider, then the tests all pass; so I'm wondering if there's something more esoteric going on, that the code coverage runner somehow forces the tests to execute in a manner which is more deterministic or less likely to result in test pollution.