[c#] 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.

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

In .Net Core (NetCore) use the following syntax to change the timeout from the default 30 seconds to 90 seconds:

public class DataContext : DbContext
{
    public DataContext(DbContextOptions<DataContext> options) : base(options)
    {
        this.Database.SetCommandTimeout(90); // <-- 90 seconds
    }
}

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

For Entity framework 6 I use this annotation and works fine.

  public partial class MyDbContext : DbContext
  {
      private const int TimeoutDuration = 300;

      public MyDbContext ()
          : base("name=Model1")
      {
          this.Database.CommandTimeout = TimeoutDuration;
      }
       // Some other codes
    }

The CommandTimeout parameter is a nullable integer that set timeout values as seconds, if you set null or don't set it will use default value of provider you use.

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. …

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

this.context.Database.CommandTimeout = 180;

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

Adding the following to my stored procedure, solved the time out error by me:

SET NOCOUNT ON;
SET ARITHABORT ON;

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