How create a database in azure elastic pool with entity framework core?

Question

I would like to create a database with entity Framework core that would be automatically added to my azure elactic pool.

I do that with a DatabaseFacadeExtension that execute SQL command after the db creation like suggested here: Azure SQL Server Elastic Pool - automatically add database to pool

public static async Task<bool> EnsureCreatedAsync(this DatabaseFacade databaseFacade, string elasticPoolName, CancellationToken cancellationToken = default)
        {
            if (!await databaseFacade.EnsureCreatedAsync(cancellationToken)) return false;

            // the database has been created.

            var dbName = databaseFacade.GetDbConnection().Database;

            try
            {
                cancellationToken.ThrowIfCancellationRequested();

                if (!string.IsNullOrEmpty(elasticPoolName))
                {
                    await databaseFacade.ExecuteSqlCommandAsync(new RawSqlString(
                            $"ALTER DATABASE {dbName} MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [{elasticPoolName}] ));"),
                        cancellationToken);
                }

                return true;

            }
            catch
            {
                await databaseFacade.EnsureDeletedAsync(cancellationToken);
                throw;
            }

        }

It's works but I will prefer an atomic operation where the database would be created directly in the Azure Elastic Pool.

sabhari karthik sabhari karthik · Accepted Answer · 2021-05-07T08:45:10

I had a very similar issue. Fortunately, I took cues from the previous answer and I improvised on it to arrive at a solution.

I had a common database to manage the application and whenever a new client onboards, I need to create a new database. So, I had to maintain multiple database contexts in my .NET core application. Also, I had migrations for the clientContext ready in my codebase, which just needed

client_db.Database.MigrateAsync();

to create the database. But, I couldn't create it directly under elastic pool as Azure doesn't have any default settings which support that. So, MigrateAsync always created the database outside the pool.

So, I created the database within the pool using T-SQL command from my common database context, followed by MigrateAsync() to migrate all the required schema.

var commandText = "CREATE DATABASE client1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = demoPool ) );";
db.Database.ExecuteSqlCommand(commandText);
clientContext client_db = new clientContext(approved_corporate.Id, _configuration);
client_db.Database.MigrateAsync();

Also I had a custom Constructor in my clientContext to support this:

public clientContext(int client_id, IConfiguration configuration = null)
{
    string client_code = "client" + client_id.ToString();
    connection_string = configuration["ConnectionStrings:Client"].ToString().Replace("client_code", client_code);
}

How create a database in azure elastic pool with entity framework core?

2 Answers