Passing whole function as lambda expression to LINQ

Question

I've getting auctions from my database as:

var auctions = from o in db.auctions select o;

I'd like to pass this function as lambda expression in .Where clause of my linq to filter my auctions result:

    private bool wordsHasProductName(auction a, string[] words)
    {
        if (!a.product_name.Contains(' ')) // Auction product name is single word - check if 
        {
            if (words.Length > 1) return false; // array is passed, single word is searching
            else return a.product_name.Contains(words[0]); // check if product name consists passed string
        }
        else // Auction product name has more words check if passed string is partially in this product name
        {
            string[] productName = a.product_name.Split(' ');
            var list = new List<string>(words);
            foreach (string item in productName) 
            {
                if (list.Contains(item)) list.Remove(item);
            }
            return list.Count == 0;
        }
    }

I'm calling it in my code, and while debugging there's no error at this line, here's place where I call .Where() clause in linq:

string[] words = searchName.Split(' ');
auctions = auctions.Where((a) => wordsHasProductName(a, words));

But at the end at return statement I'm getting exception error:

return View(auctions.ToPagedList(pageNumber, pageSize));

Error code:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'Boolean wordsHasProductName(IEP_Projekat.Models.auction, System.String[])' method, and this method cannot be translated into a store expression.

How could I succeed sending full bool function as lambda expression in my linq .Where()?

linq to sql does not support method calls. read more information here http://stackoverflow.com/questions/9970100/calling-a-method-inside-a-linq-query — Backs, Jul 11 '16 at 05:28
The `where` clause of an EF query has to be something that EF can figure out how to convert into an SQL query (within reason). Your code involves splitting a value based on a token, creating a `List`, removing elements from it, etc. This is not something that EF would be able to represent in SQL. — JLRishe, Jul 11 '16 at 05:29
Linq to Entities tries to translate your function into sql procedure. You have to filter your data locally to be able to achieve that. — Maciej Los, Jul 11 '16 at 05:30
Possible duplicate of [LINQ to Entities does not recognize the method](http://stackoverflow.com/questions/7259567/linq-to-entities-does-not-recognize-the-method) — Maciej Los, Jul 11 '16 at 05:31
Uhm okay, if so, I could make this easier I would need to check if one bigger string[] array contains less string[] array like it's subarray (and each element does not need to be fully identically same as element in subarray, it can only contain it like part of string) ? i.e. array["Ax", "By", "Cz"] contains subarray["A", "Cz"]. — luka032, Jul 11 '16 at 05:34

score 3 · Accepted Answer · answered Jul 12 '16 at 19:15

As others already mentioned, method calls cannot be used in LINQ to Entities because they cannot be translated to SQL. So the only possible way would be if you can rewrite the method as compatible expression. And here comes the other problem - you are using string.Split method which is not supported.

So you are out of luck. Or may be not. Here is the trick. Instead of splitting the product_name and checking if it contains every word in words, you can use the following (IMO equivalent) criteria:

words.All(word => (" " + product_name + " ").Contains(" " + word + " "))

Enclosing both product_name and word with space allows you to match the whole word at the beginning, middle or at the end of the target string. And it uses only constructs that are supported by the EF.

Putting it all together. The function would be like this:

private static Expression<Func<auction, bool>> wordsHasProductName(string[] words)
{
    if (words.Length == 1)
    {
        var word = words[0]; // Avoid ArrayIndex not supported
        return a => !a.product_name.Contains(" ") && a.product_name.Contains(word);
    }
    else
    {
        return a => words.All(word => (" " + a.product_name + " ").Contains(" " + word + " "));
    }
}

and the usage:

string[] words = searchName.Split(' ');
auctions = auctions.Where(wordsHasProductName(words));

However the above implementation does not produce very good SQL query, especially for more than one word. A much better SQL is produced if rewriting it by building the predicate expression manually:

private static Expression<Func<auction, bool>> wordsHasProductName(string[] words)
{
    Expression<Func<auction, string>> product_name;
    Expression condition;
    var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    if (words.Length == 1)
    {
        // a => !a.product_name.Contains(" ") && a.product_name.Contains(words[0])
        product_name = a => a.product_name;
        condition = Expression.AndAlso(
            Expression.Not(Expression.Call(product_name.Body, containsMethod, Expression.Constant(" "))),
            Expression.Call(product_name.Body, containsMethod, Expression.Constant(words[0])));
    }
    else
    {
        // a => (" " + a.product_name + " ").Contains(" " + words[0] + " ")
        // && (" " + a.product_name + " ").Contains(" " + words[1] + " ")
        // ...
        // && (" " + a.product_name + " ").Contains(" " + words[N-1] + " ")
        product_name = a => " " + a.product_name + " ";
        condition = words
            .Select(word => Expression.Call(product_name.Body, containsMethod, Expression.Constant(" " + word + " ")))
            .Aggregate<Expression>(Expression.AndAlso);
    }
    return Expression.Lambda<Func<auction, bool>>(condition, product_name.Parameters);
}

Welcome to the world of EF and expressions :)

score 0 · Answer 2 · answered Jul 11 '16 at 05:38

You can't pass this complex C# logic as an Expression Tree that can interpreted by the EF provider and the hardest part for the provider is transforming it into SQL statements.

So, your option is to create a stored procedure where you pass in the required parameters, then you write the logic using pure SQL. Then, you map your SP to EF and call it to return the list of objects you want.

Passing whole function as lambda expression to LINQ

2 Answers2