Avatar of ESCAPE in LINQ to SQL slows significantly _ 0 Points ESCAPE in LINQ to SQL slows significantly Joined Jul 2019 ESCAPE in LINQ to SQL slows significantly's threads Show activity Alert me LINQ to SQL using contains (var) clause generate ESCAPE RRS feed

  • Question

  • [previously posted under the C# forum, and someone mention that this the more appropriate forum, so here it is]

    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('...")

    It is not that those methods don't work, but that in my case I can not apply them. And my question is, can I get rid of the ESCAPE clause in any other way?

    Your help is greatly appreciated.

    Monday, July 15, 2019 7:14 PM

All replies

  • If it is difficult to get rid of ESCAPE and your columns in database are of char or varchar type, did you try to switch to nchar or nvarchar, at least for experiments?

    Monday, July 15, 2019 7:42 PM