Linq to Entities Wild-card LIKE Extension Method

By | August 4, 2010

To build a wildcard-enabled Linq query for Entity Framework, you have several methods available to use from the System.String class that Entity Framework supports and transforms into SQL:

  • Contains(string value)
  • StartsWith(string value)
  • EndsWith(string value)

A simple query example might be:

var q = (from c in db.Customers
         where c.CompanyName.Contains(name)
         select c)
        .ToList();

The above example will always search anywhere in CompanyName for a match. But you need to give your users a little more control over the match method by allowing them to supply wild-card characters at either the start or end of the text to match. This means you are left to dynamically build your query based on the presence and location of the wild-card characters.

Well my first pass at this resulted in a chunk of code that I really never want to write again. I therefore rewrote it using Expression Trees so that it could be used in any future query. Here are the resulting extension methods you are welcome to reuse:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
 
    public static class LinqExtensions
    {
        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));
        }
 
        public 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 = "Contains";
 
            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";
                textLength = value.Length;
            }
 
            var stringType = typeof(string);
            return stringType.GetMethod(methodName, new Type[] { stringType });
        }
    }

Usage of the WhereLike extension method is as follows:

var searchTerm = "*Inc";
var q = db.Customers
        .WhereLike(c => c.CompanyName, searchTerm, '*')
        .ToList();

13 thoughts on “Linq to Entities Wild-card LIKE Extension Method

  1. Joey Bradshaw

    Nice, how do I create a NOT LIKE using this?

  2. kwaku fordjour

    Any way of providing example on the use of this extension method. Hoping that future updates to EF will incorporate this and many other features in linq-2-sql

  3. Duneel

    Hi,

    Could you please show us the usage of this? Quick response would be highly appriciated.

    Thanks!

  4. Trent Post author

    Usage of the WhereLike extension method will look something like the following:

    var q = db.Customers
    .WhereLike(c => c.CompanyName, “*Inc”, ‘*’)
    .ToList();

  5. Kbalz

    Doesn’t work for multiple wildcards: Test*1st*go*

  6. Andreas

    Great stuff! Exactly what I was looking for. Thanks for sharing it.

Leave a Reply

Your email address will not be published. Required fields are marked *