Set database timeout in Entity Framework
C#.Netasp.net MvcEntity FrameworkConnection TimeoutC# Problem Overview
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 ?
C# Solutions
Solution 1 - C#
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>
Solution 2 - C#
You can use DbContext.Database.CommandTimeout = 180; // seconds
It's pretty simple and no cast required.
Solution 3 - C#
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 - C#
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 - C#
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));
}
}
Solution 6 - C#
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.
Solution 7 - C#
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;
}
}
Solution 8 - C#
You can use this simple
:dbContext.Database.SetCommandTimeout(300);
Solution 9 - C#
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)
Solution 10 - C#
You should make the changes in the Connection String tag in the web config and make the EntityFrameWork read from it in its contructor.
-
Add this term to the web.config Connection String: Connection Timeout=300;
-
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.