Motivation

The main reason for this feature is because EF does not support nested transactions. Mixing a transaction created via BeginTransaction with an ambient transaction (TransactionScope) is not supported as well. That means, if the code under test is using a transaction then you can't wrap it into another transaction to isolate the tests. Besides using a transaction, another option is to create a new database for each test but the duration of each test increases significantly. A faster way is to use the same database with different (randomly created) database schemas. That way, each tests is generating a new schema, runs the migrations, executes the test und reverts all migrations after that.

There are 2 ways to get to isolated instances of DbContext.

SQL Server only: Use one of the predefined implementation of ITestIsolationOptions. Use ITestIsolationOptions.SharedTablesAmbientTransaction to have the tests be executed in a transaction. If the code under test is using its own transaction then (1) Use ITestIsolationOptions.RollbackMigrationsAndCleanup or ITestIsolationOptions.CleanupOnly so the test is executed using randomly generated database schema (requires changing of the default schema at runtime) or (2) activate nested transaction.

SQLite only: Each test gets its own instance of the in-memory SQLite instance.

Using DbContext Provider directly

Minimal examples for SQL Server and SQLite.

// SQL Server
var dbContextProvider = new SqlServerTestDbContextProviderBuilder<DemoDbContext>("...", ITestIsolationOptions.SharedTablesAmbientTransaction)
       .Build();

// SQLite
var dbContextProvider = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .Build();

// SQLite
var dbContextProviderFactory = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .BuildFactory();

var dbContextProvider = dbContextProviderFactory.Create();

SQLite: Use BuildFactory to create a factory inside an assembly fixture (or similar) to reduce the setup of the database for each test
- example: DbContextProviderFactoryFixture and IntegrationTestsBase (requires nuget package Xunit.Extensions.AssemblyFixture )

Examples with some custom configuration.

// SQL Server
var dbContextProvider = new SqlServerTestDbContextProviderBuilder<DemoDbContext>("...", ITestIsolationOptions.SharedTablesAmbientTransaction)
       .UseLogging(testOutputHelper)
       .UseMigrationLogLevel(LogLevel.Warning)
       .ConfigureOptions((builder, schema) => builder.AddNestedTransactionSupport()
                                                     .AddSchemaRespectingComponents()
                                                     .EnableSensitiveDataLogging())
       .ConfigureSqlServerOptions((builder, schema) => builder.AddBulkOperationSupport()
                                                              .AddRowNumberSupport()
                                                              .AddCollectionParameterSupport())
       .UseSharedTablesIsolationLevel(IsolationLevel.ReadCommitted)
       .CollectExecutedCommands()
       .Build();

// SQLite
var dbContextProvider = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .UseLogging(testOutputHelper)
       .UseMigrationExecutionStrategy(IMigrationExecutionStrategy.EnsureCreated)
       .UseMigrationLogLevel(LogLevel.Warning)
       .ConfigureOptions(builder => builder.AddNestedTransactionSupport()
                                           .AddSchemaRespectingComponents()
                                           .EnableSensitiveDataLogging())
       .ConfigureSqliteOptions(builder => builder.AddBulkOperationSupport()
                                                 .AddRowNumberSupport())
       .CollectExecutedCommands()
       .Build();

The dbContextProvider provides 3 instances of the DbContext: ArrangeDbContext for setup up the database, ActDbContext for actual test and AssertDbContext for making assertions. All instances are created equally, they are using the same DbConnection and DbTransactionand are created lazily on the first access of the corresponding property. Furthermore the provider implements the interface IDbContextFactory<T> and can collect executed SQL statement for further analysis, like checking for "N + 1 Queries Problem".

   DemoDbContext ArrangeDbContext { get; }
   DemoDbContext ActDbContext { get; }
   DemoDbContext AssertDbContext { get; }

   DemoDbContext CreateDbContext();

   IReadOnlyCollection<string>? ExecutedCommands { get; } // must be activated with "CollectExecutedCommands"

Use ArrangeDbContext, ActDbContext and/or AssertDbContext in your tests.

   // Example that uses "ArrangeDbContext" and "ActDbContext" only
   [Fact]
   public void Generates_RowNumber_with_orderby_and_one_column()
   {
      // Arrange
      dbContextProvider.ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("4883F7E0-FC8C-45FF-A579-DF351A3E79BF"), Name = "1" });
      dbContextProvider.ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("18C13F68-0981-4853-92FC-FB7B2551F70A"), Name = "2" });
      dbContextProvider.ArrangeDbContext.SaveChanges();

      // Act
      var result = dbContextProvider.ActDbContext.TestEntities
                            .Select(e => new
                                         {
                                            e.Name,
                                            RowNumber = EF.Functions.RowNumber(e.Name)
                                         })
                            .ToList();

      // Assert
      result.First(t => t.Name == "1").RowNumber.Should().Be(1);
      result.First(t => t.Name == "2").RowNumber.Should().Be(2);
   }

Using a base class

Derive from SqlServerDbContextIntegrationTests<TDbContext> when using SQL Server or from SqliteDbContextIntegrationTests<TDbContext> for SQLite and replace the generic parameter TDbContext with the type of your DbContext. The corresponding base class provides 3 instances of the DbContext: ArrangeDbContext for setup up the database, ActDbContext for actual test and AssertDbContext for making assertions. All instances are created equally, they are using the same DbConnection and DbTransactionand are created lazily on the first access of the corresponding property.

public class MyIntegrationTests 
       : SqlServerDbContextIntegrationTests<DemoDbContext>
     //: SqliteDbContextIntegrationTests<DemoDbContext>

{
   public MyIntegrationTests()
      : base("conn-string", ITestIsolationOptions.SharedTablesAmbientTransaction) // <- SQL Server
    //: base() // <- SQLite
   {
   }

   // Configure the DbContext provider
   protected override void ConfigureTestDbContextProvider(SqlServerTestDbContextProviderBuilder<DemoDbContext> builder)
   {
     ...
   }

Use ArrangeDbContext, ActDbContext and/or AssertDbContext in your tests.

   // Example that uses "ArrangeDbContext" and "ActDbContext" only
   [Fact]
   public void Generates_RowNumber_with_orderby_and_one_column()
   {
      // Arrange
      ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("4883F7E0-FC8C-45FF-A579-DF351A3E79BF"), Name = "1" });
      ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("18C13F68-0981-4853-92FC-FB7B2551F70A"), Name = "2" });
      ArrangeDbContext.SaveChanges();

      // Act
      var result = ActDbContext.TestEntities
                            .Select(e => new
                                         {
                                            e.Name,
                                            RowNumber = EF.Functions.RowNumber(e.Name)
                                         })
                            .ToList();

      // Assert
      result.First(t => t.Name == "1").RowNumber.Should().Be(1);
      result.First(t => t.Name == "2").RowNumber.Should().Be(2);
   }

Further Configuration

The DbContext provider builder allows to specify the migrationExecutionStrategy. The IMigrationExecutionStrategy decides whether and how the database structure should be created:

IMigrationExecutionStrategy.Migrations: Creates the tables using EF Core Migrations
IMigrationExecutionStrategy.EnsureCreated: Creates the tables using dbContext.Database.EnsureCreated()
IMigrationExecutionStrategy.NoMigration: No database migration.
Alternatively, you can write your own migration strategy by implenting the interface IMigrationExecutionStrategy

Recommendations

Redirect EF logs to the test runner output using the method .UseLogging(...) on the builder.
SQL Server only: When the tests are isolated by using randomly generated schemas then use a different schema for each (git)branch when running tests on CI like Azure DevOps or GitHub. (see method ConfigureTestDbContextProvider to get an example)

Samples

All integration tests for Thinktecture.EntityFrameworkCore.SqlServer are using the class SqlServerDbContextIntegrationTests<TDbContext>.
Examples:



	Version 9.x.x (EF Core 9.0)
	Performance
	Features
	Convenience
	Integration Testing
	Isolation of tests
	Extensibility
	Version 7.x.x (EF Core 7.0)
	Version 4.x.x (EF Core 6.0)
	Version 3.x.x (EF Core 5.0)
	Version 2.x.x (EF Core 3.1)
	Version 1.x.x (EF Core 2)