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 ?

Solution 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

Solution 2

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

It's pretty simple and no cast required.

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

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

Solution 5

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.

View more solutions

Comments

[itsho]

FYI, On EF6, Database.CommandTimeout is no longer read-only

[David Ferenczy Rogožan]

@itsho He was talking about Database.Connection.ConnectionTimeout. Anyway, I would say that Database.CommandTimeout is the right thing in the case your query is time-outing (exception System.Data.Entity.Core.EntityCommandExecutionException containing System.Data.SqlClient.SqlException: Timeout expired.).

[Tim Pohlmann]

Possible duplicate of Entity Framework Timeouts

[Worthy7]

I assume you actually don't care about the CONNECTION timeout, but instead you want to adjust the COMMAND timeout.

[Kevin Gorski]

I would recommend using the connection string version as if you try to access the ObjectContext in this constructor sometimes the PowerShell/NuGet console commands will fail in a circular way.

[Clay Lenhart]

Connection Timeout and CommandTimeout and two separate things. The connection string setting, Connection Timeout, won't affect the amount of time the command runs (CommandTimeout).

[user692942]

Might be because this is an answer is a year old but Connection Timeout raises an EntityException "The underlying provider failed on Open". The actual valid value is Connect Timeout but it appears to be ignored by EF anyway.

[Karsten]

My problem was a litte different. I got timeout during migrations. EF has a similar property to set for using during migrations: msdn.microsoft.com/en-us/library/…

[Jim Aho]

Depending on what version of EF you use, see this answer to get a feeling about the different API's in how to specify the CommandTimeout property.

[Jezbers]

Does not work for me (Connection vs Command not being teh same thing I suspect). This post solved it though stackoverflow.com/questions/6232633/entity-framework-timeout‌​s

[Nebu]

My Connection Timeout was set OK but discovered CommandTimeout was null. I set CommandTimeout as per @leniel-macaferi 's suggestion and it solved my timeout issue. -So they are not the same thing and both need to be set to enable a custom timeout to be set in EF.

[cdonner]

@Karsten - thanks for the tip about dbmigrationsconfiguration! I would have tinkered with the context command timeout until the cows come home.

[GoldBishop]

Very useful for us that use Fluent API form of EF.

[shas]

Is there way we can specify timeout in Template using some config file.?

[Christian Gollhardt]

not sure, if there something build in (I wasn't able to find something). But instead of hardcoding 180, you can use System.Configuration.ConfigurationManager.AppSettings["keyna‌​me"] @shas

[Thomas Hahn]

The connection string solution leads to "Keyword not supported: 'connection timeout'."

[Wanderson López]

and how do i call this extension method?

[Andrew]

Yes these are 2 different things command timeout and connection timeout. command timeout is what you want not connection timeout

[Gert Arnold]

As noted earlier: connection timeout != command timeout. Command timeout is what matters here.

[Wael Galal El Deen]

This approach was to read it dynamically from the Connection String rather than making a separate setting for it.

[Gert Arnold]

That detail escaped my eye, and that immediately shows the problem with such hacks. It's totally unexpected and should never be done.

[Wael Galal El Deen]

The hacking checks should be done from other places and I thing the command time out of the command should be long enough to execute long queries or SQL commands. So the admin of the website should be able to control this period according to his needs without getting a new publish for just changing the command time out period.

[Gert Arnold]

NOT by abusing the Connection Timeout setting in the connection string! The next application manager will set it to some other value, unaware of its implications. And, remember, it also sets the connection timeout (obviously). You don't want people to wait for 5 minutes if the connection happens to be unavailable due to some contingency.