DavidStringer-3141 avatar image
0 Votes"
DavidStringer-3141 asked DavidStringer-3141 commented

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

Asp.Net Core version 5.0.10

I have a number of xunit based integration tests for an 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;
#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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DavidStringer-3141, Since this question is related to xunit, I recommend that you could ask the question in xunit.

0 Votes 0 ·


II only mentioned xunit in relation to how test hosts and dependency injected services are initialized via xunit test collections and their related fixtures.
Here is a thought exercise that does not involve xunit and does it all by hand.

  1. Initialize a test host with new EF dblocal database.

  2. Run some tests to add data via the test host.

  3. Dispose the test host and delete the dblocal database.

  4. Initialize another instance of the same test host with new EF dblocal database (same name).

  5. Run some more tests to add data via the new test host and start to see insert conflicts due to internal ISqlServerValueGeneratorCache implementation caching sequence numbers from old run at step 2.

If I add a step between 3 and 4 to clear the EF singleton service providers as illustrated by the reflection code I provided, the problem would go away.

0 Votes 0 ·

1 Answer

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.