Wildcard Search for Linq

Wildcard search for LINQ

I would use Regular Expressions, since you might not always be using Linq to SQL.

Like this example of Linq to Objects

List<string> list = new List<string>();
list.Add("This is a sentence.");
list.Add("This is another one.");
list.Add("C# is fun.");
list.Add("Linq is also fun.");

System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex("This");

var qry = list
    .Where<string>(item => regEx.IsMatch(item))
    .ToList<string>();

// Print results
foreach (var item in qry)
{
    Console.WriteLine(item);
}

C# LINQ: how to handle wildcard search?

You could use regex and replace the wild cards with regex expressions. Here is an example.

    IEnumerable<string> Search(IEnumerable<string> data, string q)
    {
        string regexSearch = q
            .Replace("*", ".+")
            .Replace("%", ".+")
            .Replace("#", "\\d")
            .Replace("@", "[a-zA-Z]")
            .Replace("?", "\\w");

        Regex regex = new Regex(regexSearch);

        return data
            .Where(s => regex.IsMatch(s));
    }

Linq to Entities Wildcard %

.Contains("aaa") is equivalent to LIKE '%aaa%'

.StartsWith("aaa") is equivalent to LIKE 'aaa%'

.EndsWith("aaa") is equivalent to LIKE '%aaa'

So you don't have to add '%' manually.

LINQ search using WildCards character like *, %,?

Here's an extension method you might use

public static class EnumerableExtensions
{
    public static IEnumerable<T> MatchesWildcard<T>(this IEnumerable<T> sequence, Func<T,string> expression, string pattern)
    {
        var regEx = WildcardToRegex(pattern);

        return sequence.Where(item => Regex.IsMatch(expression(item), regEx));
    }

    public static string WildcardToRegex(string pattern)
    {
        return "^" + Regex.Escape(pattern).
        Replace("\\*", ".*").
        Replace("\\?", ".") + "$";
    }
}

Use it as follows:

void Main()
{
    var items = new[] { new MyObj { MyProperty = "ABC123" },
                        new MyObj { MyProperty = "123ABC" },
                        new MyObj { MyProperty = "123ABC456" },
    };

    var matches = items.MatchesWildcard(item => item.MyProperty, "???ABC");
}

public class MyObj
{
    public string MyProperty {get;set;}
}

(WildcardToRegex taken from CodeProject)

Like operator or using wildcards in LINQ to Entities

You can try use this article, where author describes how to build a LIKE statement with wildcard characters in LINQ to Entities.

EDIT: Since the original link is now dead, here is the original extension class (as per Jon Koeter in the comments) and usage example.

Extension:

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

Usage Example:

string strEmailToFind = "%@yahoo.com"

IQueryable<User> myUsers = entities.Users.WhereLike(u => u.EmailAddress, strEmailToFind, '%');

or, if you expect your users to be more accustomed to Windows Explorer-styled wildcards:

string strEmailToFind = "*@yahoo.com"

IQueryable<User> myUsers = entities.Users.WhereLike(u => u.EmailAddress, strEmailToFind, '*');

WildCard Search Using Linq

In the comments to the other answers you have indicated that you want to execute the query on the server side. You can do this by converting the card number into a canonical card number which has the format that is used in the database:

String GetCanonicalCardNo(String cardNo) {
  if (cardNo.Length == 19)
    return cardNo;
  if (cardNo.Length != 16)
    throw new ArgumentException("Invalid card number.", "cardNo");
  return String.Format(
    "{0}-{1}-{2}-{3}",
    cardNo.Substring(0, 4),
    cardNo.Substring(4, 4),
    cardNo.Substring(8, 4),
    cardNo.Substring(12, 4)
  );
}

This function will convert the card number into 0001-1234-5678-9001.

You can then find a card using code like this:

var canonicalCardNo = GetCanonicalCardNo(cardNo);
var card = Cards.FirstOrDefault(card => card.CardNo == canonicalCardNo);

The predicate used to select the card only contains a string comparision which can be executed on the server side.

Wildcard search in LINQ to SQL query not executing

to do what you want .. i usually use this Utlis .. (extension method that perform a wherelike with wildcards),.... hope it heps you

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace Fwd.Repository.EF.Utils
{
    public static class LinqUtils
    {
        /// <summary>
        ///     Wheres the like.
        /// </summary>
        /// <typeparam name="TSource">The type of the source.</typeparam>
        /// <param name="source">The source.</param>
        /// <param name="valueSelector">The value selector.</param>
        /// <param name="value">The value.</param>
        /// <param name="wildcard">The wildcard.</param>
        /// <returns></returns>
        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));
        }

        /// <summary>
        ///     Builds the like expression.
        /// </summary>
        /// <typeparam name="TElement">The type of the element.</typeparam>
        /// <param name="valueSelector">The value selector.</param>
        /// <param name="value">The value.</param>
        /// <param name="wildcard">The wildcard.</param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">valueSelector</exception>
        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);
        }

        /// <summary>
        ///     Gets the like method.
        /// </summary>
        /// <param name="value">The value.</param>
        /// <param name="wildcard">The wildcard.</param>
        /// <returns></returns>
        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 });
        }
    }
}

then you can use it on your IQueryable Entities like

var query = _dbContext.Users.WhereLike(xx=>xx.name,"fred*","*")

---

Related Topics