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!

8 komentářů:

  1. Is this protected against SQL injection?

    OdpovědětVymazat
  2. Yes if the provider for EntitySQL is implemented correctly and according to my tests, the one for MS SQL Server is.

    OdpovědětVymazat
  3. I get this when trying to use it:

    The specified method 'Boolean Like(System.String, System.String)' on the type 'My.Namespace' cannot be translated into a LINQ to Entities store expression.

    OdpovědětVymazat
    Odpovědi
    1. Are you sure you set it up right? Added the snippet of XML into the .edmx, added the method into a static class and specified the right namespace in the EdmFunction attribute? The same namespace as specified by the in the .edmx? What version of EF do you have?

      Vymazat
  4. Works excellent! Thanks

    OdpovědětVymazat
  5. What should I do if I am using Code First EF? I defined my EF classes in code.

    OdpovědětVymazat
    Odpovědi
    1. According to http://entityframework.codeplex.com/workitem/819 Code First doesn't support model defined functions so you can't. I never understood what's so great about Code First anyway ...

      Vymazat
  6. Great Work. Works Fine.

    OdpovědětVymazat