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

Question

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!

Solution 1

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;

Solution 2

There is a way to prepare dynamic queries and conditions, and also to use functions to build parts of them. The syntax is also readable, which would do for the "simple" part of the question. It's possible through combining Linq expressions. There are several articles on how this can be done, but I think I came up with a new approach. At least I didn't find it on web.

To proceed you need a library of 3 simple functions. They use System.Linq.Expressions.ExpressionVisitor to dynamically modify expressions. The key feature is unifying parameters inside the expression, so that 2 parameters with the same name were made identical (UnifyParametersByName). The remaining part is replacing a named parameter with given expression (ReplacePar) and a helper method (NewExpr). The library is available with MIT license on github: LinqExprHelper, but you may quickly write something on your own.

First you define some methods, that may later be used in creating dynamic queries.

public class Store
{
    ...

    public static Expression<Func<Store, bool>>
        SafeSearchName(string sWhat)
    {
        return LinqExprHelper.NewExpr(
            (Store s) => s.Name != null && s.Name.ToLower().Contains(sWhat)
        );
    }

    public static Expression<Func<Store, bool>>
        SafeSearchDesc(string sWhat)
    {
        return LinqExprHelper.NewExpr(
            (Store s) => s.Description != null && s.Description.ToLower().Contains(sWhat)
        );
    }
}

Then you query in this way:

    // Define a master condition, using named parameters.
    var masterExpr = LinqExprHelper.NewExpr(
        (Store s, bool bSearchName, bool bSearchDesc)
        => (bSearchName && bSearchDesc));

    // Replace stub parameters with some real conditions.
    var combExpr = masterExpr
        .ReplacePar("bSearchName", Store.SafeSearchName("b").Body)
        .ReplacePar("bSearchDesc", Store.SafeSearchDesc("p").Body);
        // Sometimes you may skip a condition using this syntax:
        //.ReplacePar("bSearchDesc", Expression.Constant(true));

    // It's interesting to see how the final expression looks like.
    Console.WriteLine("expr: " + combExpr);

   // Execute the query using combined expression.
   db.Stores
        .Where((Expression<Func<Store, bool>>)combExpr)
        .ToList().ForEach(i => { Console.WriteLine(i.Name + ", " + i.Description); });

I didn't use this in production yet, but some simple tests are passed. I don't see any limits in combining queries this way. If we need more parameters we can append additional level of combining. The advantage of this method is that you can use inline lambda expressions, which are nice to read, together with dynamic expression creation and composition, which is very capable.

Is it "simple" after all? If you consider method syntax of Linq as simple, then this is nearly that simple. It doesn't allow you to create custom Linq functions, but gives you comparable capabilities.

Comments

[ManicBlowfish]

This is working with the extension method, but when I try it using the Invoke method in linq, I get: LINQ to Entities does not recognize the method 'Boolean Invoke(Localsip.Models.Wine)' method, and this method cannot be translated into a store expression. Any ideas about that?

[ManicBlowfish]

Oh well, good enough. Would still like to know how to include the "Predicate" Func in a linq query. Thanks for your help though.

[Bob Vale]

@ManicBlowfish you could just do return from p in source.Where(Predicate(q)) select p

[AllCowsAreBurgers]

This is so wrong. Consider using linqKit and its Ef/Core extensions