var resultSet = from p in db.Products.Where(
GenericSearch.GetWildCardCriteria<Products>("ball*","productName"))
select p;
Given this query, the Generic type passed through is the class of the linq table that is to be queried. "ball*" is the search criteria, which would return all results starting with 'ball', using "ball*10" would give all results starting with 'ball' and ending with '10'. Alternatively, "*ball" would give all results ending with 'ball'.
Due to linq functions being relatively limited in their ability to query the database, and being much more limited that a SQL statement, the second GetWildCardCriteria<T>() method attempts to interpret a search string in terms of wildcard searches using the linq functions available, specifically, the .StartsWith(), .EndsWith() and .Contains() methods. These methods have been called through the building of the functions using the BuildExpressionFromMethod<T>() method, which is used as the search is generic, and so needs to be built dynamically so the correct type <T> and property name are used.
The BuildExpressionFromMethod<T>() can build the given .Contains(), .EndsWith(), etc. expression from the strings sent to it, and return a lambda expression to the predicate as all the functions have the same signature x.Method("comparisonText")
A direct comparison is done if there are no wildcards, and so a lambda expression for x.Field == "ball" is generated using the BuildEqualsExpression<T>() method.
Note: The PredicateBuilder extension class is also required to run this code and can be found at: http://www.albahari.com/nutshell/predicatebuilder.aspx
The code for the search is below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Linq.Expressions;
namespace LINQ.Helpers
{
public class GenericSearch
{
// use this if no predicate is added to and directly using .Where()
public static Expression<Func<T, bool>> GetWildCardCriteria<T>(string searchString, string propertyName)
{
return GetWildCardCriteria<T>(True<T>(), searchString, propertyName);
}
// use this method when predcate already exists - type is inferred from expression
public static Expression<Func<T, bool>> GetWildCardCriteria<T>(Expression<Func<T, bool>> predicate, string searchString, string propertyName)
{
string[] parts = searchString.Split('*');
if (parts.Length == 1)
{
predicate = predicate.And(BuildEqualsExpression<T>(propertyName, searchString));
}
else
{
//Check for predicate wild card
if (parts[0] == "")
{
if (parts.Length == 2)
{
//If on only 2 parts the end with
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "StartsWith", parts[1]));
}
else
{
//Else do a contains
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "Contains", parts[1]));
}
}
else if (parts.Length == 2 && parts[1] == "")
{
//handle suffix wild card
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "StartsWith", parts[0]));
}
else if (parts.Length == 2 && !string.IsNullOrEmpty(parts[1]))
{
//handle mid string wild card
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "StartsWith", parts[0]));
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "EndsWith", parts[1]));
}
//handle internal wild cards
for (int i = 2; i < parts.Length - 1; i++)
{
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "Contains", parts[i]));
}
//Check for a suffix in the more than 2 parts case
if (parts.Length > 2 && !string.IsNullOrEmpty(parts[parts.Length - 1]))
{
predicate = predicate.And(BuildExpressionFromMethod<T>(propertyName, "StartsWith", parts[parts.Length - 1]));
}
}
return predicate;
}
// propName is name of the property, ie. if T is the linq table Product, then propName could be the field "Product_Name"
// value is the value that propName is equal to, eg. the product 'ball' would be equivalent to x => x.Product_Name == "ball"
private static Expression<Func<T, bool>> BuildEqualsExpression<T>(string propName, string value)
{
try
{
ParameterExpression param = Expression.Parameter(typeof(T), "x");
MemberExpression prop = Expression.Property(param, propName);
BinaryExpression equals = Expression.Equal(prop, Expression.Constant(value));
Expression<Func<T, bool>> expr = Expression.Lambda<Func<T, bool>>(equals, param);
return expr;
}
catch (Exception)
{
return x => true;
}
}
// propName is same as above
// method is the method to call on the property, eg. if method = "Contains" and prop_Name = "Product_Name",
// and comparisonText = "ball"
// this would be equivalent to x => x.Product_Name.Contains("ball")
private static Expression<Func<T, bool>> BuildExpressionFromMethod<T>(string propName, string method, string comparisonText)
{
try
{
MethodInfo mi = typeof(String).GetMethod(method, new Type[] { typeof(String) });
ParameterExpression param = Expression.Parameter(typeof(T), "x");
MemberExpression field = Expression.PropertyOrField(param, propName);
MethodCallExpression mce = Expression.Call(field, mi, Expression.Constant(comparisonText));
Expression<Func<T, bool>> expr = Expression.Lambda<Func<T, bool>>(mce, param);
return expr;
}
catch (Exception)
{
return x => true;
}
}
private static Expression<Func<T, bool>> True<T>() { return f => true; }
}
}
