How to set Entity Framework Core migration timeout?

Question

I'm using the latest (1.0.0) version of EF Core. I have a migration to run on a quite big database.

I run:

dotnet ef database update -c ApplicationDbContext

And get:

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

In the connection string I explicitly set the timeout like so:

Connect Timeout=150000

Unfortunately, it didn't help. How should I do this?

Answer 1

The error message you are getting is for a Command timeout, not a connection timeout.

UPDATE

As mentioned by Pace in comments, since EF Core 2.0 you are able to use IDesignTimeDbContextFactory to change the behaviour of your context when it is being created by tooling at design time such as happens with Migrations.

Create a separate class in your project that implements the IDesignTimeDbContextFactory interface and use the DbContextoptionsBuilder to configure the behaviour you want - in this case, setting the command timeout value to 600 seconds:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace EFCoreSample.Model
{
    public class SampleContextFactory : IDesignTimeDbContextFactory<SampleContext>
    {
        public SampleContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<SampleContext>();
            optionsBuilder.UseSqlServer(@"Server=.\;Database=db;Trusted_Connection=True;",
                opts => opts.CommandTimeout((int)TimeSpan.FromMinutes(10).TotalSeconds));

            return new SampleContext(optionsBuilder.Options);
        }
    }
}

Make sure that your existing DbContext has a constructor that takes a DbContextOptions object as a parameter:

public AdventureContext(DbContextOptions options) : base(options){}

When the tooling runs the migration, it looks first for a class that implements IDesignTimeDbContextFactory and if found, will use that for configuring the context. Runtime behaviour is not affected.

Original Answer No Longer Applies

There is no way to set the CommandTimeout on a context when using EF commands. But you can set it globally in the constructor, and then remove it later if you don't need to keep it:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext()
    {
        Database.SetCommandTimeout(150000);
    }
}

Answer 2

You can set the timeout for migration only by setting the timeout on the context before calling the Migrations method:

using (var context = new DispatchingDbContext(_configuration))
{
    context.Database.SetCommandTimeout(300);
    await context.Database.MigrateAsync().ConfigureAwait(false);
}

Set timeout for migrations ef .netcore

Answer 3

You can do it also in the constructor of your database context class.

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
{
    Database.SetCommandTimeout(150000);
}

Answer 4

Using Entity Framework 6 (NOT CORE!), I set a longer timeout for migrations using the DbMigrationsConfiguration.CommandTimeout property.

Like this:

In my Global.asax.cs:

protected void Application_Start()
{
    DatabaseMigrationConfig.Register();
    //etc
}

My DatabaseMigrationConfig Class:

public class DatabaseMigrationConfig
{
    internal static void Register()
    {
        using (var context = new MyContext(Config.ConnectionStringMigrations))
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext,
                                        Migrations.Configuration>());
            context.Database.Initialize(false);
        }
    }
}

My Migrations.Configuration class:

using System.Data.Entity.Migrations;

internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;
        CommandTimeout = 360;// <----- 6 minute timeout!
    }
}

References:

Migrations: timeout error in Update-Database commands DbMigrationsConfiguration.CommandTimeout Property

Note that I also use a different connection string during migrations - the user has higher permissions than the website and the connection timeout is longer. See this question - How to use a different connection string (but same database) for migrations

Answer 5

You can generate the migration SQL script and run it on your own directly on the SQL server using this command:

dotnet ef migrations script [Baseline migration]

This way you won't be limited to timeout limitations.

More info can be found here.

To generate this script for Entity Framework 6, use:

Update-Database -Script -SourceMigration: [Baseline migration]

Answer 6

I improved Mike Brind's answer by reading the connection string from the appsettings file:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace EFCoreSample.Model
{
    public class SampleContextFactory : IDesignTimeDbContextFactory<SampleContext>
    {
        public SampleContext CreateDbContext(string[] args)
        {
            var env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");
            var settingsFileName = $"appsettings.{env}.json";
            
            if (!File.Exists(settingsFileName))
            {
                settingsFileName = "appsettings.json";
            }
            
            var connectionString = new ConfigurationBuilder().AddJsonFile(settingsFileName).Build().GetConnectionString("GlobalConnection");

            var optionsBuilder = new DbContextOptionsBuilder<SampleContext>();
            optionsBuilder.UseSqlServer(connectionString,
                opts => opts.CommandTimeout((int)TimeSpan.FromMinutes(10).TotalSeconds));

            return new SampleContext(optionsBuilder.Options);
        }
    }
}

Answer 7

Small modification to Patrick Koorevaar's answer to account for layered appsettings.json files...

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<MyDbContext>
{
    public MyDbContext CreateDbContext(string[] args)
    {
        var builder = new ConfigurationBuilder();
        
        var settingsFileName = "appsettings.json";
        if (File.Exists(settingsFileName))
        {
            builder.AddJsonFile(settingsFileName);
        }
        
        var env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");
        var envSettingsFileName = $"appsettings.{env}.json";
        if (File.Exists(envSettingsFileName))
        {
            builder.AddJsonFile(envSettingsFileName);
        }
        
        var connectionString = builder.Build().GetConnectionString("MyConnString");

        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
        optionsBuilder.UseSqlServer(connectionString,
            opts => opts.CommandTimeout((int)TimeSpan.FromMinutes(10).TotalSeconds));

        return new MyDbContext(optionsBuilder.Options);
    }
}