LINQ在查询中插入'ESCAPE N'〜' [英] LINQ inserts 'ESCAPE N'~' in query

问题描述

当我检查linq发出的sql查询时，我注意到在执行LIKE命令时它会放置一个ESCAPE N'〜'。如何摆脱这个问题？似乎查询需要两倍的时间，ESCAPE在sql中。

这是LINQ

  var SearchPhrase =xyz; 
 
 var result =（从我在db.myTabl 
其中i.col1.contains（SearchPhrase）
选择I）.toList（）; 
  

当我查看实际的sql

它看起来像这样：

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

解决方案

显然，

  var SearchPhrase =xyz; 
 var result =（从我在db.myTabl 
其中i.col1.contains（SearchPhrase）
选择I）.toList（）; 
  

将在底层查询中添加ESCAPE N'〜'。

但是使用如下所示的常量过滤器不会在底层查询中产生转义字符

  var result =（从我在db.myTabl 
其中i.col1.contains（xyz）
选择I）.toList（）; 
  

这意味着，变量过滤器被转义，而常量不是。

所以，在这种情况下，我们需要一个变量作为一个常量过滤器。

使用下面的代码，不应该添加任何转义字符：

  var SearchPhrase =xyz; 
 var result =（从我在db.myTabl 
其中SqlMethods.Like（i.col1，string.Format（％{0}％，SearchPhrase））
选择I）。 toList（）; 
  

但这只适用于LINQ to SQL。

另一个选择是将变量值作为常量进行嵌入，使用以下内容完成： SO文章

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.myTabl
              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'~'

解决方案

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

这篇关于LINQ在查询中插入'ESCAPE N'〜'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！