Set database timeout in Entity Framework

Question

My command keeps timing out, so I need to change the default command timeout value.

I've found myDb.Database.Connection.ConnectionTimeout, but it's readonly.

How can I set the command timeout in Entity Framework 5 ?

Answer 1

Try this on your context:

public class MyDatabase : DbContext
{
    public MyDatabase ()
        : base(ContextHelper.CreateConnection("Connection string"), true)
    {
        ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 180; // seconds
    }
}

---

If you want to define the timeout in the connection string, use the Connection Timeout parameter like in the following connection string:

<connectionStrings>

<add name="AdventureWorksEntities"
connectionString="metadata=.\AdventureWorks.csdl|.\AdventureWorks.ssdl|.\AdventureWorks.msl;
provider=System.Data.SqlClient;provider connection string='Data Source=localhost;
Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60;
multipleactiveresultsets=true'" providerName="System.Data.EntityClient" />

</connectionStrings>

Source: How to: Define the Connection String

Answer 2

You can use DbContext.Database.CommandTimeout = 180; // seconds

It's pretty simple and no cast required.

Answer 3

My partial context looks like:

public partial class MyContext : DbContext
{
    public MyContext (string ConnectionString)
        : base(ConnectionString)
    {
        this.SetCommandTimeOut(300);
    }

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

I left SetCommandTimeOut public so only the routines I need to take a long time (more than 5 minutes) I modify instead of a global timeout.

Answer 4

In the generated constructor code it should call OnContextCreated()

I added this partial class to solve the problem:

partial class MyContext: ObjectContext
{
    partial void OnContextCreated()
    {
        this.CommandTimeout = 300;
    }
}

Answer 5

I extended Ronnie's answer with a fluent implementation so you can use it like so:

dm.Context.SetCommandTimeout(120).Database.SqlQuery...

public static class EF
{
    public static DbContext SetCommandTimeout(this DbContext db, TimeSpan? timeout)
    {
        ((IObjectContextAdapter)db).ObjectContext.CommandTimeout = timeout.HasValue ? (int?) timeout.Value.TotalSeconds : null;

        return db;
    }

    public static DbContext SetCommandTimeout(this DbContext db, int seconds)
    {
        return db.SetCommandTimeout(TimeSpan.FromSeconds(seconds));
    } 
}

Answer 6

For Database first Aproach:

We can still set it in a constructor, by override the ContextName.Context.tt T4 Template this way:

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext
{
    public <#=code.Escape(container)#>()
        : base("name=<#=container.Name#>")
    {
        Database.CommandTimeout = 180;
<#
if (!loader.IsLazyLoadingEnabled(container))
{
#>
        this.Configuration.LazyLoadingEnabled = false;
<#
}

Database.CommandTimeout = 180; is the acutaly change.

The generated output is this:

public ContextName() : base("name=ContextName")
{
    Database.CommandTimeout = 180;
}

If you change your Database Model, this template stays, but the actualy class will be updated.

Answer 7

Same as other answers, but as an extension method:

static class Extensions
{
    public static void SetCommandTimeout(this IObjectContextAdapter db, TimeSpan? timeout)
    {
        db.ObjectContext.CommandTimeout = timeout.HasValue ? (int?) timeout.Value.TotalSeconds : null;
    }
}

Answer 8

You can use this simple :
dbContext.Database.SetCommandTimeout(300);

Answer 9

I just ran in to this problem and resolved it by updating my application configuration file. For the connection in question, specify "Connection Timeout=60" (I am using entity framework version 5.0.0.0)

ConnectionTimeout Setting

Answer 10

In my case the Connection string property is readonly. Also, the entities constructor is autogenerated so I didn't want to put it in there. Plus, putting in the constructor applied to all and I only needed it on one sproc.

Below is my workaround

try
     { using (MyEntities Mydb = new MyEntities())
       {
         (Mydb as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext.CommandTimeout = 600;
         Mydb.LongRunningSproc();
        }
      }
  catch (System.Data.Common.DbException ex)
      {
          throw new Exception(SomeMessageHere);
      }

Answer 11

You should make the changes in the Connection String tag in the web config and make the EntityFrameWork read from it in its contructor.

1. Add this term to the web.config Connection String: Connection Timeout=300;

2. Add the following code in the constructor:

   Database.CommandTimeout = Database.Connection.ConnectionTimeout;

By this approach you will make the user able to control the time out without make a new publish for him.