关于Linq to Entities中的自定义Like

其实这是个很有意思的东西，遇到的原因很简单，查询数据时需要用到 like，linq 中 Contains 会对应生成 sql 语句：Like '%xxx%'，如果 xxx 中需要用 % 再进行匹配的话，会被转义掉，查询失败，查了些资料，发现可以用自定义函数搞定，也就转过来，希望遇到这问题的朋友别再花很多时间翻资料。

原链接在这里：http://forums.asp.net/t/1654093.aspx，

以下是解决方法内容：

Re: Entity framework escaping like %

Feb 18, 2011 06:52 AM|LINK

2. works like a charm! If you do it correctly and use it in a linq context it will never throw that exception. I'll explain it a little bit further.

Drawback is that you have to manually edit the XML in your .EDMX file.

So, here's how you do it:

1) Put a definition of a linq function in your .EDMX file. It has to reside within the 

 

  <edmx:ConceptualModels>

      <Schema Namespace="...">

tag.

The function definition should look something like this:

 

<Function Name="String_Like" ReturnType="Edm.Boolean">          <Parameter Name="searchingIn" Type="Edm.String" />          <Parameter Name="lookingFor" Type="Edm.String" />          <DefiningExpression>            searchingIn LIKE lookingFor          </DefiningExpression>        </Function>

 

Now you have defined a function that will insert the like expression in the "DefiningExpression" part above into the SQL generated by EF. But you need the following step to be able to call the function.

2) Make a static class in an available namespace. Put a static method like this in it:

 

 [System.Data.Objects.DataClasses.EdmFunction("...", "String_Like")]        public static Boolean String_Like(this String searchingIn, String lookingFor)        {            throw new Exception("Not implemented");        }

Replace the "..." in the annotation with your context namespace. Now, when you call the function in a linq expression or lambda, the annotation will cause it to use the definition in the .edmx file.

3) Use the function like this:

 var test = ent.<table_name>.Where(p => p.column.String_Like("%some%text%"));

I have done this in a test project and it works really well I must say. As I said, the drawback is the manual editing of EDMX XML, but it preserves the function when doing automatic updates of the edmx file.