Entity Framework Database Initialization: Timeout

Question

I have an ASP.NET MVC application. When a new customer is created via CustomerController I run a new background task (using HostingEnvironment.QueueBackgroundWorkItem) to create a new Azure SqlDatabase for that customer.

I use Entity Framework Code First to create/initialize the new database. Here's the code:

// My ConnectionString
var con = "...";

// Initialization strategy: create db and execute all Migrations
// MyConfiguration is just a DbMigrationsConfiguration with AutomaticMigrationsEnabled = true
Database.SetInitializer(strategy: new MigrateDatabaseToLatestVersion<CustomerDataContext, MyConfiguration>(useSuppliedContext: true));

using (var context = new CustomerDataContext(con))
{
    // Neither 'Connection Timeout=300' in ConnectionString nor this line helps -> TimeoutException will rise after 30-40s
    context.Database.CommandTimeout = 300;

    // create the db - this lines throws the exception after ~40s
    context.Database.Initialize(true);
}

My Problem is that I always get a TimeoutException after about 40secs. I think that happens because Azure cannot initialize the new database within this short period of time. Don't get me wrong: The database will be created well by Azure but I want to wait for that point / get rid of the TimeoutException.

Edit1: I'm using Connection Timeout=300 in my ConnectionString but my app doesn't really care about that; after about 40s I'm always running into an SqlError.

Edit2: The exception that raises is an SqlException. Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Source: .Net SqlClient Data Provider

Edit3: I can confim now that this has nothing to do with ASP.NET/IIS. Even in a simple UnitTest method the code above fails.

Answer 1

The command timeout and the connection timeout are two different settings. In this case you only increase the commandtimeout. You can increase the connection timeout in the web.config: Connection Timeout=120. The only time you want to increase the connection timeout is when you are creating the database.

Answer 2

It seems that there is another CommandTimeout setting that is involved in database initialization process when using Code First Migrations. I want so share my solution here just in case anybody encounters this problem too.

Thanks to Rowan Miller for his hint pointing me to the solution.

Here's my code:

// Initialisation strategy
Database.SetInitializer(strategy: new CreateDatabaseIfNotExists<MyDataContext>());

// Use DbContext
using (var context = new MyDataContext(myConnectionString))
{
    // Setting the CommandTimeout here does not prevent the database
    // initialization process from raising a TimeoutException when using
    // Code First Migrations so I think it's not needed here.
    //context.Database.CommandTimeout = 300;

    // this will create the database if it does not exist
    context.Database.Initialize(force: false);
}

And my Configuration.cs class:

public sealed class Configuration : DbMigrationsConfiguration<MyDataContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;

        // Very important! Gives me enough time to wait for Azure
        // to initialize (Create -> Migrate -> Seed) the database.
        // Usually Azure needs 1-2 minutes so the default value of
        // 30 seconds is not big enough!
        CommandTimeout = 300;
    }
}