“like” queries in Entity Framework

Question

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?

Answer 1

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

Answer 2

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"))

Answer 3

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);

Answer 4

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.

• x% -> startswith
• %x -> endswith
• %x% -> contains

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 });
        }
    }
}

Answer 5

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;

Answer 6

2019 update

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();
}