Linq, expressions, NHibernate and similar comparisons

Question

I am trying to make a similar comparison based on an external parameter (passed by the search form) that determines the type of comparison ( "%string" or "string%" or "%string%" )

I thought in the following direction:

 query = query.Where( Entity.StringProperty.Like("SearchString", SelectedComparsionType) ) 

Similar to method than based on selected return type .StartsWith() or .EndsWith() or .SubString()

My knowledge of expressions seems to be far from the fact that I could not build a method that could give the correct result (server-side comparison in SQL, like the StartsWith method).

Answer 1

Easy way

Just use

 if (comparison == ComparisonType.StartsWith) query = query.Where(e => e.StringProperty.StartsWith("SearchString")); else if ... 

Hard way

If you want to do something like this, either make sure your LINQ provider can somehow learn about this new method, and how it will convert to SQL (unlikely), or prevent your method from ever reaching the LINQ provider and providing the provider understands something (hard). For example, instead of

 query.Where(e => CompMethod(e.StringProperty, "SearchString", comparsionType)) 

you can create something like

 var query = source.WhereLike(e => e.StringProperty, "SearchString", comparsionType) 

with the following code

 public enum ComparisonType { StartsWith, EndsWith, Contains } public static class QueryableExtensions { public static IQueryable<T> WhereLike<T>( this IQueryable<T> source, Expression<Func<T, string>> field, string value, SelectedComparisonType comparisonType) { ParameterExpression p = field.Parameters[0]; return source.Where( Expression.Lambda<Func<T, bool>>( Expression.Call( field.Body, comparisonType.ToString(), null, Expression.Constant(value)), p)); } } 

You can even add additional criteria this way

 var query = from e in source.WhereLike( e => e.StringProperty, "SearchString", comparsionType) where e.OtherProperty == 123 orderby e.StringProperty select e; 

Very, very difficult way

It would be (technically) possible to rewrite the expression tree before the provider sees it, so you can use the query that you had in mind in the first place, but you need

• create Where(this IQueryable<EntityType> source, Expression<Func<EntityType, bool>> predicate) to intercept Queryable.Where ,
• CompMethod expression tree, replacing your CompMethod , wherever it is, with one of the String methods,
• calling the original Queryable.Where with a rewritten expression
• and, above all, be able to follow the expansion method described above!

But this is probably too complicated for what you had in mind.

Answer 2

It looks like you should use:

 query = query.Where( Entity.StringProperty.Contains("SearchString") ) 

This should map to:

 WHERE StringProperty LIKE '%SearchString%' 

This should also work for more advanced search masks, such as "Mr? Sm% th", but I haven't had to test any search strings like me yet.

---

UPDATE: based on editing OPs

It looks like what you are asking is something like the following:

  public enum SelectedComparsionType { StartsWith, EndsWith, Contains } public static bool Like(this string searchString, string searchPattern, SelectedComparsionType searchType) { switch (searchType) { case SelectedComparsionType.StartsWith: return searchString.StartsWith(searchPattern); case SelectedComparsionType.EndsWith: return searchString.EndsWith(searchPattern); case SelectedComparsionType.Contains: default: return searchString.Contains(searchPattern); } } 

This will allow you to write code as needed, i.e.

 query = query.Where( Entity.StringProperty.Like("SearchString", SelectedComparsionType.StartsWith) ) 

However, personally, I would replace any use of SelectedComparsionType with a direct call to the required string function. I.e

 query = query.Where( Entity.StringProperty.StartsWith("SearchString") ) 

Since this will be displayed in the SQL LIKE query.

Answer 3

This is exactly what I had in mind, thanks. I had something similar already written, but it did not translate into SQL. For example, it worked if I did it directly:

 Entity.StringProperty.EndsWith("SearchString"); 

This did not work if I used the dedicated method:

 CompMethod("BaseString","SearchString",SelectedComparsionType.EndsWith) 

I think he probably has something to do with evaluating the expression, I'm just not sure what.

Answer 4

You will be able to use Regex to solve this problem.