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.

回答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;

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

回答3:

If you are using DbContext and EF v6+, alternatively you can use:

this.context.Database.CommandTimeout = 180;

回答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

回答5:

I know this is very old thread running, but still EF has not fixed this. For people using auto-generated DbContext can use the following code to set the timeout manually.

public partial class SampleContext : DbContext
{
    public SampleContext()
        : base(\"name=SampleContext\")
    {
        this.SetCommandTimeOut(180);
    }

    public void SetCommandTimeOut(int Timeout)
    {
        var objectContext = (this as IObjectContextAdapter).ObjectContext;
        objectContext.CommandTimeout = Timeout;
    }

回答6:

This is what I\'ve fund out. Maybe it will help to someone:

So here we go:

If You use LINQ with EF looking for some exact elements contained in the list like this:

await context.MyObject1.Include(\"MyObject2\").Where(t => IdList.Contains(t.MyObjectId)).ToListAsync();

everything is going fine until IdList contains more than one Id.

The “timeout” problem comes out if the list contains just one Id. To resolve the issue use if condition to check number of ids in IdList.

Example:

if (IdList.Count == 1)
{
    result = await entities. MyObject1.Include(\"MyObject2\").Where(t => IdList.FirstOrDefault()==t. MyObjectId).ToListAsync();
}
else
{
    result = await entities. MyObject1.Include(\"MyObject2\").Where(t => IdList.Contains(t. MyObjectId)).ToListAsync();
}

Explanation:

Simply try to use Sql Profiler and check the Select statement generated by Entity frameeork. …

回答7:

If you are using Entity Framework like me, you should define Time out on Startup class as follows:

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

回答8:

If you are using DbContext and EF v6+, you can use CommandTimeout property

this.context.Database.CommandTimeout = 180;