ManicBlowfish ManicBlowfish - 1 month ago 10
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?

Thanks!

Answer

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) ||
        q.SafeSearch(x.Description)
        );
}

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;
Comments