如何在Linq To SQL中为连接设置ARITHABORT ON [英] How to SET ARITHABORT ON for connections in Linq To SQL

问题描述

默认情况下，对于OLEDB连接，SQL连接选项ARITHABORT为OFF，我假设Linq To SQL正在使用.但是我需要将其打开.原因是我的数据库包含一些索引视图，并且如果连接没有ARITHABORT ON，则对属于索引视图的表的任何插入/更新/删除操作都将失败.如果使用WITH(NOEXPAND)提示，则即使针对索引视图本身进行选择也会失败(必须在SQL Standard Edition中使用该提示才能获得索引视图的性能优势).

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??

我已经解决了一个笨拙的解决方法，但是我不喜欢它.我必须为每个选择/插入/更新/删除操作创建一个存储过程，然后在此过程中首先运行SET ARITHABORT ON，然后执行另一个包含实际选择/插入/更新/删除的过程.换句话说，第一个过程只是第二个过程的包装器.仅将SET ARITHABORT ON置于选择/插入/更新/删除代码上方是行不通的.

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.

推荐答案

我最终要做的是在自己的"helper"类中编写自己的方法来创建数据上下文，并在每次需要数据上下文时都使用此方法，例如

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)

这里的想法是使用将连接作为参数的datacontext构造函数.我创建并打开一个SqlConnection，在其上运行"set arithabort ..."，然后将其传递给DC(贷记到海报

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).

这篇关于如何在Linq To SQL中为连接设置ARITHABORT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！