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.
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