实体框架数据库初始化：初始化新的Azure SqlDatabase时超时 [英] Entity Framework Database Initialization: Timeout when initializing new Azure SqlDatabase

问题描述

我有一个ASP.NET MVC应用程序。当通过 CustomerController 创建新客户时，我运行一个新的后台任务（使用HostingEnvironment.QueueBackgroundWorkItem ）为该客户创建一个新的Azure SqlDatabase。

我使用实体框架代码首先创建/初始化新的数据库。以下是代码：

  //我的ConnectionString 
 var con =... 
 
 //初始化策略：创建数据库并执行所有迁移
 // MyConfiguration只是一个DbMigrationsConfiguration，具有AutomaticMigrationsEnabled = true 
 Database.SetInitializer（strategy：new MigrateDatabaseToLatestVersion< CustomerDataContext，MyConfiguration> ;（useSuppliedContext：true））; 
 
 using（var context = new CustomerDataContext（con））
 {
 // ConnectionString中没有Connection Timeout = 300，这行也不帮助 - > TimeoutException将在30-40s 
上下文之后上升.Database.CommandTimeout = 300; 
 
 //创建数据库 - 这行在〜40s 
之后抛出异常上下文.Database.Initialize（true）; 
} 
  

我的问题是我总是得到一个 TimeoutException 约40秒后。我认为这是因为Azure在这个短时间内无法初始化新的数据库。不要误会我的数据库，Azure将会很好的创建数据库，但是我想等一下/摆脱 TimeoutException 。

编辑1：
我在ConnectionString中使用 Connection Timeout = 300 ，但我的应用程序并不在乎;大约40年后，我总是遇到一个SqlError。

Edit2：
引发的异常是一个 SqlException 即可。 讯息：超时已过期。在完成操作或服务器之前经过的超时时间没有响应。来源：.Net SqlClient数据提供者

Edit3：
我现在可以确定这没有什么与ASP.NET / IIS有关。即使在一个简单的UnitTest方法中，上述代码失败。

解决方案

似乎还有另一个 CommandTimeout 设置涉及到在数据库初始化过程中使用Code First Migrations。我想在这里分享我的解决方案，以防任何人也遇到这个问题。

感谢

这是我的代码：

  //初始化策略
 Database.SetInitializer（strategy：new CreateDatabaseIfNotExists< MyDataContext>（））; 
 
 //使用DbContext 
使用（var context = new MyDataContext（myConnectionString））
 {
 //设置CommandTimeout在这里不会阻止数据库
 //使用
时提高TimeoutException的初始化过程//代码第一次迁移，所以我认为这里不需要。 
 //context.Database.CommandTimeout = 300; 
 
 //如果不存在，这将创建数据库
 context.Database.Initialize（force：false）; 
} 
  

我的Configuration.cs类：

  public sealed class配置：DbMigrationsConfiguration< MyDataContext> 
 {
 public Configuration（）
 {
 AutomaticMigrationsEnabled = false; 
 AutomaticMigrationDataLossAllowed = false; 
 
 //很重要！给我足够的时间等待Azure 
 //初始化（创建 - >迁移 - >种子）数据库。 
 //通常Azure需要1-2分钟，所以默认值
 // 30秒不够大！ 
 CommandTimeout = 300; 
} 
} 
  

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

这篇关于实体框架数据库初始化：初始化新的Azure SqlDatabase时超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！