entity framework set arithabort on for each query

entity-framework-6

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.

1
4
7/15/2015 1:40:27 PM

Accepted Answer

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)
    {
    }
}
6
7/15/2015 1:56:48 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow