Azure SQL Database serverless: retain the service tier when recreating a database

Recently I started using Azure SQL Database without server For my integration tests. But before each of my integration tests I have to delete my database and then recreate it so that it is independent.

But if I use the SQL DROP DATABLE function I lose all my Azure performance/pricing configuration.

The Solution

My solution to deleting is:

Delete foreign keys then delete the tables one by one

To do so I retrieve the list of tables through the INFORMATION table_here

void EnsureDeleted()
{
   var tableNames = RawSqlQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;");

   while (tableNames.Count != 0)
   {
      List<string> toRemove = new List<string>();
      // Cette table est ajouté automatiquement par Azure Database et on ne peux pas la supprimer.

      tableNames.Remove("database_firewall_rules");
      foreach (var tableName in tableNames)
      {
         // génère un script qui permet de supprimer la FK.
         var scriptsToRemoveFk = RawSqlQuery($@"
                        SELECT
                        'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
                        '.[' + OBJECT_NAME(k.parent_object_id) +
                        '] DROP CONSTRAINT ' + k.name
                        FROM sys.foreign_keys k
                        WHERE referenced_object_id = object_id('{tableName}')");

         foreach (var script in scriptsToRemoveFk)
         {
            _nuContext.Database.ExecuteSqlCommand(script);
         }

         #pragma warning disable EF1000 // Possible SQL injection vulnerability.
         _nuContext.Database.ExecuteSqlCommand("DROP TABLE [" + tableName + "]");
         #pragma warning restore EF1000 // Possible SQL injection vulnerability.
         toRemove.Add(tableName);
      }
      foreach (var tableName in toRemove)
      {
         tableNames.Remove(tableName);
      }
   }
}

The method of retrieving a string list from’a SQL query.

        private List<string> RawSqlQuery(string query)
        {
            using (var command = _nuContext.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                _nuContext.Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<string>();
                    while (result.Read())
                    {
                        entities.Add(result.GetString(0));
                    }

                    return entities;
                }
            }
        }

Happy coding !