Integration Tests with multiple AbpDbContext in "same database" - results in "Table AbpUsers" does not exist #11225

This is a post to store knowledge for future reference.

We have an ANZ application, and have segregated our application entities into its own DbContext - separate from ANZ. We do "share" some entities, however - for example - the AbpUsers table is included in our own DbContext - but we disable migrations for that table so it does not attempt to generate the table a second time.

We have started writing unit/integration tests for our business logic - and came across this problem - how do we mock the database, when we need BOTH the Abp tables, AND our own table schemas to be created into the mocked sqlite dbcontext? If we do nothing, we get an error when running the test, that tables could not be found.

The answer required 2 adjustments to achieve.

1. Edit 'XXX.Test.Base/DependencyInjection/ServiceCollectionRegistrar.cs' to publish the sqlite in-memory connection to IoC

public static class ServiceCollectionRegistrar
{
    public static void Register(IIocManager iocManager)
    {
        RegisterIdentity(iocManager);

        var builder = new DbContextOptionsBuilder<AnzDbContext>();

        var inMemorySqlite = new SqliteConnection("Data Source=:memory:");
        builder.UseSqlite(inMemorySqlite);

        // register connection for reuse
        iocManager.IocContainer.Register(Component.For<SqliteConnection>().Instance(inMemorySqlite));

        iocManager.IocContainer.Register(
            Component
                .For<DbContextOptions<AnzDbContext>>()
                .Instance(builder.Options)
                .LifestyleSingleton()
        );

        inMemorySqlite.Open();
        
        new AnzDbContext(builder.Options).Database.EnsureCreated();
    }

    private static void RegisterIdentity(IIocManager iocManager)
    {
        var services = new ServiceCollection();

        IdentityRegistrar.Register(services);

        WindsorRegistrationHelper.CreateServiceProvider(iocManager.IocContainer, services);
    }
}

2. We derive our unit tests from the same unit test base as our Abp unit test classes - that triggers the Abp tables to be generated/mocked
3. We edit our custom unit test "ServiceCollectionRegistrar" to locate the sqlite database connection from IoC, rather than initialize a new DB - however, we also need to use reflection to dig into EF to FORCE our custom table schema to be generated. Usually, the code will call EnsureCreated( new CustomDbContext(builder.Options).Database.EnsureCreated();) - but reading the XmlDoc, you will see that if the DB already exists, it will do nothing - not even generate the schema - so we need to use reflection to access the Create() and CreateTables() methods.

public static void Register(IIocManager iocManager)
{
    RegisterIdentity(iocManager);

    var builder = new DbContextOptionsBuilder<CustomDbContext>();

    var inMemorySqlite = iocManager.Resolve<SqliteConnection>();
    builder.UseSqlite(inMemorySqlite);

    iocManager.IocContainer.Register(
        Component
            .For<DbContextOptions<CustomDbContext>>()
            .Instance(builder.Options)
            .LifestyleSingleton()
    );

    inMemorySqlite.Open();

    var db = new CustomDbContext(builder.Options);

    // FORCE the table schema to be created - even though the database already exists, it only contains ANZ tables - none of our own
    var propDependencies = typeof(DatabaseFacade).GetProperty("Dependencies", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
    var dependencies = (IDatabaseFacadeDependencies)propDependencies.GetValue(db.Database);
    if (dependencies.DatabaseCreator is IRelationalDatabaseCreator databaseCreator)
    {
        databaseCreator.Create();
        databaseCreator.CreateTables();
    }
}