Entity Framework Timeouts

Problem

When utilizing the Entity Framework (EF), I’m getting timeouts when using a function import that takes more than 30 seconds to complete. I tried the following but was unable to resolve the problem:

As advised here, I added Default Command Timeout=300000 to the connection string in the App.Config file in the project with the EDMX file.

This is what my connection string looks like:

<add 
    name="MyEntityConnectionString" 
    connectionString="metadata=res://*/MyEntities.csdl|res://*/MyEntities.ssdl|
       res://*/MyEntities.msl;
       provider=System.Data.SqlClient;provider connection string=&quot;
       Data Source=trekdevbox;Initial Catalog=StarTrekDatabase;
       Persist Security Info=True;User ID=JamesTKirk;Password=IsFriendsWithSpock;
       MultipleActiveResultSets=True;Default Command Timeout=300000;&quot;"
    providerName="System.Data.EntityClient" />

I tried explicitly setting the CommandTimeout in my repository, like follows:

private TrekEntities context = new TrekEntities();

public IEnumerable<TrekMatches> GetKirksFriends()
{
    this.context.CommandTimeout = 180;
    return this.context.GetKirksFriends();
}

Is there anything else I can do to prevent the EF from timing out? This only occurs with really huge datasets. With small datasets, everything works properly.

One of the errors I’m getting is this:

OK, I got it to work, and it’s quite amusing what happened. Both the Default Command Timeout=300000 and the CommandTimeout were set to 180 in the connection string. I was able to get it to function after removing the Default Command Timeout from the connection string. So, in your repository, on your context object, manually change the CommandTimeout as follows:

this.context.CommandTimeout = 180;

Setting the timeout settings in the connection string appears to have no effect.

Asked by Halcyon

Solution #1

There is a known issue when using the EF connection string to provide the default command timeout.

http://bugs.mysql.com/bug.php?id=56806

Remove the value from the connection string and replace it with the value of the data context object. If you delete the conflicting value from the connection string, it will work.

this.context.Database.SetCommandTimeout(180);

this.context.Database.CommandTimeout = 180;

((IObjectContextAdapter)this.context).ObjectContext.CommandTimeout = 180;

this.context.CommandTimeout = 180;

Answered by 5 revs, 3 users 68%

Solution #2

If you are using a DbContext, use the following constructor to set the command timeout:

public class MyContext : DbContext
{
    public MyContext ()
    {
        var adapter = (IObjectContextAdapter)this;
        var objectContext = adapter.ObjectContext;
        objectContext.CommandTimeout = 1 * 60; // value in seconds
    }
}

Answered by saille

Solution #3

Alternatively, if you’re using DbContext and EF v6+, you can use:

this.context.Database.CommandTimeout = 180;

Answered by Paul

Solution #4

Usually I handle my operations within a transaction. As I’ve experienced, it is not enough to set the context command timeout, but the transaction needs a constructor with a timeout parameter. I had to set both time out values for it to work properly.

int? prevto = uow.Context.Database.CommandTimeout;
uow.Context.Database.CommandTimeout = 900;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(900))) {
...
}

At the end of the function I set back the command timeout to the previous value in prevto.

Using EF6

Answered by pillesoft

Solution #5

If you’re using Entity Framework like me, you’ll want to define the Time out on Startup class like this:

 services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), o => o.CommandTimeout(180)));

Answered by parismiguel

Post is based on https://stackoverflow.com/questions/6232633/entity-framework-timeouts