How do I get wildcard text searches (like SQL's "like" statement) in ASP.net MVC using the edo entity framework?
I assumed this would work:
var elig = (from e in _documentDataModel.Protocol_Eligibility_View
where e.criteria.Contains(query)
select e);
But it returns no results even when searching for a query string that's definitely in the database. What am I doing wrong?
This guy made a very nice "WhereLike" extension for Linq that accepts any wildcard character and compares two values (one of which comes from an expression) with a generic method derived from the location of the wildcard.
http://trentacular.com/2010/08/linq-to-entities-wild-card-like-extension-method/
EDIT: The article seems to be down. I will paste the extention code below:
public static class LinqHelper
{
//Support IQueryable (Linq to Entities)
public static IQueryable<TSource> WhereLike<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, string>> valueSelector, string value, char wildcard)
{
return source.Where(BuildLikeExpression(valueSelector, value, wildcard));
}
//Support IEnumerable (Linq to objects)
public static IEnumerable<TSource> WhereLike<TSource>(this IEnumerable<TSource> sequence, Func<TSource, string> expression, string value, char wildcard)
{
var regEx = WildcardToRegex(value, wildcard);
//Prevent multiple enumeration:
var arraySequence = sequence as TSource[] ?? sequence.ToArray();
try
{
return arraySequence.Where(item => Regex.IsMatch(expression(item), regEx));
}
catch (ArgumentNullException)
{
return arraySequence;
}
}
//Used for the IEnumerable support
private static string WildcardToRegex(string value, char wildcard)
{
return "(?i:^" + Regex.Escape(value).Replace("\\" + wildcard, "." + wildcard) + "$)";
}
//Used for the IQueryable support
private static Expression<Func<TElement, bool>> BuildLikeExpression<TElement>(Expression<Func<TElement, string>> valueSelector, string value, char wildcard)
{
if (valueSelector == null) throw new ArgumentNullException("valueSelector");
var method = GetLikeMethod(value, wildcard);
value = value.Trim(wildcard);
var body = Expression.Call(valueSelector.Body, method, Expression.Constant(value));
var parameter = valueSelector.Parameters.Single();
return Expression.Lambda<Func<TElement, bool>>(body, parameter);
}
private static MethodInfo GetLikeMethod(string value, char wildcard)
{
var methodName = "Equals";
var textLength = value.Length;
value = value.TrimEnd(wildcard);
if (textLength > value.Length)
{
methodName = "StartsWith";
textLength = value.Length;
}
value = value.TrimStart(wildcard);
if (textLength > value.Length)
{
methodName = (methodName == "StartsWith") ? "Contains" : "EndsWith";
}
var stringType = typeof(string);
return stringType.GetMethod(methodName, new[] { stringType });
}
}
}
String.Contains should work appropriately. SQL's LIKE statement is typically handled via String.StartsWith, String.Contains, or String.EndsWith.
However, it is possible you're having casing issues. You could try:
var elig = (from e in _documentDataModel.Protocol_Eligibility_View
where e.criteria.ToLower().Contains(query.ToLower())
select e);
Linq to entities does not support the SqlMethods method, but you could use the string functions instead:
.Where(entity => entity.Name.Contains("xyz"))
.Where(entity => entity.Name.EndsWith("xyz"))
.Where(entity => entity.Name.StartsWith("xyz"))
The System.Data.Linq.SqlClient namespace contains the SqlMethods class. You can use it's Like method like this:
var elig = from e in _documentDataModel.Protocol_Eligibility_View
where SqlMethods.Like(e.criteria, query)
select e;
I started using the code that Jon Koeter posted from the blog post in another answer that no longer appears to exist.
However, I found it didn't really work properly, especially when using an IEnumerable. Namely, it was resolving the enumerable using ToArray and using a regex to match, rather than the built in functions.
Since I only want to resolve my IEnumerable once I have finished filtering, I made some changes to convert to an IQueryable then use the rest of the code to find the correct Entity Framework method and call that. This way, the query itself is not called against the database until later, and it avoids the use of a regex.
public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, Expression<Func<T, string>> valueSelector, string value, char wildcard)
{
return source.Where(BuildLikeExpression(valueSelector, value, wildcard));
}
public static IEnumerable<T> WhereLike<T>(this IEnumerable<T> source, Expression<Func<T, string>> valueSelector, string value, char wildcard)
{
return source.AsQueryable().WhereLike(valueSelector, value, wildcard);
}
private static Expression<Func<T, bool>> BuildLikeExpression<T>(Expression<Func<T, string>> valueSelector, string value, char wildcard)
{
if (valueSelector == null) throw new ArgumentNullException("valueSelector");
var method = GetLikeMethod(value, wildcard);
value = value.Trim(wildcard);
var body = Expression.Call(valueSelector.Body, method, Expression.Constant(value));
var parameter = valueSelector.Parameters.Single();
return Expression.Lambda<Func<T, bool>>(body, parameter);
}
private static MethodInfo GetLikeMethod(string value, char wildcard)
{
var methodName = "Equals";
var textLength = value.Length;
value = value.TrimEnd(wildcard);
if (textLength > value.Length)
{
methodName = "StartsWith";
textLength = value.Length;
}
value = value.TrimStart(wildcard);
if (textLength > value.Length)
{
methodName = (methodName == "StartsWith") ? "Contains" : "EndsWith";
}
var stringType = typeof(string);
return stringType.GetMethod(methodName, new[] { stringType });
}
Usage:
// example data set
var data = new List<Person> {
new Person{FirstName="John", LastName="Smith"},
new Person{FirstName="Jane", LastName="Doe"}
};
data.WhereLike(x=>x.FirstName, "John", "%"); // returns John Smith
data.WhereLike(x=>x.FirstName, "J%", "%"); // returns John Smith and Jane Smith
For Entity Framework 6.2 you can use DBFunctions
For example:
try
{
using (var db = new YOUREntities())
{
var data = db.LenderProgram.Where(i => DbFunctions.Like(i.LenderProgramCode, "OTO%"))
.ToList();
return data;
}
}
catch (Exception e)
{
e.HandleException();
}
