I want to use test containers for integration tests but I cannot find any resources for setting it up in a database first approach. I have a SQL Server .sqlproj file within my project but I cannot figure out how to use that to set up the container database. I’m hoping there’s a way to do this or maybe setup migrations only for the test container.

So far the await context.Database.EnsureCreatedAsync(); creates the tables but doesn’t create the views since it’s based on the DbContext (I’m assuming).

public class DatabaseFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container;
    public IServiceProvider ServiceProvider { get; private set; }

    public DatabaseFixture()
    {
        _container = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:latest")
            .Build();
    }

    public async Task InitializeAsync()
    {
        await _container.StartAsync();

        var masterConnectionString = _container.GetConnectionString();
        var connectionStringBuilder = new SqlConnectionStringBuilder(masterConnectionString)
        {
            InitialCatalog = "master"
        };

        using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
        {
            await connection.OpenAsync();

            using (var command = new SqlCommand("CREATE DATABASE MyDatabase", connection))
            {
                await command.ExecuteNonQueryAsync();
            }
        }

        connectionStringBuilder.InitialCatalog = "MyDatabase";

        var serviceCollection = GetServices(connectionStringBuilder.ConnectionString);
        ServiceProvider = serviceCollection.BuildServiceProvider();

        using var scope = ServiceProvider.CreateScope();
        var scopedServices = scope.ServiceProvider;
        var context = scopedServices.GetRequiredService<MyDbContext>();

        await context.Database.EnsureCreatedAsync();
    }

    public async Task DisposeAsync()
    {
        using var scope = ServiceProvider.CreateScope();
        var scopedServices = scope.ServiceProvider;
        var context = scopedServices.GetRequiredService<MyDbContext>();
        await context.Database.EnsureDeletedAsync();
        await _container.StopAsync();
    }

    private IServiceCollection GetServices(string connectionString)
    {
        var services = new ServiceCollection();

        services.AddTransient<IMyServices, MyServices>();

        services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connectionString));
        services.AddScoped(s => new MyDbContext(connectionString));

        return services;
    }
}