Entity Framework Timeouts

I am getting timeouts using the Entity Framework (EF) when using a function import that takes over 30 seconds to complete. I tried the following and have not been able to resolve this issue:

I added Default Command Timeout=300000 to the connection string in the App.Config file in the project that has the EDMX file as suggested here.

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 setting the CommandTimeout in my repository directly like so:

private TrekEntities context = new TrekEntities();

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

What else can I do to get the EF from timing out? This only happens for very large datasets. Everything works fine with small datasets.

Here is one of the errors I'm getting:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

OK - I got this working and it's silly what happened. I had both the connection string with Default Command Timeout=300000 and the CommandTimeout set to 180. When I removed the Default Command Timeout from the connection string, it worked. So the answer is to manually set the CommandTimeout in your repository on your context object like so:

this.context.CommandTimeout = 180;

Apparently setting the timeout settings in the connection string has no effect on it.

Solution 1:

There is a known bug with specifying default command timeout within the EF connection string.

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

Remove the value from the connection string and set it on the data context object itself. This will work if you remove the conflicting value from the connection string.

Entity Framework Core 1.0:

this.context.Database.SetCommandTimeout(180);

Entity Framework 6:

this.context.Database.CommandTimeout = 180;

Entity Framework 5:

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

Entity Framework 4 and below:

this.context.CommandTimeout = 180;

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