SQL Timeout when running an Entity Framework Migration

Question

I am currently experiencing a SQL Timeout when running a SQL() command inside of an EF Migration.

Situation: I am replacing a bunch (>50) tables with one table, and need to convert the data from those tables I'm about to drop into the new table. I've organized the Migration the following way:

1.Create the new table.

2. In the same Migration, use the SQL() function to run a sql script that migrates the data.

3.Drop all the old tables.

Currently, the migration gives the following error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

The error is happening in an environment where I give them an installer, and they run it without me involved, so I can't manually run individual migrations, and pause in the middle to run the SQL script.

Is there any way to change the timeout for a connection, or get around this issue?

Environment:

EF 6.0 Code First

SQL Server 2012

Answer 1

See this answer.

Use Configuration.cs file to set custom time out:

internal sealed class Configuration :

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

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

With this method, you can change the timeout only for migration and not everyone using your default connection string.

Answer 2

You should be able to set the connection timeout in the connection string, something like:

Connection Timeout=180;