Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to SET ARITHABORT ON for connections in Linq To SQL

By default, the SQL connection option ARITHABORT is OFF for OLEDB connections, which I assume Linq To SQL is using. However I need it to be ON. The reason is that my DB contains some indexed views, and any insert/update/delete operations against tables that are part of an indexed view fail if the connection does not have ARITHABORT ON. Even selects against the indexed view itself fail if the WITH(NOEXPAND) hint is used (which you have to use in SQL Standard Edition to get the performance benefit of the indexed view).

Is there somewhere in the data context I can specify I want this option ON? Or somewhere in code I can do it??

I have managed a clumsy workaround, but I don't like it .... I have to create a stored procedure for every select/insert/update/delete operation, and in this proc first run SET ARITHABORT ON, then exec another proc which contains the actual select/insert/update/delete. In other words the first proc is just a wrapper for the second. It doesn't work to just put SET ARITHABORT ON above the select/insert/update/delete code.

like image 569
Laurence Avatar asked Nov 21 '25 18:11

Laurence


1 Answers

What I ended up doing was writing my own method in my own "helper" class to create the datacontext and using this every time I need a datacontext, e.g.

      Dim conn As New SqlConnection(Config.GetConnectionString("SiteSqlServer"))
      Dim command As New SqlCommand("set arithabort on;", conn)
      command.Connection.Open()
      command.ExecuteNonQuery()
      Dim dc = New SiteDataContext(conn)

The idea here is to use the datacontext constructor that takes a connection as a parameter. I create and open a SqlConnection, run "set arithabort..." on it, and pass it to the DC (credit goes to poster here).

like image 86
Laurence Avatar answered Nov 25 '25 00:11

Laurence



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!