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

Question

I have a LINQ 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?

Answer 1

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

Answer 2

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

I suggest you rebuild them:

exec sp_MSForeachTable 'UPDATE STATISTICS ?'