entity framework set arithabort on for each query

Question

I have an entity framework query that, when converted to SQL returns within a second, but when ran through entity framework times out after an hour (!) I tracked it down to the fact that, before executing the actual query, entity framework executes:

set arithabort off

I'm looking for either a way to configure EF not to do this, or for a way to override it.

I have 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 executes just once in the beginning, and gets overridden whenever another query is executed.

Maybe with an interceptor? ==> http://dba.stackexchange.com/questions/2500/make-sqlclient-default-to-arithabort-on — sdabet, Jul 15 '15 at 13:46

score 8 · Accepted Answer · answered Jul 15 '15 at 13:56

Thanks to @fiddler, adding an interceptor worked. Feels a bit hackish, 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)
    {
    }
}

This is going to do some performance degradation within itself, having `ARITHABORT` set for each and every query. At least use some local variable to store its state and execute the statement only when required. — Rohit Vipin Mathews, Jul 15 '15 at 14:05
Might be worth noting, you shouldnt do this in the instance constructor --- do this in the static constructor -- this only needs to be added once. (DbInterception.Add(new Interceptor());) — TravisWhidden, Jun 23 '17 at 01:15
Do not add the interceptor on the context, just got a problem because of this, I got the set arithabort part added more and more, the more I query de DB. You need to register your interceptor into a DbConfiguration implementation, inside the ctor, so you don't have this problem — Yushox, Oct 28 '21 at 09:33

score 1 · Answer 2 · answered Oct 11 '19 at 17:49

Building on Solomon Rutzy answer, using the StateChange event 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's DbCommand instead of SqlCommand, and DbConnection instead of SqlConnection.

A SQL Profiler trace confirms, SET ARITHABORT ON is sent when the connection opens, before any other commands are executed in the transaction.

I don't think an event handler would ever give you any warranty on executing before subsequent queries, does it? I'm going to use it thou... — Shockwaver, Jun 04 '20 at 08:24
@Shockwaver, If the connection is still open on subsequent commands, SET ARITHABORT remains ON. If the connection is closed and then reopened, the event will fire again, before any other command is executed. I hope I understood your question. — CapNCook, Jun 05 '20 at 14:17
I meant something different. If you open the connection and then send a query I think you have no warranty the event handler (with the ARITHABORT query in it) will be executed BEFORE the other query. It dependes on how the handler is called, I didn't dig through EF code thou, I could be wrong — Shockwaver, Jun 10 '20 at 10:20

entity framework set arithabort on for each query

2 Answers2