LINQ inserts 'ESCAPE N'~' in query

Question

When I examine the SQL query the Linq spits out, I noticed that it places a ESCAPE N'~' when doing a LIKE command. How do I get rid of this? It seems like the query takes twice as long with the ESCAPE is in the SQL.

Here is the LINQ

var SearchPhrase = "xyz";

var result = (from i in db.myTable
              where i.col1.contains(SearchPhrase)
              select i).toList();

When I look at the actual SQL it looks something like this:

   SELECT 
       [Extent1].Col1
   FROM myTable As [Extent1]
   WHERE [Extent1].Col1 LIKE @p__linq__3 ESCAPE N'~'

Answer 1

Apparently,

var SearchPhrase = "xyz";
var result = (from I in db.myTabl
          where i.col1.contains(SearchPhrase)
          select I).toList();

will add ESCAPE N'~' in the underlying query.

However using a constant filter like the following, doesn't produce escape characters in the underlying query

var result = (from I in db.myTabl
          where i.col1.contains("xyz")
          select I).toList();

Which means, variable filters are escaped, while constants are not.

So, in this case, we need a variable to be used as a constant filter.

Using the following, shouldn't add any escape characters:

var SearchPhrase = "xyz";
var result = (from I in db.myTabl
          where SqlMethods.Like(i.col1, string.Format("%{0}%", SearchPhrase))
          select I).toList();

but this works only with LINQ to SQL.

The other alternative is to embed the variable value as a constant, which is done using the following as explained in the SO article