Parallell xunit tests fail using sqlite, works when running sequentially

[andrejohansson]

Disclamer it is not yet clear to me if my issue is with sqlite, efcore, xunit, the Microsoft.Data.Sqlite driver or my Query filters.

My goals

I want to create integration tests using xunit where I:

• Spin up a in memory Sqlite based db context per class
• Run tests in parallell
• Tests within the same class share db context and data
• Different classes gets their own db contexts and should not bother eachother

Expected behaviour

That tests between different classes do not affect each others contexts.

Actual behaviour

The behavior I am seeing is that running tests from two classes fails (either tests from class one fails or tests from class 2 fails). But if I run them sequentially one by one, they pass. Some kind of leak is happening.

The SqlLiteConnection is not thread safe according to an answer in (2) but as far as I understand xunit and class fixtures each DbFixture should get their own SqlConnection and each DbFixture should only be instantiated once per class.

I have tried

• using ClassFixtures
• using CollectionFixtures with name
• setting names on the sqlite inmemory connection (and verified that the tests indeed uses different connection strings)

But the problem remains.

To Reproduce

1. Create database fixture classes according to xunit documentation

       public class DefaultDatabaseFixture : DatabaseFixture
       {
           // The default DefaultDatabaseFixture initializes a db with query filters enabled.
           public DefaultDatabaseFixture() : base(nameof(DefaultDatabaseFixture), true, this.TestUserContext)
           {
           }
       }
   
       public class DisabledQueryFilterDatabaseFixture : DatabaseFixture
       {
           // The DisabledQueryFilterDatabaseFixture  fixture initializes a db with query filters disabled
           public DisabledQueryFilterDatabaseFixture() : base(nameof(DisabledQueryFilterDatabaseFixture), false, this.TestUserContext)
           {
           }
       }
   
   public abstract class DatabaseFixture : IDisposable
   {
           // Using a connection string with a named database per fixture in order to try to use Sqlites memory 
           private const string InMemoryConnectionString = "DataSource={0};Mode=Memory;cache=shared";
           private SqliteConnection connection;
   
           /// <summary>
           /// This db context is initialized once per class and shared between
           /// the tests within the same class.
           /// <see cref="https://xunit.net/docs/shared-context"/>
           /// </summary>
           public BokaMeraContext SharedDbContext { get; private set; }
   
       public DatabaseFixture(string databaseName, bool enableQueryFilters, IUserContext userContext)
       {
               var connectionString = string.Format(InMemoryConnectionString, databaseName);
               this.connection = new SqliteConnection(connectionString);
               this.connection.Open();
               var options = new DbContextOptionsBuilder<BokaMeraContext>()
                   .UseLazyLoadingProxies()
                   .UseSqlite(this.connection)
                   .Options;
               this.SharedDbContext = new BokaMeraContext(options, enableQueryFilters, userContext);
               this.SharedDbContext.Database.EnsureCreated();
       }
   
           protected virtual void Dispose(bool disposing)
           {
               if (disposing)
               {
   
                  this.SharedDbContext?.Dispose();
                  this.connection?.Dispose();
               }
           }
   
           public void Dispose()
           {
               this.Dispose(true);
               GC.SuppressFinalize(this);
           }
   }

2. Create two test fixtures that uses different database fixtures

   public class DisabledQueryFiltersTests : TestBase, IClassFixture<DisabledQueryFilterDatabaseFixture>
       {
           private readonly DisabledQueryFilterDatabaseFixture databaseFixture;
   
           public DisabledQueryFiltersTests(ITestOutputHelper testOutputHelper,
               DisabledQueryFilterDatabaseFixture databaseFixture) : base(
               testOutputHelper)
           {
               this.databaseFixture = databaseFixture;
           }
   
           [Fact]
           public void DbContextWithDisabledQueryFiltersShouldReturnValues()
           {
               // Given
               var context = this.databaseFixture.SharedDbContext;
   
               // When
               var owningBookingSupplier = new BookingSupplier {Id = Guid.NewGuid()};
               var service = new Service {Id = Guid.NewGuid(), Owner = owningBookingSupplier};
               context.Service.Add(service);
               context.SaveChanges();
               var result = context.Service.SingleOrDefault(s => s.Id == service.Id);
   
               // Then
               result.Should().BeEquivalentTo(service,
                   "the service should be returned even if the current UserContext does not own the BookingSupplier since the QueryFilters are disabled");
           }
       }

    public class SecurityQueriesTests : TestBase, IClassFixture<DefaultDatabaseFixture>
       {
           private readonly DefaultDatabaseFixture databaseFixture;
   
           public SecurityQueriesTests(ITestOutputHelper testOutputHelper, DefaultDatabaseFixture databaseFixture) : base(
               testOutputHelper)
           {
               this.databaseFixture = databaseFixture;
           }
   
           [Fact]
           public void DbContextWithoutUserContextShouldNotReturnValues()
           {
               // Given
               var context = this.databaseFixture.SharedDbContext;
   
               var owningBookingSupplier = new BookingSupplier();
               // When
               context.Service.Add(new Service {Owner = owningBookingSupplier});
               context.SaveChanges();
               var result = context.Service.SingleOrDefault(s => s.Id == owningBookingSupplier.Id);
   
               // Then
               result.Should().BeNull();
           }
   }

3. Run the tests in parallell on a multi core cpu. One of the tests fail. Example: DbContextWithDisabledQueryFiltersShouldReturnValues fails on the last assertion because it cannot find the service that was added and saved on the rows above. Re-running the test makes it pass. IF the context in this case would be a context with query filters enabled, it would have been correct that the service should not be found. Therefore I suspect that the context from the other fixture leaks into this test somehow.

Additional context

I am using a xeon cpu with 12 cores. I have noticed in other situations that if I use a machine with less cores I have less parallell issues in general (naturally). I mention it because it can be troublesome to replicate my issues if you don´t have multiple cores to run on on you machine.

BokaMeraContext

Relevant parts:

public partial class BokaMeraContext : DbContext
    {
        public IUserContext UserContext { get; set; }
        public bool EnableQueryFilters { get; }

        public BokaMeraContext(DbContextOptions<BokaMeraContext> options, bool enableQueryFilters) : base(options)
        {
            this.EnableQueryFilters = enableQueryFilters;
            this.UserContext = DefaultUserContext;
        }

        public BokaMeraContext(DbContextOptions<BokaMeraContext> options, bool enableQueryFilters,
            IUserContext userContext) : base(options)
        {
            this.EnableQueryFilters = enableQueryFilters;
            this.UserContext = userContext;
        }

     // call AddOwnershipQueryFilter for relevant entities here

       private void AddOwnershipQueryFilter<T>(EntityTypeBuilder<T> builder) where T : class, IOwnedEntity
        {
            // If security filters are explicitly disabled, return all rows
            if (this.EnableQueryFilters)
            {
                // If no user context is set, no owned rows should be returned
                builder.HasQueryFilter(x => this.UserContext.OwnedBookingSuppliers.Contains(x.Owner.Id) );
            }
        }

   // ...more below
}

xunit.runner.json

{
  "$schema": "https://xunit.net/schema/current/xunit.runner.schema.json",
  "shadowCopy": false,
  "parallelizeAssembly": true,
  "parallelizeTestCollections": true,
  "maxParallelThreads": 0
}

"maxParallelThreads": 0 means that xunit tries to utilize the default value (which is to use the number of cpu cores, in my case 12). See configuring xunit

Links for reading

1. https://xunit.net/docs/shared-context
2. https://stackoverflow.com/questions/56319638/entityframeworkcore-sqlite-in-memory-db-tables-are-not-created
3. https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite#using-sqlite-in-memory-databases
4. https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/

Component versions

Component	Version
Microsoft.NET.Test.Sdk	16.6.1
xunit	2.4.1
xunit.runner.visualstudio	2.4.1
Microsoft.EntityFrameworkCore	3.1.5
Microsoft.EntityFrameworkCore.Proxies	3.1.5
Microsoft.EntityFrameworkCore.Tools	3.1.5
Microsoft.EntityFrameworkCore.Sqlite	3.1.5
Microsoft.EntityFrameworkCore.SqlServer	3.1.5
Microsoft.Data.Sqlite.Core	3.1.5
Target framework	netcoreapp3.1
Dotnet core version	3.1.301
Operating system	Windows 10 Pro, Build 2004

[ajcvickers]

@andrejohansson How is AddOwnershipQueryFilter used?

[andrejohansson]

It looks like this:

        private void AddOwnershipQueryFilter<T>(EntityTypeBuilder<T> builder) where T : class, IOwnedEntity
        {
            // If security filters are explicitly disabled, return all rows
            if (this.EnableQueryFilters)
            {
                // If no user context is set, no owned rows should be returned
                builder.HasQueryFilter(x => this.UserContext.OwnedBookingSuppliers.Contains(x.Owner.Id) );
            }
        }

and is called like this

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // ...

            // Setup query filters
            this.SetupQueryFilters(modelBuilder);

            // ...
        }

   private void SetupQueryFilters(ModelBuilder modelBuilder)
        {
            this.AddOwnershipQueryFilter(modelBuilder.Entity<Service>());
            // ....more entities
       }

[ajcvickers]

@andrejohansson By default, EF creates and caches one model for each DbContext type. This means that the model is built by the first test that runs and is then reused by all the other tests, hence the errors you see. See https://docs.microsoft.com/en-us/ef/core/modeling/dynamic-model for more information.

[andrejohansson]

@ajcvickers that did the trick! Thank you!

A suggestion...

Perhaps there should be a note and a link from the page regarding thread issues to the dynamic model page you sent?

The error looks like a threading issue and if you do not know that the model is cached it seems to be the thread page you land on every time. It could be good to have a pointer there then.