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.

score 1 · Answer 1 · edited May 07 '21 at 08:50

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);
}

score 0 · Answer 2 · answered Apr 12 '19 at 09:17

0

Azure Elastic Pool supports you creates a new database in an existing pool or as a single database. You must be connected to the master database to create a new database.

For more details, please see: Transact-SQL: Manage pooled databases.

Example T-SQL Code:

Creating a Database in an Elastic Pool:

CREATE DATABASE db1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = S3M100 ) ) ;

Please see: Azure SQL Database single database/elastic pool

You can replace the T-SQL statement and try again.

Hope this helps

answered Apr 12 '19 at 09:17

Leon Yue

15,693
1
11
23

Yes but I'm looking for to create database with entity Framework (with DbContext) like that. Not with a transact-SQL command. – Kassoul Apr 12 '19 at 13:53
I'm sorry that I haven't found the example about create database with entity Framework (with DbContext). But you can reference these tutorial [Create code to query the SQL database](https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-dotnet-visual-studio?toc=%2Fen-us%2Fdotnet%2Fazure%2FTOC.json&bc=%2Fen-us%2Fdotnet%2Fazure_breadcrumb%2Ftoc.json&view=azure-dotnet#create-code-to-query-the-sql-database), using the create statement and achieve your purpose. If my answer could be helps, can you please accept it? Thanks and have a good day. – Leon Yue Apr 15 '19 at 02:03

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

2 Answers2