How to Use SQL Wildcards in Linq to Entity Framework

How to use SQL wildcards in LINQ to Entity Framework

I'm not sure that this is possible directly with linq because you can call only basic string functions like Contains, StartsWith or EndsWith. It is possible with Entity SQL so you can combine these approaches.

var query = new ObjectQuery<Profile>(
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE '" + wildcardSearch + "'",
    context);

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

ESQL injection strikes back :)

Second version without injection vulnerability (I didn't try it but it should work):

var commandText =
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE @search";

var query = new ObjectQuery<Profile>(commandText, context);
query.Parameters.Add(new ObjectParameter("search", wildcardSearch));

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

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, '*');

Linq to Entities (EF 4.1): How to do a SQL LIKE with a wildcard in the middle ( '%term%term%')?

I believe you could use SqlFunctions.PatIndex:

dt.Table.Where(p => SqlFunctions.PatIndex(term, p.fieldname) > 0);

SqlFunctions.PatIndex behaves the same as the SQL LIKE operator. It supports all standard wildcard characters including:

• % Any string of zero or more characters.
• _ (underscore) Any single character.
• [ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
• [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

SqlFunctions.PatIndex is often available when the SqlMethods.Like is not available (including within MVC controllers)

Entity Framework: combining exact and wildcard searching conditional on search term

You should be able to use the ternary operator:

bool startsWithWildCard = searchValue.StartsWith("%");
bool endsWithWildCard = searchValue.EndsWith("%");

counterparties = tdsDb.Counterparties.Where(x => 
      endsWithWildCard
          ? (startsWithWildCard
              ?  x.CounterpartyName.Contains(searchValue)
              : (x.CounterpartyName.StartsWith(searchValue)))
          : (x.CounterpartyName == searchValue));

Did you test btw if querying by a searchValue that has an % at the beginning or end works as you expect? It might be possible that % will be escaped as a character to query for because StartsWith and Contains will prepend/append % wildcards to the generated SQL search term anyway. In that case you need to cut off the % from the searchValue before you pass it into StartsWith or Contains.

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

Use LIKE %{%}% in Entity Framework with Linq?

When I need to perform such LIKE queries with entity framework (and this happens very rarely, because such like queries usually cannot use any index and so perform full table scan and are quite slow on big tables), I use PATINDEX, like this:

var routelist = (from dbHost in db.Hosts
                         where dbHost.Host == host
                         join dbRoute in db.Route on dbHost.HostsId equals dbRoute.HostId
                         where SqlFunctions.PatIndex("%{%}%",dbRoute.alias) > 0
                         select dbRoute).ToList();

PATINDEX function in sql server is like LIKE, but returns the position of the first match.

If you want to perform LIKE query in a form of "%something%", you can use Contains("something"). If it has the form of "%something" - use StartsWith("something"). If it has the form of "something%" - use EndsWith("something").

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