c# - Why is Linq query setting my Arithabort options to false?

Question

I have code like this:

using (var db = new MyDataContext()) {
  db.ExecuteStoreCommand("Set Arithabort on");
  var q = AFairlyComplexQuery(db); // returns an IQueryable<>
  var result = q.ToList();  // Line 4
  return result;
}

I was finding that this query was timing out. I ran SQL Profiler and grabbed the SQL and ran it in SSMS, and it came back in 7 seconds. From past experience, I've learned that this is invariably being caused by the Arithabort option being set to 0, which is why I run that first command. But it's still timing out.

I put a breakpoint on Line 4. When I hit the breakpoint, I went to SSMS, and ran the following query:

SELECT arithabort, * FROM sys.dm_exec_sessions s
WHERE program_name LIKE 'MyProg%'

As expected, Arithabort is set to 1. Then I stepped over Line 4, and immediately went back to SSMS to run that query... and suddenly Arithabort has been set back to 0!

Why? How to fix this?

EDIT: Well, I found a workaround, and it's more or less an answer, but not very satisfying.

using (var db = new MyDataContext()) {
  db.Connection.Open(); // INSERTING THIS LINE PRESERVES ARITHABORT
  db.ExecuteStoreCommand("Set Arithabort on");
  var q = AFairlyComplexQuery(db); // returns an IQueryable<>
  var result = q.ToList();  // Line 4
  return result;
}

I inserted a line db.Connection.Open(). Now Arithabort maintains its original value. But that doesn't explain (a) why this is, and (b) why it's still running roughly 10 times longer than it takes in SSMS...

Answer 1

It seems to be a known issue that EF sets Arithabort off. And for some reason, ExecuteStoreCommand doesn't work, while the following code does work:

var cmd = ((EntityConnection)db.Connection).StoreConnection.CreateCommand();
cmd.Connection.Open();
cmd.CommandText = "set arithabort on";
cmd.ExecuteNonQuery();

See this answer to this question for more.