CODEWITHSUNDEEP
c#linqlambda
luka032
luka032

Reputation: 955

Passing whole function as lambda expression to LINQ

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()?

Upvotes: 1

Views: 2785

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205539

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 :)

Upvotes: 3

Zein Makki
Zein Makki

Reputation: 30022

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.

Upvotes: 0

Related Questions