Azure ElasticDB Integration for multitenancy

[GitOPa]

When using Elastic Pool in Azure with Azure SQL Database, creating a new tenant creates a tenant's database outside of the pool that the host database locates in. Should this feature fix this problem?

[alaminbs23]

The database will be created in an existing Elastic Pool, followed by the execution of Entity Framework migrations.

 public async Task CreateTenantDatabaseAsync(string tenantName)
{
    try
    {
        var configuration = _serviceProvider.GetRequiredService<IConfiguration>();
        var dbName = $"{ClientPortalConsts.TenantDatabasePrefix}{tenantName}";
        var defaultConnectionString = configuration.GetConnectionString("Default") ?? string.Empty;
        var elasticPoolName = configuration["DatabaseSettings:ElasticPoolName"] ?? string.Empty;

        // Create tenant database
        await CreateTenantDatabaseAsync(defaultConnectionString, dbName, elasticPoolName);

        // Apply migrations
        await _serviceProvider
            .GetRequiredService<ClientPortalDbContext>()
            .Database.MigrateAsync();
    }
    catch (Exception ex)
    {
        // Handle database creation error
        Console.WriteLine($"Error creating database for tenant {tenantName}: {ex.Message}");
        throw; // Rethrow the exception for handling at a higher level
    }
}

 private async Task CreateTenantDatabaseAsync(string connectionString, string tenantDatabaseName, string elasticPoolName)
{
    if (string.IsNullOrEmpty(tenantDatabaseName))
        throw new ArgumentException("tenantDatabaseName is required", nameof(tenantDatabaseName));

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

        bool isAzureSql = connection.DataSource.Contains("database.windows.net");
        string commandText;

        if (isAzureSql)
        {
            if (string.IsNullOrEmpty(elasticPoolName))
                throw new ArgumentException("elasticPoolName is required for Azure SQL", nameof(elasticPoolName));

            commandText = $@"
            IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{tenantDatabaseName}')
            BEGIN
                CREATE DATABASE [{tenantDatabaseName}] 
                ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = {elasticPoolName} ) )
            END";
        }
        else
        {
            commandText = $@"
            IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{tenantDatabaseName}')
            BEGIN
                CREATE DATABASE [{tenantDatabaseName}]
            END";
        }

        using (var command = new SqlCommand(commandText, connection))
        {
            await command.ExecuteNonQueryAsync();
        }
    }
}