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
14 Answers
You can use SqlMethods.Like().
An example of the usage:
var results =
from u in users
where SqlMethods.Like(u.FirstName, "%John%")
select u;
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);
}
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;
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;
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"));
Are you talking LINQ to objects or LINQ to SQL?
For LINQ to objects you'll have to resort to regular expressions me thinks.
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));
}
}
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.
Related