1

I've found tons of answers for how to set Arithabort on in Linq to SQL, but nothing in Linq to Entities. In Linq to SQL, you can do this:

using (var conn = new SqlConnection(connectionString)){
  cmd = conn.CreateCommand();
  cmd.Connection.Open();
  cmd.CommandText = "set arithabort on;";
  cmd.ExecuteNonQuery(); // Line 5
  using (var db = new MyDataContext(conn)) {
    ...
  }
}

but if I do exactly the same thing, just substituting EntityConnection from SqlConnection in the code snippet above, I get a runtime error on Line 5:

The query syntax is not valid. Near identifier 'arithabort', line 1, column 5.

I'm guessing it's connected to the fact that Linq2Sql is hardwired for SQL Server, whereas EF can work on other DBs. So what's the trick?

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • not sure if it's available for EF5, but : http://stackoverflow.com/questions/6344992/controlling-arithabort-in-ef4 – Raphaël Althaus May 08 '13 at 10:16
  • @RaphaëlAlthaus - I have downloaded the solution from that blog. I just cannot bring myself to believe that it is so difficult and complicated to do something so simple. – Shaul Behr May 08 '13 at 11:10
  • 1
    If you've got a query that runs slowly in entity framework, but quickly in ssms the problem may be parameter sniffing, I had this same problem and solved it after reading these two posts: http://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query/ http://stackoverflow.com/questions/7962789/entity-framework-cached-query-plan-performance-degrades-with-different-parameter – cwohlman Feb 06 '14 at 20:41

1 Answers1

2

The SqlConnection is not specific to linq-to-sql or EF, you can still use the code with EF. But you must create an EntityConnection that receives the SqlConnection.

EntityConnection.CreateCommand creates an EntityCommand, which expects Entity SQL as command text, no raw SQL commands.

An alternative is to use the context's ExecuteStoreQuery command (ObjectContext), or context.Database.ExecuteSqlCommand (DbContext).

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • How do you create an `EntityConnection` that "receives" the `SqlConnection`? I did try using `ExecuteStoreQuery`, and that ran, but my query performance is still over 10x slower than when I run it in SSMS, which makes me think that there's some kind of black magic happening behind the scenes that is still treating arithabort as off. – Shaul Behr May 08 '13 at 11:14
  • There is [this](http://msdn.microsoft.com/en-us/library/cc679371.aspx) overload, but that's probably not too convenient because of the `workspace` parameter. Ten times slower is surprising, I don't know why that happens. – Gert Arnold May 08 '13 at 11:18
  • And how do you get a `DbContext` object from an `ObjectContext`? – Shaul Behr May 08 '13 at 11:18
  • You can use [this](http://msdn.microsoft.com/en-us/library/gg696505%28v=vs.103%29.aspx) constructor. – Gert Arnold May 08 '13 at 11:20
  • 1
    You can also try `((EntityConnection)context.Connection).StoreConnection.CreateCommand()` – Gert Arnold May 08 '13 at 11:23
  • I used `((EntityConnection)context.Connection).StoreConnection.CreateCommand()` and that worked! Dunno why `context.ExecuteStoreCommand` wasn't effective - but this worked! Thanks! – Shaul Behr May 08 '13 at 11:39