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.
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;
}
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With