CODEWITHSUNDEEP
c#sqllinqwildcard
PlayKid
PlayKid

Reputation: 1375

Wildcard search for LINQ

I would like to know if it is possible to do a wildcard search using LINQ.

I see LINQ has Contains, StartsWith, EndsWith, etc.

What if I want something like %Test if%it work%, how do I do it?

Regards

Upvotes: 45

Views: 74122

Answers (14)

donvnielsen
donvnielsen

Reputation: 195

I have extended Ruard van Elburg's example to support my needs, and thought I would share. It handles wildcards such as "a%" (startswith(a)), "%b" (endswith(b)), "a%b" (startswith(a) && endswith(b)), and "a%b%c" (startwith(a), indexof(a) < indexof(b), & endswith(c) ).

    public static class LinqLikeExtension
{
    /// <summary> Permits searching a string value with any number of wildcards. This was written 
    /// to handle a variety of EF wildcard queries not supported because the current version is 
    /// less tan EFv6.2, which has a .Like() method.
    /// like in EFv6.</summary>
    /// <typeparam name="T">String or an object with a string member.</typeparam>
    /// <param name="query">Original query</param>
    /// <param name="searchstring">The searchstring</param>
    /// <param name="columnName">The name of the db column, or null if not a column.</param>
    /// <returns>Query filtered by 'LIKE'.</returns>
    /// <example>return iQueryableRows.Like("a", "ReferenceNumber");</example>
    /// <example>return iQueryableRows.Like("a%", "ReferenceNumber");</example>
    /// <example>return iQueryableRows.Like("%b", "ReferenceNumber");</example>
    /// <example>return iQueryableRows.Like("a%b", "ReferenceNumber");</example>
    /// <example>return iQueryableRows.Like("a%b%c", "ReferenceNumber");</example>
    /// <remarks>Linq (C#) is case sensitive, but sql isn't. Use StringComparison ignorecase for Linq.
    /// Keep in mind that Sql however doesn't know StringComparison, so try to determine the provider.</remarks>
    /// <remarks>base author -- Ruard van Elburg from StackOverflow, modifications by dvn</remarks>
    /// <seealso cref="https://stackoverflow.com/questions/1040380/wildcard-search-for-linq"/>
    public static IQueryable<T> Like<T>(this IQueryable<T> query, string searchstring, string columnName = null)
    {
        var eParam = Expression.Parameter(typeof(T), "e");
        var isLinq = (query.Provider.GetType().IsGenericType && query.Provider.GetType().GetGenericTypeDefinition() == typeof(EnumerableQuery<>));

        MethodInfo IndexOf, StartsWith, EndsWith, Equals;
        MethodCallExpression mceCurrent, mcePrevious;

        Expression method = string.IsNullOrEmpty(columnName) ? eParam : (Expression)Expression.Property(eParam, columnName);

        var likeParts = searchstring.Split(new char[] { '%' });

        for (int i = 0; i < likeParts.Length; i++)
        {
            if (likeParts[i] == string.Empty) continue; // "%a"

            if (i == 0)
            {
                if (likeParts.Length == 1) // "a"
                {
                    Equals = isLinq
                        ? Equals = typeof(string).GetMethod("Equals", new[] { typeof(string), typeof(StringComparison) })
                        : Equals = typeof(string).GetMethod("Equals", new[] { typeof(string) });
                    mceCurrent = isLinq
                        ? Expression.Call(method, Equals, new Expression[] { Expression.Constant(likeParts[i], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) })
                        : Expression.Call(method, Equals, Expression.Constant(likeParts[i], typeof(string)));
                }
                else // "a%" or "a%b"
                {
                    StartsWith = isLinq
                        ? StartsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string), typeof(StringComparison) })
                        : StartsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
                    mceCurrent = isLinq
                        ? Expression.Call(method, StartsWith, new Expression[] { Expression.Constant(likeParts[i], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) })
                        : Expression.Call(method, StartsWith, Expression.Constant(likeParts[i], typeof(string)));
                }
                query = query.Where(Expression.Lambda<Func<T, bool>>(mceCurrent, eParam));
            }
            else if (i == likeParts.Length - 1)  // "a%b" or "%b"
            {
                EndsWith = isLinq
                    ? EndsWith = typeof(string).GetMethod("EndsWith", new[] { typeof(string), typeof(StringComparison) })
                    : EndsWith = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
                mceCurrent = isLinq
                    ? Expression.Call(method, EndsWith, new Expression[] { Expression.Constant(likeParts[i], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) })
                    : Expression.Call(method, EndsWith, Expression.Constant(likeParts[i], typeof(string)));
                query = query.Where(Expression.Lambda<Func<T, bool>>(mceCurrent, eParam));
            }
            else // "a%b%c"
            {
                IndexOf = isLinq
                    ? IndexOf = typeof(string).GetMethod("IndexOf", new[] { typeof(string), typeof(StringComparison) })
                    : IndexOf = typeof(string).GetMethod("IndexOf", new[] { typeof(string) });
                mceCurrent = isLinq
                    ? Expression.Call(method, IndexOf, new Expression[] { Expression.Constant(likeParts[i], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) })
                    : Expression.Call(method, IndexOf, Expression.Constant(likeParts[i], typeof(string)));
                mcePrevious = isLinq
                    ? Expression.Call(method, IndexOf, new Expression[] { Expression.Constant(likeParts[i - 1], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) })
                    : Expression.Call(method, IndexOf, Expression.Constant(likeParts[i - 1], typeof(string)));
                query = query.Where(Expression.Lambda<Func<T, bool>>(Expression.LessThan(mcePrevious, mceCurrent), eParam));
            }
        }

        return query;
    }
}

I understand this is really late, and I understand EFv6.2+ supports a Like() method. But maybe you are like me, in a small shop with large legacy applications that make it difficult to simply upgrade .Net and EF versions.

Upvotes: 0

browsermator
browsermator

Reputation: 1354

I use this for supporting a wildcard filter of "*" in a user's search. (order does not matter): 

 if (!string.IsNullOrEmpty(SearchString))
    {
     List<String> containValues = new List<String>();
     if (SearchString.Contains("*"))
        {

        String[] pieces = SearchString.Split("*");

        foreach (String piece in pieces)
                {
                if (piece != "")
                   {
                   containValues.Add(piece);
                   }
                 }
           }

       if (containValues.Count > 0)
          {
          foreach(String thisValue in containValues)
             {
             Items = Items.Where(s => s.Description.Contains(thisValue));
             }
           }
           else
           {
           Items = Items.Where(s => s.Description.Contains(SearchString));
           }
       }

Upvotes: 0

Dmitry Pavlov
Dmitry Pavlov

Reputation: 28290

For Entity Framework Core 2.0 there is LIKE operator (announced in August 2017):

var query = from e in _context.Employees
                    where EF.Functions.Like(e.Title, "%developer%")
                    select e;

Upvotes: 14

user4864425
user4864425

Reputation:

Looking at the question

What if I want something like %Test if%it work%, how do I do it?

then I am expecting something of 

LIKE '%Test if%it work%'

meaning that the string must contain 'Test if' and 'it work', in that order.

This will not work:

context.SomeTable.Where(s => s.Name.Contains("Test if%it work")).ToList();

And if I use:

context.SomeTable.Where(s => s.Name.Contains("Test if") && s.Name.Contains("it work")).ToList();

then I will find all records that contain both "Test if" and "it work", but not specifically in that order.

So with Contains this is not possible. But with IndexOf it is.

IndexOf will locate the searchstring AND return the position of it in the string. Making it possible to find the words in the correct order.

-- Update --

With my original answer it was not my goal to provide a generic solution, but rather an example of another approach that is not sql dependend. So it is correct that the original example only answers the literal question. But since the answer may be more useful if it is generic, I've written an IQuerable extension that allows to add a like statement to the query as easy as a where statement. The extension works for both Linq as Linq-Sql.

This will find all records with both "Test if" and "it work", in that order.

context.SomeTable.Like("test if%it work", "Name").ToList();

listOfString.Like("test if%it work").ToList();

Extension, allows any number of wildcards:

/// <summary>
/// Allow to search the string with wildcards.
/// </summary>
/// <typeparam name="T">String or an object with a string member.</typeparam>
/// <param name="q">Original query</param>
/// <param name="searchstring">The searchstring</param>
/// <param name="memberName">The name of the field or null if not a field.</param>
/// <returns>Query filtered by 'LIKE'.</returns>
public static IQueryable<T> Like<T>(this IQueryable<T> q, string searchstring, string memberName = null)
{
    // %a%b%c% --> IndexOf(a) > -1 && IndexOf(b) > IndexOf(a) && IndexOf(c) > IndexOf(b)

    var eParam = Expression.Parameter(typeof(T), "e");

    MethodInfo methodInfo;

    // Linq (C#) is case sensitive, but sql isn't. Use StringComparison ignorecase for Linq.
    // Sql however doesn't know StringComparison, so try to determine the provider.
    var isLinq = (q.Provider.GetType().IsGenericType && q.Provider.GetType().GetGenericTypeDefinition() == typeof(EnumerableQuery<>));
    if (isLinq)
        methodInfo = typeof(string).GetMethod("IndexOf", new[] { typeof(string), typeof(StringComparison) });
    else
        methodInfo = typeof(string).GetMethod("IndexOf", new[] { typeof(string) });

    Expression expr;
    if (string.IsNullOrEmpty(memberName))
        expr = eParam;
    else
        expr = Expression.Property(eParam, memberName);

    // Split the searchstring by the wildcard symbol:
    var likeParts = searchstring.Split(new char[] { '%' }, StringSplitOptions.RemoveEmptyEntries);

    for (int i = 0; i < likeParts.Length; i++)
    {
        MethodCallExpression e;
        if (isLinq)
            e = Expression.Call(expr, methodInfo, new Expression[] { Expression.Constant(likeParts[i], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) });
        else
            e = Expression.Call(expr, methodInfo, Expression.Constant(likeParts[i], typeof(string)));

        if (i == 0)
        {
            // e.IndexOf("likePart") > -1
            q = q.Where(Expression.Lambda<Func<T, bool>>(Expression.GreaterThan(e, Expression.Constant(-1, typeof(int))), eParam));
        }
        else
        {
            // e.IndexOf("likePart_previous")
            MethodCallExpression ePrevious;
            if (isLinq)
                ePrevious = Expression.Call(expr, methodInfo, new Expression[] { Expression.Constant(likeParts[i - 1], typeof(string)), Expression.Constant(StringComparison.OrdinalIgnoreCase) });
            else
                ePrevious = Expression.Call(expr, methodInfo, Expression.Constant(likeParts[i - 1], typeof(string)));

            // e.IndexOf("likePart_previous") < e.IndexOf("likePart")
            q = q.Where(Expression.Lambda<Func<T, bool>>(Expression.LessThan(ePrevious, e), eParam));
        }
    }
    return q;
}

Since it doesn't need SqlMethods I assume you can use this for any database, like MySql or Postgresql. But I do not know for sure. I did test this with Sql Server using Entity Framework 6. The above statement generates the following code in Sql Server.

SELECT [Extent1].* FROM SomeTable AS [Extent1]
WHERE ((( CAST(CHARINDEX(N'test if', [Extent1].[Name]) AS int)) - 1) > -1)
AND ((( CAST(CHARINDEX(N'test if', [Extent1].[Name]) AS int)) - 1) < 
     (( CAST(CHARINDEX(N'it work', [Extent1].[Name]) AS int)) - 1))

About performance, there seems to be some discussion about what is 'better': LIKE or CHARINDEX. And from what I've read CHARINDEX seems to be favorite.

Upvotes: 6

nurchi
nurchi

Reputation: 800

I know this is and old topic, but here is my very simple solution:

string s=Regex.Escape("pattern - escaped for sanity").Replace("%", ".*").Replace("_", ".?");
user => Regex.IsMatch(user.FullName, s, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase);

In this code, I am using common escape characters for the SQL language. If you want to use say * and ?, escaped string will contain \* and \? correspondingly, make sure to include the backslash character in the .Replace(...) statement(s). Of course, if you want to give your user the ability to RexEx search, just don't escape the pattern string.

Search Regex tutorial for other options.

I believe normally % will match at least one character, while the RegEx .* will match zero or more characters. So in reality, the % wildcard is more like .+ (greedy) rather than .* (lazy).

Hope this helps.

Upvotes: 4

spadelives
spadelives

Reputation: 1628

You can also use "contains"

var myresult = db.MyItems.Where(x=>x.MyField.Contains(mysearchstring));

Upvotes: 1

Michael Freidgeim
Michael Freidgeim

Reputation: 28435

In .Net code including LINQ to Objects, I am using implementation of IsSqlLikeMatch function from thread Using Regex to create a SQL's "like" like function..

Example of use 

bool ret = message.IsSqlLikeMatch(pattern);

More details in my post SQL's "like" patterns to compare in .Net

Upvotes: 1

Gerhard
Gerhard

Reputation: 29

var result = (from x in db.Members
              where x.IDNumber.Contains(idnumber)
              && x.InstitutionIdentifier == institution.Identifier
              select x).ToList();
return result;

Will work for both Linq to SQL and Linq in memory.

Upvotes: 2

David Basarab
David Basarab

Reputation: 73311

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

Upvotes: 42

Joe Davis
Joe Davis

Reputation: 1039

add System.Data.Linq.SqlClient to your using or imports list then try:

var results= from x in data
             where SqlMethods.Like(x.SearchField, “%something%like%this%”)
             select x;

Upvotes: 14

bytebender
bytebender

Reputation: 7491

not sure if you talk LinqToSql or just linq... but you could regular expressions like this:

.Where(dto => System.Text.RegularExpressions.Regex.IsMatch(dto.CustomerName, @"Ad"));

Upvotes: 1

fretje
fretje

Reputation: 8372

Are you talking LINQ to objects or LINQ to SQL?

For LINQ to objects you'll have to resort to regular expressions me thinks.

Upvotes: 0

Rony
Rony

Reputation: 9511

.Where( column LIKE "Pattern")

Upvotes: 3

Ryan Versaw
Ryan Versaw

Reputation: 6495

You can use SqlMethods.Like().

An example of the usage:

var results =
        from u in users
        where SqlMethods.Like(u.FirstName, "%John%")
        select u;

Upvotes: 87

Related Questions