pátek 4. února 2011

LIKE in LINQ to Entities

For whatever reason there is (as far as Google searches suggest) no way to use LIKE in LINQ to Entities.
You can use LIKE in EntitySQL though so ... would you like a like in your LINQ?

No big deal actually.

Add

        <Function Name="String_Like" ReturnType="Edm.Boolean" ef4ex:RenameTo="Like">
          <Parameter Name="searchingIn" Type="Edm.String" />
          <Parameter Name="lookingFor" Type="Edm.String" />
          <DefiningExpression>
            searchingIn LIKE lookingFor
          </DefiningExpression>
          <ef4ex:CodeBlock>
            throw new Exception("Not implemented");
          </ef4ex:CodeBlock>
        </Function>

somewhere into the

<edmx:Runtime><edmx:ConceptualModels><Schema Namespace="Your.Namespace"
...>
and then
[System.Data.Objects.DataClasses.EdmFunction"Your.Namespace""String_Like")]public static Boolean Like(this String searchingIn, String lookingFor) {
 throw new Exception("Not implemented");
}
into a static class (the name doesn't matter) in one the namespaces you tend to be "using" et voila ...

var results = db.EntitySet.Where(e => e.FooBarBaz.Like("%foo%bar%")); 

Big deal, right? (Ignore or remove the ef4ex: attributes and tags, they are used by our customized template so that the C# code above gets generated automatically. If you decide to keep them add xmlns:ef4ex="http://jenda.krynicky.cz/schemas/EF4ex" attribute into the root tag of the .edmx file.)

So what's the catch? There are two ... related. 

First if you use two .edmx files in your project you either have to make sure you are only "using" one of the namespaces in each file (so that the compiler knows which extension method Like() do you mean) or have to rename one of the methods (in the C# code) so that you can access both, but then you have to make sure you always use the right one in each query.

The second is that you can't use this solution somewhere deep within a library that's to be reused with different projects. The Like() is tied fast to the .edmx. If I find a way to overcome this restriction, I'll update this post!