DockerMsSqlServerDatabase.cs

internal class DockerMsSqlServerDatabase : IAsyncDisposable
{
    private const string Password = "!Passw0rd";
    private const string Image = "mcr.microsoft.com/mssql/server";
    private const string Tag = "2019-GA-ubuntu-16.04";

    private static IContainer _sqlServerContainer;
    private SemaphoreSlim semaphore = new(1, 1);
    private readonly string DatabaseName;

    private bool _deleted;
    private static readonly int ContainerPort = 1433;

    public static async Task<DockerMsSqlServerDatabase> Create(CancellationToken cancellationToken = default)
    {
        string dbName = "ovs-" + Guid.NewGuid();
        var db = new DockerMsSqlServerDatabase(dbName);
        await db.CreateAndStartContainer();
        await db.CreateDatabase(cancellationToken);
        return db;
    }

    private DockerMsSqlServerDatabase(string databaseName = null)
    {
        DatabaseName = databaseName;
    }

    private async Task CreateAndStartContainer()
    {
        if (_sqlServerContainer == null)
        {
            try
            {
                await semaphore.WaitAsync();

                if (_sqlServerContainer == null)
                {
                    _sqlServerContainer = new ContainerBuilder()
                        .WithImage($"{Image}:{Tag}")
                        .WithPortBinding(ContainerPort, assignRandomHostPort: true)
                        .WithEnvironment("ACCEPT_EULA", "Y")
                        .WithEnvironment("SA_PASSWORD", Password)
                        .WithCleanUp(cleanUp: true)
                        .WithWaitStrategy(Wait.ForUnixContainer()
                            .UntilOperationIsSucceeded(
                                () => HealthCheck(CancellationToken.None).GetAwaiter().GetResult(),
                                10))
                        .Build();

                    _sqlServerContainer.Stopping += OnStopping;

                    await _sqlServerContainer.StartAsync();
                }
            }
            catch (DockerImageNotFoundException)
            {
                throw new InvalidOperationException(
                    "SQL Server docker image not found. Did you run \"build.ps1 BuildSqlServerWithFtsImage\"");
            }
            finally
            {
                semaphore.Release();
            }
        }
    }

    private void OnStopping(object sender, EventArgs e)
    {
        try
        {
            semaphore.Wait();

            if (_sqlServerContainer != null)
            {
                _sqlServerContainer.Stopping -= OnStopping;
                _sqlServerContainer = null;
            }
        }
        finally
        {
            semaphore.Release();
        }
    }

    private static async Task<bool> HealthCheck(CancellationToken cancellationToken)
    {
        try
        {
            SqlConnection.ClearAllPools();

            await using var connection = CreateConnection();
            await connection.OpenAsync(cancellationToken);

            return true;
        }
        catch (Exception)
        {
            await Task.Delay(1.Seconds(), cancellationToken);
        }

        return false;
    }

    public string ConnectionString =>
        $"server=localhost,{PublicPort};database={DatabaseName};User Id=sa;Password={Password};Encrypt=false";

    private async Task CreateDatabase(CancellationToken cancellationToken = default)
    {
        SqlConnection.ClearAllPools();

        await using var connection = CreateConnection();
        await connection.OpenAsync(cancellationToken);

        var createCommand = $@"
            CREATE DATABASE [{DatabaseName}]
            ALTER DATABASE [{DatabaseName}] SET SINGLE_USER
            ALTER DATABASE [{DatabaseName}] SET COMPATIBILITY_LEVEL=110
            ALTER DATABASE [{DatabaseName}] SET MULTI_USER";

        await using var command = new SqlCommand(createCommand, connection);

        // HACK: should mitigate (slightly) the bug in MSSQL that prevents us from creating
        // new databases.
        // See https://github.com/Microsoft/mssql-docker/issues/344 for tracking issue.
        var CreatePolicy = Policy
            .Handle<SqlException>(e => e.Number == 5177)
            .WaitAndRetryAsync(new[]
            {
                TimeSpan.FromSeconds(1),
                TimeSpan.FromSeconds(4),
                TimeSpan.FromSeconds(6)
            });

        await CreatePolicy.ExecuteAsync(async () => { await command.ExecuteNonQueryAsync(cancellationToken); });
    }

    public ValueTask DisposeAsync()
    {
        if (_deleted)
        {
            return new ValueTask();
        }

        DeleteDatabase();

        return new ValueTask();
    }

    private void DeleteDatabase()
    {
        using (var connection = CreateConnection())
        {
            connection.Open();

            using (var command =
                   new SqlCommand($"ALTER DATABASE [{DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
                       connection))
            {
                command.ExecuteNonQuery();
            }

            using (var command = new SqlCommand($"DROP DATABASE [{DatabaseName}]", connection))
            {
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

        _deleted = true;
    }

    private static SqlConnection CreateConnection()
    {
        var masterConnectionString =
            $"server=localhost,{PublicPort};User Id=sa;Password={Password};Initial Catalog=master;Encrypt=false";
        var connectionStringBuilder = new SqlConnectionStringBuilder(masterConnectionString);
        return new SqlConnection(connectionStringBuilder.ConnectionString);
    }

    private static int PublicPort => _sqlServerContainer.GetMappedPublicPort(ContainerPort);
}

First of all: Thanks, this works great.

However, I ran into an issue testing a query that uses .Contains() in EF 8: "Incorrect syntax near '$'". After some doublechecking and searching I came across this answer https://stackoverflow.com/a/77374827 about a breaking change in EF 8 for older version of SQL Server. Bumping the COMPATIBILITY_LEVEL on line 117 up to 150 seems to fix the issue.

Just thought I'd mention it here in case anybody else runs into the same issue. 😇

Cool. Thank you for reporting that.