Run Integrations Tests on GitActions with MsSqlContainer - could not connect to SQL

[vmrsilva]

Running the integration tests locally everything runs successfully. However, when running the GitActions pipeline I get the error:

Error Message:
System.Exception : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

How can I confirm that the container has started?

public class TechChallangeApplicationFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly MsSqlContainer _sqlContainer = new MsSqlBuilder().Build();
    private readonly RedisContainer _redisContainer = new RedisBuilder().Build();

    private string? _connectionString;
    private string? _connectionStringRedis;

    protected override IHost CreateHost(IHostBuilder builder)
    {
        builder.ConfigureServices(services =>
        {
            var descriptorSql = services.SingleOrDefault(d =>
                d.ServiceType == typeof(DbContextOptions<TechChallangeContext>));

            if (descriptorSql != null)
            {
                services.Remove(descriptorSql);
            }

            services.AddDbContext<TechChallangeContext>(options =>
                options.UseSqlServer(_connectionString!)
            );


            var descriptorRedis = services.SingleOrDefault(options =>
                options.ServiceType == typeof(IDistributedCache));

            if (descriptorRedis != null)
            {
                services.Remove(descriptorRedis);
            }

            services.AddStackExchangeRedisCache(options =>
            {
                options.InstanceName = nameof(CacheRepository);
                options.Configuration = _connectionStringRedis;
            });

            services.AddScoped<ICacheRepository, CacheRepository>();
            services.AddScoped<ICacheWrapper, CacheWrapper>();

        });

        using var connection = new SqlConnection(_connectionString);


        var host = base.CreateHost(builder);

        return host;
    }

    public async Task InitializeAsync()
    {
        await _sqlContainer.StartAsync();
        _connectionString = _sqlContainer.GetConnectionString();
        Environment.SetEnvironmentVariable("ConnectionStrings.Database", _connectionString);
        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        await _redisContainer.StartAsync();
        _connectionStringRedis = _redisContainer.GetConnectionString();
        Environment.SetEnvironmentVariable("ConnectionStrings.Cache", _connectionStringRedis);


        await WaitForDatabaseAsync();

        using (var scope = Services.CreateScope())
        {
            var context = scope.ServiceProvider.GetRequiredService<TechChallangeContext>();
            await context.Database.EnsureCreatedAsync();

            var region = new RegionEntity("SP", "11");

            context.Region.Add(region);
            await context.SaveChangesAsync();
        }
    }

    public async Task DisposeAsync()
    {
        await _sqlContainer.StopAsync();
        await _redisContainer.StopAsync();
    }

    private async Task WaitForDatabaseAsync()
    {
        using var connection = new SqlConnection(_connectionString);
        for (int i = 0; i < 10; i++)
        {
            try
            {
                await connection.OpenAsync();
                return;
            }
            catch
            {
                await Task.Delay(1000);
            }
        }
        throw new Exception("Database did not answer.");
    }
}

[HofmeisterAn]

Removing DbContextOptions is not enough. You need to remove additional classes from the configuration. See the following article for more details.

However, I do not recommend this approach. Instead, use the Connection Strings approach (see Set Redis connection string). It is much easier to set up, cleaner, and aligns with what you typically use in production. Essentially, what you need is:

private sealed class CustomWebApplicationFactory : WebApplicationFactory<Program>
{
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseSetting("ConnectionStrings:MSSQL", _msSqlContainer.GetConnectionString());
    }
}

Then, retrieve the connection string from your web application builder configuration:

var builder = WebApplication.CreateBuilder(args);
var msSqlConnectionString = builder.Configuration.GetConnectionString("MSSQL");

This has the advantage that no additional configuration is necessary in your test setup. You only need to set the connection string accordingly.

[vmrsilva]

@HofmeisterAn , just one more doubt, I didn't understand where to use this:

var builder = WebApplication.CreateBuilder(args);
var msSqlConnectionString = builder.Configuration.GetConnectionString("MSSQL");

Thank you for your time!

[HofmeisterAn]

This is typically your app and is usually part of Program.cs.

[vmrsilva]

Tks, @HofmeisterAn ! 😄