EntityFrameworkCore caches SQL Server Sequence Numbers between xUnit test fixtures using SQL localdb which in turn causes primary key insert errors.

Question

Asp.Net Core version 5.0.10

I have a number of xunit based integration tests for an asp.net core web api that uses entity framework core for persistence.
My test fixtures use xunit to bootstrap a testhost using the Microsoft.AspNetCore.TestHost package.
I started having problems when running multiple tests that spanned different xunit test fixtures that primed an EF localdb test database in different ways. One set of tests used a pretty much empty database which was cleared down after each test, the other set of tests required a reasonable amount of standing data (setup by a xunit fixture) so that higher level web api resources could be tested.
Once a set of tests that share the same fixture have finished (achieved using xunit test collections), the test database would be deleted.

What I have observed is that an internal statically assigned service provider is caching many of the EF services between the two types of tests. In turn this meant a singleton service that caches HiLo sequence numbers was also cached. The singleton service is called SqlServerValueGeneratorCache implementing the interface ISqlServerValueGeneratorCache in the name space Microsoft.EntityFrameworkCore.SqlServer.ValueGeneration.Internal of the assembly Microsoft.EntityFrameworkCore.SqlServer (5.0.10)

This service carries on serving up sequence numbers from cached state following a database delete and subsequent database recreation as the new xunit test fixture starts. This in turn causes primary key insert conflicts as sequence numbers are incorrectly used from cached state.

There a multiple fixes to this situation.

1. Use the services.AddDbContext with options.EnableServiceProviderCaching(false). This solution works but is awful as every scoped instantiation of a DBContext has to reinitialize all the internal entity framework core services and things run very very slowly.
2. Use the services.AddDbContext with the (sp, options) delegate version and then use the options.UseInternalServiceProvider(sp) method to inject all the internal entity framework services into the application service container. This works but I don't like it as now all the internal entity framework services are visible to the web api controllers.
3. Do a bit of reflection to clear down the cached entity framework service providers in the async dispose of my xunit test fixtures, just after I have deleted the test database. This has become my preferred solution.

Here is the code I have used.

public static void ClearEFServiceProviderCache()
{

pragma warning disable EF1001

var serviceProviderCache = Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.Instance;
var dictionary = typeof(Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache).InvokeMember("_configurations", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Instance, null, serviceProviderCache, null) as IDictionary;
dictionary!.Clear();

pragma warning restore EF1001

}

Although this code is a bit illegal. It does have the benefit of completely removing all cached services to do with entity framework between xunit test fixtures.

I know I have answered my own question here, but I have a bit disappointed that EF is still resorting to use static singletons rather than keeping to the rules of DI singletons.

Answer 1

If you feel this should change then write it up here.