CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Andrzej Gis
Andrzej Gis

Reputation: 14316

How to set Entity Framework Core migration timeout?

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?

Upvotes: 56

Views: 54957

Answers (8)

Matthew Holmes
Matthew Holmes

Reputation: 802

From EF Core 7 you now don't need to use the DesignTimeFactory to determine if it is the migrations running vs the application. The DesignTimeFactory is a pain because of all the Configuration setup.

Now you can do the following in your context:

public class MyDbContext : DbContext
{
    private readonly IConfiguration _config;

    public MyDbContext(DbContextOptions options, IConfiguration config) : base(options)
    {
        _config = config;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (EF.IsDesignTime)
        {
            optionsBuilder.UseSqlServer(_config.GetConnectionString("DefaultConnection"), opt => opt.CommandTimeout(600));
        }
        base.OnConfiguring(optionsBuilder);
    }

Replace "DefaultConnection" with the connection string's key from your appsettings file.

As a side-note, most of the solutions including this one, don't work when running a migration bundle .exe file. You seem to have to set the timeout in the connection string in that case. I haven't checked if that's because the context doesn't even exist in the bundle or if it's considered to not be design time

Upvotes: 5

Jeff Brewster
Jeff Brewster

Reputation: 1

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

Upvotes: 0

Patrick Koorevaar
Patrick Koorevaar

Reputation: 1343

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

Upvotes: 0

Nir
Nir

Reputation: 2082

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]

Upvotes: 1

Colin
Colin

Reputation: 22595

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

Upvotes: 1

Mike Brind
Mike Brind

Reputation: 30110

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

Upvotes: 84

Nishan
Nishan

Reputation: 4441

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

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

Upvotes: 11

Peter
Peter

Reputation: 27944

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

Upvotes: 41

Related Questions