How to force recompile of execution plan of a Linq to SQL query?

I have a L开发者_Go百科INQ to SQL query that's created dynamically. Funny thing is, when I run it in SQL Management Studio it's lightning fast. When I run it from L2S it becomes awefully slow after a while.

This is probably because of the query plan/execution plan. When I restart SQL Server the L2S query is also lightning fast again.

Now with T-SQL you can have WITH RECOMPILE. But how to do this with L2S?

As I found in the thread below, you can use the DataContext.GetCommand(IQueryable) to get a DbCommand for the query you wish to execute. You can add "OPTION (RECOMPILE)" to the command text, from that, open a reader, and use [DataContext.Translate<T>]1 to translate the opened reader to the entity type you wanted.

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/def80609-eaf2-4631-8d3d-ad10fc9aedfa

For example, given a DataContext dataContext:

IQueryable<string> exampleItemsQuery = dataContext.Table.Where(…).Select(…); //etc

DbCommand command = dataContext.GetCommand(exampleItemsQuery);
command.CommandText += Environment.NewLine + "OPTION (RECOMPILE)";
if (dataContext.Connection.State != ConnectionState.Open)
   dataContext.Connection.Open();

IEnumerable<string> exampleItems = dataContext.Translate<string>(command.ExecuteReader(CommandBehavior.CloseConnection));

From the behaviour you describe, your statistics are almost certainly out of date.

I suggest you rebuild them:

exec sp_MSForeachTable 'UPDATE STATISTICS ?'

Check out the CompiledQuery class. Here's a tutorial from Microsoft that goes into even more detail.

I used this EF 6 Parameter Sniffing to add at the end of SQL commands "option(recompile)" before executing. It work for me. It is very good workaround how to solve it.

;) you dont. Simlpe. Not exposed.

But dynamic queries should not need a "WITH RECOMPILE". Check the query in management studio when it is slow.... all users share execution paths.

Could it be it is not the SQL Server that is slow? But LINQ (i.e. client side handling)?

What is the query you run?