CODEWITHSUNDEEP
c#.netasp.net-mvcentity-frameworkconnection-timeout
James
James

Reputation: 2871

Set database timeout in Entity Framework

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 ?

Upvotes: 182

Views: 303748

Answers (11)

Bob Sheehan
Bob Sheehan

Reputation: 160

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

Upvotes: 0

Wael Galal El Deen
Wael Galal El Deen

Reputation: 359

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.

Upvotes: -2

Vu Nguyen
Vu Nguyen

Reputation: 3665

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

It's pretty simple and no cast required.

Upvotes: 201

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102378

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

Upvotes: 220

Andrew Burrow
Andrew Burrow

Reputation: 19

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

Upvotes: 1

Hassan Muhammad Saad
Hassan Muhammad Saad

Reputation: 21

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

Upvotes: 1

Owen
Owen

Reputation: 4397

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

Upvotes: 10

Christian Gollhardt
Christian Gollhardt

Reputation: 17004

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.

Upvotes: 9

Timmerz
Timmerz

Reputation: 6199

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

Upvotes: 9

Ronnie Overby
Ronnie Overby

Reputation: 46460

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

Upvotes: 8

Erik Philips
Erik Philips

Reputation: 54618

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.

Upvotes: 21

Related Questions