连接Azure时设置实体框架超时问题

Question

我有一个Azure数据库，正在设置Entity Framework。我认为连接字符串是正确的...

<configSections>
   <section name="entityFramework"   type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
</configSections>
<connectionStrings>
     <add name="MyContext" connectionString="Server=tcp:azure_server_name.database.windows.net,1433;Database=azure_database_name;User ID=azure_user_account@azure_server_name;Password=azure_password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
          providerName="System.Data.SqlClient" />
</connectionStrings>

...但我一直收到以下超时错误。

"Message":"An error has occurred.",
"ExceptionMessage":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.",
"ExceptionType":"System.InvalidOperationException",
"StackTrace":null,
"InnerException":{
   "Message":"An error has occurred.",
   "ExceptionMessage":"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.",
   "ExceptionType":"System.Data.SqlClient.SqlException",
   "StackTrace":"   
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)\r\n   
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n   
    at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   
    at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   
    at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   
    at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass1a.<CreateDatabaseFromScript>b__19(DbConnection conn)\r\n  
    at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32()\r\n   
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()\r\n   
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)\r\n   
    at System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action`1 act)\r\n   
    at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)\r\n   
    at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript)\r\n   
    at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)\r\n   
    at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)\r\n   
    at System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase()\r\n   
    at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)\r\n   
    at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)\r\n   
    at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)\r\n   
    at System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext)\r\n   
    at System.Data.Entity.Internal.InternalContext.CreateDatabase(ObjectContext objectContext, DatabaseExistenceState existenceState)\r\n   
    at System.Data.Entity.Database.Create(DatabaseExistenceState existenceState)\r\n   
    at System.Data.Entity.DropCreateDatabaseAlways`1.InitializeDatabase(TContext context)\r\n   
    at System.Data.Entity.Internal.InternalContext.<>c__DisplayClassf`1.<CreateInitializationAction>b__e()\r\n   
    at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)\r\n   
    at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()\r\n   
    at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)\r\n   
    at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)\r\n 
    at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)\r\n   
    at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()\r\n   
    at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)\r\n   
    at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()\r\n   
    at System.Data.Entity.Internal.Linq.InternalSet`1.GetEnumerator()\r\n   
    at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.IEnumerable.GetEnumerator()\r\n   
    at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n   
    at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n   
    at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)\r\n   
    at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)\r\n   
    at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n   
    at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n   
    at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)\r\n   
    at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   
    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   
    at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()",
   "InnerException":{
      "Message":"An error has occurred.",
      "ExceptionMessage":"The wait operation timed out",
      "ExceptionType":"System.ComponentModel.Win32Exception",
      "StackTrace":null
   }}}

**Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.**

我已将Azure防火墙对我的IP地址打开...并通过调试器运行此代码。

以下是应该执行的一小段代码...

public class MyContextInitializer : DropCreateDatabaseAlways<MyContext>
{
    protected override void Seed(MyContext context)
    {
        context.Database.CommandTimeout = 0;

        var books = new List<Book>
        {
            new Book() {Name = "War and Pease", Author = "Tolstoy",Price=19.95m },
            new Book() {Name = "As I Lay Dying", Author = "Faulkner",Price=99.95m },
            new Book() {Name = "Harry Potter 1", Author = "J.K. Rowling",Price=19.95m },
            new Book() {Name = "Pro Win 8", Author = "Liberty",Price=49.95m },
            new Book() {Name = "Book one", Author = "Author1",Price=10.95m },
            new Book() {Name = "Book two", Author = "Author2",Price=20.95m },
            new Book() {Name = "Book three", Author = "Author3",Price=30.95m }
        };
        books.ForEach(b => context.Books.Add(b));
        context.SaveChanges();
        base.Seed(context);
    }
}

-----------------按照请求添加书籍结构和MyContext-------------------

public class Book
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Author { get; set; }
    public decimal Price { get; set; }

}


public class MyContext : DbContext
{
    public MyContext() : base("name=MyContext")
    {

    }

    public DbSet<Book> Books { get; set; }

}

- Mark

---

嗨，@Mark。你能否请发布一下你的 Book 类的结构。 - Shaun Luttin

你正在使用哪个版本的EntityFramework？ - Shaun Luttin

@ShaunLuttin EF版本6，不需要2个DbContexts。假设它们都是MyContext - 我已经更新了代码示例。 - Mark

好的 :) 然后，看着你的错误信息，似乎 "The 'ObjectContent 1' type failed to serialize the response body for content type 'application/json; charset=utf-8'." 是问题的一部分。你能否也发布 MyDbContext 类的代码？ - Shaun Luttin

@ShaunLuttin 我添加了DbContext代码。我的研究让我相信我需要使用类似于TransientFaultHandling的东西。 - Mark

Answer 1

简短回答

SQL Azure创建一个数据库需要大约120秒的时间，这就是你遇到超时的原因。所以，增加CommandTimeout时间。一种方法是将一行代码添加到你的DbContext构造函数中。

public MyContext() : base("name=MyContext")
{
    // three minute command timeout
    this.Database.CommandTimeout = 180;
}

故障排除步骤

我刚刚在West US创建了一个全新的SQL Azure服务器，重新产生了异常。当我打开实体框架日志记录时，输出显示Create database语句引起了超时。以下是日志输出。

Opened connection at 9/8/2015 9:24:14 AM -07:00
select cast(serverproperty('EngineEdition') as int)
-- Executing at 9/8/2015 9:24:14 AM -07:00
-- Completed in 40 ms with result: SqlDataReader

Closed connection at 9/8/2015 9:24:14 AM -07:00
Opened connection at 9/8/2015 9:24:15 AM -07:00
IF db_id(N'mvp1') IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=N'mvp1'
-- Executing at 9/8/2015 9:24:15 AM -07:00
-- Completed in 306 ms with result: 1

Closed connection at 9/8/2015 9:24:15 AM -07:00
Opened connection at 9/8/2015 9:24:15 AM -07:00
drop database [mvp1]
-- Executing at 9/8/2015 9:24:15 AM -07:00
-- Completed in 10635 ms with result: -1

Closed connection at 9/8/2015 9:24:26 AM -07:00
Opened connection at 9/8/2015 9:24:26 AM -07:00
create database [mvp1]
-- Executing at 9/8/2015 9:24:26 AM -07:00
-- Failed in 30050 ms with error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Closed connection at 9/8/2015 9:24:56 AM -07:00

为了进一步排除故障，我使用SQL Server Management Studio连接到SQL Azure数据库并运行日志输出。命令执行成功，但需要1:53（约两分钟）的时间才能完成。这超出了默认的DbContext命令超时时间。

解决方法是将DbContext命令超时时间增加到该持续时间以上。搞定！它奏效了。

我的电脑上工作的控制台应用程序

packages.config

唯一的包是EntityFramework版本6.1.3。

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.1.3" targetFramework="net452" />
</packages>

App.config

这个配置使用interceptors部分来记录生成的T-SQL语句。这使我能够在SQL Server Management Studio上运行记录的T-SQL，以确定创建数据库需要多长时间。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <connectionStrings>    
    <add name="MyContext" 
         connectionString="Server=tcp:d01kzjctcf.database.windows.net,1433;Database=mvp1;User ID=mvp@d01kzjctcf;Password=3pN*iV55XmtW;Trusted_Connection=False;Encrypt=True;Connection Timeout=120;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
    <interceptors>
      <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
        <parameters>
          <parameter value="C:\Temp\LogOutput.txt"/>
        </parameters>
      </interceptor>
    </interceptors>
  </entityFramework>
</configuration>

Program.cs

这个演示程序的作用是删除并重新创建数据库，添加书籍数据，查询所有书籍，并将书籍数量输出到控制台。

using System;
using System.Data.Entity;
using System.Collections.Generic;
using System.Data.Entity.SqlServer;

public class Program
{
    public static void Main()
    {
        Database.SetInitializer<MyContext>(new MyContextInitializer());
        var count = 0;
        using (var context = new MyContext())
        {
            count = context.Books.CountAsync().Result;
        }
        Console.WriteLine(string.Format("There are {0} books.", count));
        Console.ReadLine();
    }
}

public class MyContextInitializer : DropCreateDatabaseAlways<MyContext>
{
    protected override void Seed(MyContext context)
    {
        context.Database.CommandTimeout = 0;

        var books = new List<Book>
        {
            new Book() {Name = "War and Pease", Author = "Tolstoy",Price=19.95m },
            new Book() {Name = "As I Lay Dying", Author = "Faulkner",Price=99.95m },
            new Book() {Name = "Harry Potter 1", Author = "J.K. Rowling",Price=19.95m },
            new Book() {Name = "Pro Win 8", Author = "Liberty",Price=49.95m },
            new Book() {Name = "Book one", Author = "Author1",Price=10.95m },
            new Book() {Name = "Book two", Author = "Author2",Price=20.95m },
            new Book() {Name = "Book three", Author = "Author3",Price=30.95m }
        };
        books.ForEach(b => context.Books.Add(b));
        context.SaveChanges();
        base.Seed(context);
    }
}

public class MyContext : DbContext
{
    public MyContext() : base("name=MyContext")
    {
        this.Database.CommandTimeout = 180;
    }

    public DbSet<Book> Books { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Author { get; set; }
    public decimal Price { get; set; }
}

参见

在Entity Framework中设置数据库超时时间

如何查看由Entity Framework生成的SQL语句？

结束语

一个全局的CommandTimeout三分钟可能只适用于开发环境。在生产环境中，您可能需要将其减少或仅针对某些特定的长时间运行命令（例如数据库创建）设置。

- Shaun Luttin

---

哇，这太简单了。我在本周早些时候尝试了timeout命令，但我把它放在代码的其他地方，所以它没有起作用。今晚我再次尝试，第一次就成功了。感谢你的帮助，肖恩！ - Mark

我已经将CommandTimeout和Connection Timeout都增加到了300，但在数据库创建之前仍然出现超时错误。日志输出显示它在30秒后超时，我确认createDatabase需要120秒。似乎由于某种原因，超时时间仍为30秒。有什么想法吗？ - Liam Weston

@Liam，如果你正在进行迁移，有一个单独的设置。设置上下文命令超时将不会有任何影响（https://msdn.microsoft.com/en-us/library/system.data.entity.migrations.dbmigrationsconfiguration.commandtimeout）。 - cdonner

谢谢你 - 这让我非常困惑。 - Rodney