How to stay dry using LINQ for entities and helper methods?

Question

Let's say that I have a specific way to decide if some lines match:

public bool stringsMatch(string searchFor, string searchIn) { if (string.IsNullOrEmpty(searchFor)) { return true; } return searchIn != null && (searchIn.Trim().ToLower().StartsWith(searchFor.Trim().ToLower()) || searchIn.Contains(" " + searchFor)); } 

I would like to pull matches from the database using Linq To Entities and this helper. However, when I try this:

 IQueryable<Blah> blahs = query.Where(b => stringsMatch(searchText, b.Name); 

I get "LINQ to Entities does not recognize the method ..."

If I rewrote the code as follows:

 IQueryable<Blah> blahs = query.Where(b => string.IsNullOrEmpty(searchText) || (b.Name != null && (b.Name.Trim().ToLower().StartsWith(searchText.Trim().ToLower()) || b.Name.Contains(" " + searchText))); 

Which is logically equivalent, then everything works fine. The problem is that the code is not readable, and I have to rewrite it for every single entity that I want to map.

As far as I can judge by issues such as this , what I want to do is not possible at the moment, but I hope that I miss something, I?

Answer 1

Using a freely available library called LINQKit (as mentioned in @Eranga) this task becomes reasonable. Using LINQKit code, which now looks like mine:

 protected Expression<Func<T, bool>> stringsMatch(string searchFor, Expression<Func<T, string>> searchIn) { if (string.IsNullOrEmpty(searchFor)) { return e => true; } return e => (searchIn.Invoke(e) != null && (searchIn.Invoke(e).Trim().ToLower().StartsWith(searchFor.Trim().ToLower()) || searchIn.Invoke(e).Contains(" " + searchFor))); } 

And you need to call it like this (pay attention to the AsExpandable () call)

 IQueryable<Blah> blahs = query().AsExpandable().Where(StringsMatch(searchText, b => b.Name)); 

The magic parts are calls to searchIn.Invoke (e) and the use of AsExpandable (), which adds a wrapper layer that allows them to work.

The AsExpandable () bit is described in detail by the original author here .

Please note that I am still a little foggy on some details of the expressions, so please add a comment / edit this answer if it can be improved / shorter / clearer.

Answer 2

If all the "blahs" (classes) that you will filter have the same structure, you can use a simple method like this. The main difference is that it returns an expression that Linq should parse, and it fills in the entire instance and filters the name instead of just entering the string name.

  public static Expression<Func<T, bool>> BuildStringMatch<T>(string searchFor) where T : IHasName { return b => string.IsNullOrEmpty(searchFor) || (b.Name != null && (b.Name.Trim().ToLower().StartsWith(searchFor.Trim().ToLower()) || b.Name.Contains(" " + searchFor))); } 

You can use this method as follows:

  IQueryable<Blah> blahs = query.Where(BuildStringMatch<Blah>(searchText)); 

This assumes that all of your classes that you want to filter implement some interface, for example:

  public interface IHasName { string Name { get; } } 

If you want to filter different properties, I don’t think you can do it with simple code. I believe that you will need to develop an expression with reflection yourself (or using a library that uses reflection) - this is still possible, but much more complicated.

Edit: it looks like you need dynamic behavior, so I borrowed some dtb logic to answer this question and came up with the following:

 public static Expression<Func<T, bool>> BuildStringMatch<T>(Expression<Func<T, string>> property, string searchFor) { var searchForExpression = Expression.Constant(searchFor, typeof(string)); return Expression.Lambda<Func<T, bool>>( Expression.OrElse( Expression.Call(typeof(string), "IsNullOrEmpty", null, searchForExpression), Expression.AndAlso( Expression.NotEqual(property.Body, Expression.Constant(null, typeof(string))), Expression.OrElse( Expression.Call(Expression.Call(Expression.Call(property.Body, "Trim", null), "ToLower", null), "StartsWith", null, Expression.Call(Expression.Call(searchForExpression, "Trim", null), "ToLower", null)), Expression.Call(property.Body, "Contains", null, Expression.Call(typeof(string), "Concat", null, Expression.Constant(" "), searchForExpression)) ) ) ), property.Parameters ); } 

You would use it like:

  IQueryable<Blah> blahs2 = query.Where(BuildStringMatch<Blah>(b => b.Name, searchText)); 

It is long and detailed, but you can see how it looks like the original method written in direct C # code. Note. I have not tested this code, so there may be a few minor problems - but this is a general idea.