Entity framework arithabort for each request
I have an entity framework query that, when converted to SQL, returns within a second, but when running through the Framework entity, it expires after an hour (!) I tracked it down to the point where the entity framework does before executing the actual query:
set arithabort off
I'm looking for either a way to configure EF to not do this, or to override it.
I've tried the following:
public partial class MyContext : DbContext
{
public MyContext () : base("name=MyContext ")
{
Context.Database.ExecuteSqlCommand("set arithabort on");
}
public DbContext Context
{
get { return this; }
}
}
But this is only executed once at the beginning and becomes overridden whenever another request is made.
source to share
Thanks @fiddler for adding an interceptor. Feels a little hacky, but it certainly worked.
public partial class IfcContext : DbContext, IIfcContext
{
public MyContext() : base("name=MyContext")
{
///used to set ArithAbort to on before each query
DbInterception.Add(new Interceptor());
}
public DbContext Context
{
get { return this; }
}
}
public class Interceptor : IDbCommandInterceptor
{
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
command.CommandText = "SET ARITHABORT ON; " + command.CommandText;
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
}
source to share
Building on Solomon Ruci's answer ( enter link description here ), for EF6:
using System.Data;
using System.Data.Common;
namespace project.Data.Models
{
abstract class ProjectDBContextBase: DbContext
{
internal ProjectDBContextBase(string nameOrConnectionString) : base(nameOrConnectionString)
{
this.Database.Connection.StateChange += new StateChangeEventHandler(OnStateChange);
}
protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
if (args.OriginalState == ConnectionState.Closed
&& args.CurrentState == ConnectionState.Open)
{
using (DbCommand _Command = ((DbConnection)sender).CreateCommand())
{
_Command.CommandType = CommandType.Text;
_Command.CommandText = "SET ARITHABORT ON;";
_Command.ExecuteNonQuery();
}
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
...
This uses System.Data.Common DbCommand instead of SqlCommand and DbConnection instead of SqlConnection.
SQL Profiler trace confirms that SET ARITHABORT ON is sent when a connection is opened, before any other commands are executed in a transaction.
source to share