Tutorial: Implementing EF Core's MigrationsInfrastructureTestBase for a Custom Provider

This guide explains how to implement the MigrationsInfrastructureTestBase from the Entity Framework Core test suite for a custom database provider. This is a crucial step to ensure your provider's migration logic is compatible with EF Core.

This pattern uses Testcontainers for automated database provisioning, which is highly recommended for creating reliable, self-contained tests.

Further, for this tutorial, we are using the the CmdScale.EntityFrameworkCore.TimescaleDB provider as an example.

Project Setup

The Microsoft.EntityFrameworkCore.Relational.Specification.Tests is using xUnit, so you should also choose xUnit as your testing framework. The naming convention for your project should be <Your-Provider>.FunctionalTests as mentioned in the Microsoft Docs. Next you need to install the following packages (Testcontainers.PostgreSql is optional, but recommended) and reference your custom database provider (CmdScale.EntityFrameworkCore.TimescaleDB and CmdScale.EntityFrameworkCore.TimescaleDB.Design in this case).

<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="8.0.19" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Relational.Specification.Tests" Version="8.0.19" />
  <PackageReference Include="Testcontainers.PostgreSql" Version="4.7.0" />
</ItemGroup>
<ItemGroup>
  <ProjectReference Include="..\CmdScale.EntityFrameworkCore.TimescaleDB.Design\CmdScale.EntityFrameworkCore.TimescaleDB.Design.csproj" />
  <ProjectReference Include="..\CmdScale.EntityFrameworkCore.TimescaleDB\CmdScale.EntityFrameworkCore.TimescaleDB.csproj" />
</ItemGroup>

The Database Fixture (`TimescaleMigrationsFixture`)

The xUnit Class Fixture is the heart of the test setup. It manages the entire lifecycle of the test database, starting a fresh container before tests run and destroying it after.

Key Responsibilities:

Implement IAsyncLifetime to manage the Testcontainer.
Holding the instance of your RelationalTestStoreFactory

TimescaleMigrationsFixture.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.TestUtilities;
using Testcontainers.PostgreSql;
using Xunit;

namespace CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Utils;

public class TimescaleMigrationsFixture : MigrationsInfrastructureFixtureBase, IAsyncLifetime
{
    // Configure the Testcontainer for your specific database
    private readonly PostgreSqlContainer _dbContainer = new PostgreSqlBuilder()
        .WithImage("timescale/timescaledb:latest-pg17")
        .WithDatabase("migration_tests_db")
        .WithUsername("test_user")
        .WithPassword("test_password")
        .Build();

        public string ConnectionString => _dbContainer.GetConnectionString();

        // Start the container before tests run
        public override async Task InitializeAsync()
        {
            await _dbContainer.StartAsync();
            TimescaleTestStoreFactory.ConnectionString = ConnectionString;
            await base.InitializeAsync();
        }

        // Stop the container after tests finish
        public override async Task DisposeAsync()
        {
            await base.DisposeAsync();
            await _dbContainer.StopAsync();
        }

        protected override ITestStoreFactory TestStoreFactory => TimescaleTestStoreFactory.Instance;
}

The Command Interceptor (Handling Incompatibilities)

This is the most critical step. The EF Core test suite contains raw SQL and data that was written with a generic or SQL Server-centric mindset. This data can be incompatible with other databases that have stricter rules (like PostgreSQL).

Since you cannot edit the tests, you must intercept and fix the incompatible SQL before it's executed.

TimescaleMigrationsTestInterceptor.cs

using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Utils;

public class TimescaleMigrationsTestInterceptor : DbCommandInterceptor
{
    /// <summary>
    /// The <see cref="MigrationsInfrastructureTestBase"/> from <see cref="Microsoft.EntityFrameworkCore.Migrations"/> does not quote table names
    /// in the raw SQL queries it provides. In PostgreSQL, unquoted names are folded to lower case, while in the model they are defined in PascalCase.
    /// Further, the Bar column is defined without a type and is by default interpreted as an integer type in PostgreSQL, but the seed data provides a string value.
    /// 
    /// This results in failed migrations due to missing tables or columns, which is a false negative. To prevent this, we can intercept the SQL commands
    /// and fix the casing of the table and column names, as well as any other issues.
    /// </summary>
    private static string Fix(string commandText)
    {
        return commandText
            // Fixes the invalid string-to-integer insert.
            .Replace("' '", "0")
            // Fixes the unquoted, case-sensitive table and column names.
            .Replace("INSERT INTO Table1 (Id, Bar, Description)", "INSERT INTO \"Table1\" (\"Id\", \"Bar\", \"Description\")");
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        command.CommandText = Fix(command.CommandText);
        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        command.CommandText = Fix(command.CommandText);
        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }

    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        command.CommandText = Fix(command.CommandText);
        return result;
    }

    public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        command.CommandText = Fix(command.CommandText);
        return new ValueTask<InterceptionResult<int>>(result);
    }
}

`RelationalTestStoreFactory` and `RelationalTestStore`

Together, the RelationalTestStoreFactory and RelationalTestStore implement a factory pattern for the DbContext, which allows for configuring the DbContextOptions for the tests, including your provider's settings.

TimescaleTestStore.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.TestUtilities;
using Npgsql;
using System.Collections.Concurrent;
using System.Data.Common;

namespace CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Utils;

public class TimescaleTestStore : RelationalTestStore
{
    private static readonly ConcurrentDictionary<string, TimescaleTestStore> _sharedStores = new();

    protected override DbConnection Connection { get => base.Connection; set => base.Connection = value; }
    public override string ConnectionString { get => base.ConnectionString; protected set => base.ConnectionString = value; }

    private TimescaleTestStore(string name, bool shared, string connectionString)
        : base(name, shared)
    {
        ConnectionString = connectionString;
        Connection = new NpgsqlConnection(ConnectionString);
    }

    public static TimescaleTestStore Create(string name, string connectionString)
        => new(name, shared: false, connectionString);

    public static TimescaleTestStore GetOrCreateShared(string name, string connectionString)
        => _sharedStores.GetOrAdd(name, _ =>
        {
            TimescaleTestStore store = new(name, shared: true, connectionString);

            DbContextOptions<MigrationsInfrastructureFixtureBase.MigrationsContext> options = new DbContextOptionsBuilder<MigrationsInfrastructureFixtureBase.MigrationsContext>()
                .UseNpgsql(connectionString).UseTimescaleDb().Options;
            store.Initialize(null, () => new MigrationsInfrastructureFixtureBase.MigrationsContext(options), null);
            return store;
        });

    public override DbContextOptionsBuilder AddProviderOptions(DbContextOptionsBuilder builder)
        => builder.AddInterceptors(new TimescaleMigrationsTestInterceptor()).UseNpgsql(ConnectionString, options =>
        {
        }).UseTimescaleDb().EnableSensitiveDataLogging();

    public override void Clean(DbContext context)
    {
        context.Database.EnsureClean();
    }
}

TimescaleTestStoreFactory.cs

using Microsoft.EntityFrameworkCore.TestUtilities;
using Microsoft.Extensions.DependencyInjection;

namespace CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Utils
{
    public class TimescaleTestStoreFactory : RelationalTestStoreFactory
    {
        public static TimescaleTestStoreFactory Instance { get; } = new();
        public static string ConnectionString { get; set; } = string.Empty;

        public override IServiceCollection AddProviderServices(IServiceCollection serviceCollection)
        {
            return serviceCollection.AddEntityFrameworkNpgsql().AddEntityFrameworkTimescaleDb();
        }

        public override TestStore Create(string storeName)
            => TimescaleTestStore.Create(storeName, ConnectionString);

        public override TestStore GetOrCreate(string storeName)
            => TimescaleTestStore.GetOrCreateShared(storeName, ConnectionString);

        public override ListLoggerFactory CreateListLoggerFactory(Func<string, bool> shouldLogCategory)
            => new TestSqlLoggerFactory(shouldLogCategory);
    }
}

Implement the Test Class

Finally, you can implement the MigrationsInfrastructureTestBase. The setup is now clean, and the tests will pass. The backward-compatibility tests (Can_diff_against_*) require providing model snapshot files. You can find an example of them here.

TimescaleDbMigrationsInfrastructureTests.cs

using CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Snapshots;
using CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests.Utils;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Xunit.Abstractions;

namespace CmdScale.EntityFrameworkCore.TimescaleDB.FunctionalTests
{
    public class TimescaleDbMigrationsInfrastructureTests : MigrationsInfrastructureTestBase<TimescaleMigrationsFixture>
    {
        private readonly ITestOutputHelper _testOutputHelper;

        private class AspNetIdentityDbContext(DbContextOptions options)
            : IdentityDbContext<IdentityUser>(options)
        {
            protected override void OnModelCreating(ModelBuilder builder)
            {
                base.OnModelCreating(builder);

                builder.Entity<IdentityUser>(b =>
                {
                    b.HasIndex(u => u.NormalizedUserName).HasDatabaseName("UserNameIndex").IsUnique();
                    b.HasIndex(u => u.NormalizedEmail).HasDatabaseName("EmailIndex");
                    b.ToTable("AspNetUsers");
                });

                builder.Entity<IdentityUserClaim<string>>(b =>
                {
                    b.ToTable("AspNetUserClaims");
                });

                builder.Entity<IdentityUserLogin<string>>(b =>
                {
                    b.ToTable("AspNetUserLogins");

                    b.Property(l => l.LoginProvider).HasMaxLength(128);
                    b.Property(l => l.ProviderKey).HasMaxLength(128);
                });

                builder.Entity<IdentityUserToken<string>>(b =>
                {
                    b.ToTable("AspNetUserTokens");

                    b.Property(t => t.LoginProvider).HasMaxLength(128);
                    b.Property(t => t.Name).HasMaxLength(128);
                });

                builder.Entity<IdentityRole>(b =>
                {
                    b.HasIndex(r => r.NormalizedName).HasDatabaseName("RoleNameIndex").IsUnique();
                    b.ToTable("AspNetRoles");
                });

                builder.Entity<IdentityRoleClaim<string>>(b =>
                {
                    b.ToTable("AspNetRoleClaims");
                });

                builder.Entity<IdentityUserRole<string>>(b =>
                {
                    b.ToTable("AspNetUserRoles");
                });
            }
        }

        public TimescaleDbMigrationsInfrastructureTests(TimescaleMigrationsFixture fixture, ITestOutputHelper testOutputHelper)
        : base(fixture)
        {
            _testOutputHelper = testOutputHelper;
            Fixture.ListLoggerFactory.Clear();
        }

        [ConditionalFact]
        public override void Can_diff_against_2_1_ASP_NET_Identity_model()
        {
            using AspNetIdentityDbContext context = new(
                Fixture.TestStore.AddProviderOptions(new DbContextOptionsBuilder()).Options);

            DiffSnapshot(new AspNetIdentity21ModelSnapshot(), context);
        }

        [ConditionalFact]
        public override void Can_diff_against_2_2_ASP_NET_Identity_model()
        {
            using AspNetIdentityDbContext context = new(
                Fixture.TestStore.AddProviderOptions(new DbContextOptionsBuilder()).Options);

            DiffSnapshot(new AspNetIdentity22ModelSnapshot(), context);
        }

        [ConditionalFact]
        public override void Can_diff_against_2_2_model()
        {
            using MigrationsInfrastructureFixtureBase.MigrationsContext context = Fixture.CreateContext();
            DiffSnapshot(new EfCore22ModelSnapshot(), context);
        }

        [ConditionalFact]
        public override void Can_diff_against_3_0_ASP_NET_Identity_model()
        {
            using AspNetIdentityDbContext context = new(
                Fixture.TestStore.AddProviderOptions(new DbContextOptionsBuilder()).Options);

            DiffSnapshot(new AspNetIdentity30ModelSnapshot(), context);
        }
    }
}

And that's it! ✅

This should be the result:

test-explorer-MigrationsInfrastructure-tests

This complete setup:

Uses Testcontainers for isolation
Works around EF Core migration test quirks using a command interceptor
Supports snapshot diff testing
Enables running the MigrationsInfrastructureTestBase suite with your custom provider

Great work!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Tutorial: Implementing EF Core's MigrationsInfrastructureTestBase for a Custom Provider

Project Setup

The Database Fixture (`TimescaleMigrationsFixture`)

Key Responsibilities:

The Command Interceptor (Handling Incompatibilities)

`RelationalTestStoreFactory` and `RelationalTestStore`

Implement the Test Class

Resources

Clone this wiki locally

Tutorial: Implementing EF Core's MigrationsInfrastructureTestBase for a Custom Provider

Project Setup

The Database Fixture (TimescaleMigrationsFixture)

Key Responsibilities:

The Command Interceptor (Handling Incompatibilities)

RelationalTestStoreFactory and RelationalTestStore

Implement the Test Class

Resources

Clone this wiki locally

The Database Fixture (`TimescaleMigrationsFixture`)

`RelationalTestStoreFactory` and `RelationalTestStore`