Is there an easy way to write a custom function in LINQ for Entities?

Question

I am writing a simple search query for my Entity Framework application. I need to check if the bunch of fields is zero, and if not, call ToLower () on them and compare with 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 many such lines, so I was tempted to write a helper method to clean it:

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

This, of course, does not work, because LINQ for objects does not understand what the SafeSearch function is:

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

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

Thanks!

Answer 1

Since linq uses an expression that is not executed before the actual database call, you will need to wrap your function inside the predicate.

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

Also, by changing the SafeSearch extension method, calling it upon request, it will take care of cases when 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 can use it with extesion methods

 return source.Where(Predicate(q)); 

or using the linq expression

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

Answer 2

There is a way to prepare dynamic queries and conditions, and use functions to build them. The syntax also reads what will be done for the "simple" part of the question. This is possible thanks to a combination of 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 did not find it on the Internet.

To continue, you will need a library of 3 simple functions. They use System.Linq.Expressions.ExpressionVisitor to dynamically change expressions. A key feature is the unification of parameters within an expression, so that 2 parameters with the same name are made identical ( UnifyParametersByName ). The remainder replaces the named parameter with the specified expression ( ReplacePar ) and the helper method ( NewExpr ). The library is available under the MIT license on github: LinqExprHelper , but you can quickly write something yourself.

First you define some methods that can later be used when 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 like this:

  // 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 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 have not used this in production yet, but some simple tests are passed. I do not see any restrictions in combining queries this way. If we need more parameters, we can add an additional level of union. The advantage of this method is that you can use the built-in lambda expressions that are nice to read, along with creating dynamic expressions and composition that are very capable.

Is it still easy? If you consider the syntax of the Linq method as simple, then it is almost as simple. It does not allow you to create custom Linq functions, but it provides comparable capabilities.