ManicBlowfish ManicBlowfish - 1 year ago 61
C# Question

Is there a simple way to write a custom function in LINQ to Entities?

I'm writing a simple search query for my Entity Framework application. I need to check if a bunch of fields are null, and if not, call ToLower() on them and compare to the search query. The LINQ query looks something like this:

public IQueryable<Store> SearchStores(string q, IQueryable<Store> source)
q = q.ToLower();

return (
from s in source
where (
(s.Name != null && s.Name.ToLower().Contains(q)) ||
(s.Description != null && s.Description.ToLower().Contains(q)) ||

There are a lot of lines like this, so I was tempted to write a helper method to clean it up a bit:

public static bool SafeSearch(this string s, string q)
return s == null ? false : s.ToLower().Contains(q);

This of course doesn't work, though, since LINQ to entities doesn't understand what the SafeSearch function is:

LINQ to Entities does not recognize the method 'Boolean SafeSearch(System.String, System.String)' method, and this method cannot be translated into a store expression.

Is there an easy way to write a simple custom function like this?


Answer Source

Since linq uses expression that are not executed until you actually calling the database, you would need to wrap your function inside of a predicate.

private static Func<Country, bool> Predicate(string q)
    return x => (
        q.SafeSearch(x.Name) ||

Also reversing the SafeSearch extension method by calling it on query, will take care of cases where x.Name is null.

public static class SearchExt
    public static bool SafeSearch(this string q, string param)
        return param == null ? false : param.ToLower().Contains(q);

and then you could use it with extesion methods

return source.Where(Predicate(q));

or by using linq expression

return from p in source
       where Predicate(q).Invoke(p)
       select p;