Entity Framework Timeouts

Question

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.

Longing

Remove " from connection string – Celebes

refer to this as well https://mcmap.net/q/87859/-sql-exception-with-net-4-amp-ef – Dar

@hamlin11 In an EF connection string, that is required to define what part is connection string and what part is EF metadata. Leave " in the string. – Faustofaustus

my suggestion is before you increase the timeout would to investigate first to see why EF is timing out. In Our case we realised that we needed to add NONCLUSTERED indexes to some of the tables, this resolved the timeout issue for us. – Protactinium

I am working with MS support on a SQL time out issue - this is when the DB is hosted in SQL Azure. I was told all Azure PaaS services (PaaS websites and SQL Azure etc) there is a universal timeout of 230 seconds, and this always takes precedence, even if you set a timeout manually. This is to protect resources of multi-tenanted PaaS infrastructure. – Muriah

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

Somatic

How can I achieve this using edmx? – Paperboard

@Paperboard The EDMX model file doesn't expose these properties on the data context. You need to access the data context property using one of the methods above. – Faustofaustus

In which version of the EntityFramework is this fixed? I can't find the EF bug for it. – Shelbashelbi

@Shelbashelbi I don't believe any version fixes it. I haven't submitted a bug to the EF team so maybe nobody else has bothered to either. Feel free :) – Faustofaustus

I don't believe this is a bug, but rather by design, see Remarks section here link – Overtake

Because some settings are in ms and some in s, I looked it up here, CommandTimeout is in seconds. – Pollypollyanna

@MickP That's annoying. Seems to me they should check if one is supplied in the string first, then not set the timeout on the underlying data provider if one is present in the string. At the very least give the user some kind of error message about redundant timeouts or something. It just being ignored is super confusing, as is evidenced by the popularity of this question. Maybe it's better now but at one point specifying it in the string and manually with CommandTimeout would result in neither of them being respected. That's even more frustrating to figure out. – Faustofaustus

@Alex Ford do you know if the "known bug" has been fixed on .net 4.5? – Breger

@Breger Pretty sure it's still there. – Faustofaustus

Was the bug on the entity framework version or the .net framework version? – Breger

I haven't done .NET in a while but I believe the bug is with SQL Server. – Faustofaustus

In Entity Framework 7 you can set this in DbContext / IdentityDbContext's constructor: this.Database.SetCommandTimeout(180); – Crinoid

How do I set it for migrations only? – Flatboat

If anyone here is familar with .net core, i have a question which is about the timeout.. #44428095 – Dump

Setting the CommandTimeout property in the code solved my issue. I am using Entity Framework 5 to get data from a huge table in my SQL. I am usually got "Calling 'Read' when DataReader is closed", so I use the command below to fix the issue: ((IObjectContextAdapter)this.context).ObjectContext.CommandTimeout = 1800; – Assiduous

Wow... 5 versions of EF and 5 ways of setting CommandTimeout. None of the version fixed it in ConnectionString? – Romine

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

Cheremkhovo

@ErickPetru, so you can easily change it to a different number of minutes :), also I would not be too surprised if the compiler optimizes out that multiplication! – Offutt

@JoelVerhagen, do not be surprised. Here is a good explanation of when auto optimization occurs: #161348. In this case, I suppose that even happen (since they are two literal values​​), but honestly I think the code is kind of strange this way. – Denyse

meh...children are starving...who cares about 1*60? – Mcmillen

@ErikPetru, this is actually a very common practice and makes the code more readable. – Deth

What's the best way to handle this given that my DbContext derived class was auto generated from an edmx file? – Foreknow

@matt-burland, create another partial class for your generated DbContext and implement this in its constructor. – Diastrophism

^^ cant have constructor in a partial class, how would this workout ? – Permeance

I have the same reaction than Matt and Muds. What's the best way to handle this given that my DbContext derived class was auto generated from an edmx file? And cant have constructor in a partial class, how would this workout? – Fatling

Just sub class the generated class I guess. Of course if your queries are routinely timing out, you really need to investigate why, but sometime increasing the timeout can get you out of a tight spot. – Cheremkhovo

Well, if you want to make it readable, use (int)TimeSpan.FromMinutes(1).TotalSeconds – Consignor

For generated contexts, add the code to the .tt file (that generates the model's source code) – Rossetti

Answer 3

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

this.context.Database.CommandTimeout = 180;

Spearing

Answer 4

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"), 
     a => a.CommandTimeout(180)));

Ectogenous

Answer 5

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

Khajeh

Not a good approach at all. I used to add lot of transaction scope and it become a nightmare to me in a project. Eventually replaced all transaction scope with a single SAVEChanges() in EF 6+. Check this coderwall.com/p/jnniww/… – Screeching

This answer should have higher vote. I tried all different ways to increase the timeout but only when I set BOTH context command timeout and Transaction scope then it worked. – Caliphate

Answer 6

In .NET Core 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
    }
}

Jointless

Answer 7

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

Zaratite

Add your missing } at the end for the partial. – Passim

Thanks! This worked from my EF 4.4.0 project. Yes, old, but works! – Portland

This post seems to have a long life. I implemented something similar as a reflex but now I am trying to justify to myself why creating a separate function (your "SetCommandTimeOut") is a good idea. Can you help me out? – Eyelash

Answer 8

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

Boxwood

Answer 9

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.

Men

Answer 10

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

SET NOCOUNT ON;
SET ARITHABORT ON;

Roundworm