LINQ to SQL using contains (var) clause generate ESCAPE N'~' which slows performance significantly

[ESCAPE in LINQ to SQL slows significantly]

I have added a "contains (searchVar)" clause to my LINQ query. The resulting LINQ to SQL translated query contained ESCAPE N'~'., which resulted in a significant performance drop. When removing ESCAPE N'~' from the SQL query, data returns within 2 sec, while with the ESCAPE N'~' , data returns after more than 30 secs, and in some cases simply times out. I have run the execution plan on the query, and found out that the ESCAPE N'~' causes the execution to run in a single threat, while without the ESCAPE N'~', it execute using parallel processing.

I have two questions:

1. While does the ESCAPE N'~' being added to the SQL query?

2. How do I get rid of it?

In the interest of saving time, I have already tried all of the following with no success, simply because of how the query is built in my code. So please avoid the the solutions in your answer.

1. turning the var into a constant

2. using SqlMethods.Like

3. using SqlQuery('...")

Your help is greatly appreciated.

Continue reading...