Code first自定义SQL迁移超时异常

Question

我正在尝试使用实体框架迁移通过执行自定义Sql来创建全文索引。

我的迁移类看起来像这样：

public partial class DocumentContentFullTextIndex : DbMigration
{
    public override void Up()
    {
        AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: 260));

        Sql("CREATE FULLTEXT CATALOG FullTextIndexes AS DEFAULT;", true);

        Sql(@"CREATE FULLTEXT INDEX ON [Attachments](
Content
    TYPE COLUMN ContentType
    Language 'ENGLISH'
)
KEY INDEX [PK_dbo.Attachments]
ON FullTextIndexes;", true);
    }

    public override void Down()
    {
        AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: null));

        Sql("DROP FULLTEXT INDEX ON [Attachments]");
        Sql("DROP FULLTEXT CATALOG FullTextIndexes");
    }
}

当我从MSSQL管理工具中运行时，一切都非常完美，SQL也正好做到了我期望的结果。

但是当从迁移项目中运行第二个Sql请求时，会触发异常。

超时。操作完成之前超时时间已过或服务器未响应。

带有-Verbose标志的完整堆栈跟踪：

Update-Database -ConnectionStringName DatabaseContext -Verbose
Using StartUp project 'Lx2'.
Using NuGet project 'Database.Model'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'Lx2' (DataSource: ., Provider: System.Data.SqlClient, Origin: Explicit).
Applying explicit migrations: [201406050348083_AttachmentsContentFullTextIndex].
Applying explicit migration: 201406050348083_AttachmentsContentFullTextIndex.
ALTER TABLE [dbo].[Attachments] ALTER COLUMN [ContentType] [nvarchar](260) NULL
CREATE FULLTEXT CATALOG FullTextIndexes AS DEFAULT;
CREATE FULLTEXT INDEX ON [Attachments](
Content
    TYPE COLUMN ContentType
    Language 'ENGLISH'
)
KEY INDEX [PK_dbo.Attachments]
ON FullTextIndexes;
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   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)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
ClientConnectionId:3d298f0a-e2dc-4976-8587-c69d03b23c6b
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

我尝试直接从详细输出中复制所有SQL并在管理工具中直接执行它，结果正如预期一样，没有错误。

有什么想法可以解决这个问题吗？

奇怪的是，如果我放置ContentType最大长度更改代码，

AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: 260));

在单独的迁移文件中，一切也正常工作。

更新:

根据shivakumar的建议，我尝试增加迁移配置中的连接超时时间(最长为5分钟)，这样可以延长接收“超时异常”之前的时间，但问题仍然存在。

- Nick

---

请您标记@hovhannes-hakobyan的回答为已接受吗？这对我有用。 - regisbsb

Answer 1

使用 Configuration.cs 文件来设置自定义超时时间：

internal sealed class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        ContextKey = "YourDbContext";

        // New timeout in seconds
        this.CommandTimeout = 60 * 5; 
    }
}

- Hovhannes Hakobyan

---

这个救了我！其他的配置（连接字符串或在context.Database.CommandTimeout中设置）在EF6中进行迁移时都不起作用。 - Marc

如果我从程序包管理器控制台运行update-database命令，即使我设置了更长的超时时间，它也会在5分钟后失败。但是，如果我从程序（控制台应用程序）中运行，则可以正常工作。 - Stanislav

这个构造函数是如何被调用的？它应该放在哪个命名空间中？ - Extragorey

如果您已经在使用EF6迁移，那么这个配置文件将已经存在于您的迁移文件夹中。这个答案的关键点是在构造函数中添加超时时间，该类已经存在。 - Tim

@Extragorey只需在整个项目/解决方案中搜索DbMigrationsConfiguration，您就能找到这个文件。 - undefined

Answer 2

您的请求处理可能比实体框架（30秒）的默认命令超时时间更长。

如果需要，可以像下面的线程一样增加命令超时时间。

实体框架超时

- shivakumar

---

谢谢您的帮助。我尝试将超时设置为5分钟，这将将时间增加到5分钟，直到出现“超时异常”，因此我认为我做得正确，但异常仍然存在:( - Nick