shamim
Reputation: 6778
LIKE operator in LINQ
Is there any way to compare strings in a C# LINQ expression similar to SQL's LIKE operator?
Suppose I have a string list. On this list I want to search a string. In SQL, I could write:
SELECT * FROM DischargePort WHERE PortName LIKE '%BALTIMORE%'
Instead of the above, query want a linq syntax.
using System.Text.RegularExpressions;
…
var regex = new Regex(sDischargePort, RegexOptions.IgnoreCase);
var sPortCode = Database.DischargePorts
.Where(p => regex.IsMatch(p.PortName))
.Single().PortCode;
My above LINQ syntax does not work. What have I got wrong?
Upvotes: 115
Views: 341453
Answers (16)
DrakonHaSh
Reputation: 901
Great custom LIKE function by @adobrzycdon't work for me in LINQPad.
Here version which work in LINQPad (tested on LINQPad 5 and LINQPad 6)
Code
void Main()
{
var users = from au in ApplicationUsers
select au;
users.Like(u => u.UserName, "Ada Byron").Dump();
users.Like(u => u.UserName, "%yro%").Dump();
users.Like(u => u.UserName, "% Byron").Dump();
users.Like(u => u.UserName, "Ada %").Dump();
users.Like(u => u.UserName, "%yro%").Like(u => u.UserName, "Ada %").Dump();
// => SQL =>
// DECLARE @p0 NVarChar(1000) = 'Ada %'
// DECLARE @p1 NVarChar(1000) = '%yro%'
// SELECT [t0].[UserName], ...
// FROM [ApplicationUsers] AS [t0]
// WHERE ([t0].[UserName] LIKE @p0) AND ([t0].[UserName] LIKE @p1)
}
// based on LinqEx by adobrzyc (https://stackoverflow.com/a/35636138/1351740)
public static class LinqExFork
{
private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
public static Expression<Func<TSource, bool>> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
{
var param = Expression.Parameter(typeof(TSource), "t");
var memberInfo = GetMemberInfo(property);
MemberExpression member;
if (memberInfo is PropertyInfo)
member = Expression.Property(param, memberInfo.Name);
else if (memberInfo is FieldInfo)
member = Expression.Field(param, memberInfo.Name);
else
throw new InvalidOperationException("Unable to determine propery or field info from expression.");
var startWith = value.StartsWith("%");
if (startWith)
value = value.Remove(0, 1);
var endsWith = value.EndsWith("%");
if (endsWith)
value = value.Remove(value.Length - 1, 1);
var constant = Expression.Constant(value);
Expression exp;
if (endsWith && startWith)
exp = Expression.Call(member, ContainsMethod, constant);
else if (startWith)
exp = Expression.Call(member, EndsWithMethod, constant);
else if (endsWith)
exp = Expression.Call(member, StartsWithMethod, constant);
else
exp = Expression.Equal(member, constant);
return Expression.Lambda<Func<TSource, bool>>(exp, param);
}
public static IQueryable<TSource> Like<TSource, TMember>(this IQueryable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
{
return source.Where(LikeExpression(parameter, value));
}
private static MemberInfo GetMemberInfo(Expression expression)
{
var lambda = expression as LambdaExpression;
if (lambda == null)
throw new ArgumentNullException("expression");
MemberExpression memberExpr = null;
switch (lambda.Body.NodeType)
{
case ExpressionType.Convert:
memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
break;
case ExpressionType.MemberAccess:
memberExpr = lambda.Body as MemberExpression;
break;
}
if (memberExpr == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine member info from expression.");
var output = memberExpr.Member as MemberInfo;
if (output == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine member info from expression.");
return output;
}
}
Upvotes: 0
prashan kc
Reputation: 31
You can also use the EF function tested in .net5
public async Task<IEnumerable<District>> SearchDistrict(string query, int stateId)
{
return await _dbContext
.Districts
.Include(s => s.State)
.Where(s => s.StateId == stateId && EF.Functions.Like(s.Name, "$%{query}%"))
.AsNoTracking()
.ToListAsync();
}
Upvotes: 3
Tiago Santos
Reputation: 7
Like Extension Linq / SQL
LikeExtension Class
Tested in .NET 5
public static class LikeExtension {
private static string ColumnDataBase<TEntity, TKey>(IModel model, Expression<Func<TEntity, TKey>> predicate) where TEntity : class {
ITable table = model
.GetRelationalModel()
.Tables
.First(f => f
.EntityTypeMappings
.First()
.EntityType == model
.FindEntityType(predicate
.Parameters
.First()
.Type
));
string column = (predicate.Body as MemberExpression).Member.Name;
string columnDataBase = table.Columns.First(f => f.PropertyMappings.Count(f2 => f2.Property.Name == column) > 0).Name;
return columnDataBase;
}
public static IQueryable<TEntity> Like<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {
string columnDataBase = ColumnDataBase(context.Model, predicate);
return context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);
}
public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {
string columnDataBase = ColumnDataBase(context.Model, predicate);
return await context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);
}
public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {
DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return await entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);
}
public static IQueryable<TEntity> Like<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {
DbSet<TEntity> entities = query as DbSet<TEntity>;
string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
return entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);
}
}
Repository
public async Task<IEnumerable<TEntity>> LikeAsync<TKey>(Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) {
return await context.LikeAsync(predicate, text, cancellationToken);
}
public IQueryable<TEntity> Like<TKey>(Expression<Func<TEntity, TKey>> predicate, string text) {
return context.Like(predicate, text);
}
Use
IQueryable<CountryEntity> result = countryRepository
.Like(k => k.Name, "%Bra[sz]il%") /*Use Sync*/
.Where(w => w.DateRegister < DateTime.Now) /*Example*/
.Take(10); /*Example*/
Or
IEnumerable<CountryEntity> result = await countryRepository
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/
Or
IQueryable<CountryEntity> result = context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null); /*Example*/
Or
List<CountryEntity> result2 = await context.Countries
.Like(k => k.Name, "%Bra[sz]il%")
.Where(w => w.Name != null) /*Example*/
.ToListAsync(); /*Use Async*/
Or
IEnumerable<CountryEntity> result3 = await context.Countries
.Where(w => w.Name != null)
.LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/
Upvotes: 1
Steve
Reputation: 184
@adobrzyc had this great custom LIKE function - I just wanted to share the IEnumerable version of it.
public static class LinqEx
{
private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains");
private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
private static Func<TSource, bool> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
{
var param = Expression.Parameter(typeof(TSource), "t");
var propertyInfo = GetPropertyInfo(property);
var member = Expression.Property(param, propertyInfo.Name);
var startWith = value.StartsWith("%");
var endsWith = value.EndsWith("%");
if (startWith)
value = value.Remove(0, 1);
if (endsWith)
value = value.Remove(value.Length - 1, 1);
var constant = Expression.Constant(value);
Expression exp;
if (endsWith && startWith)
{
exp = Expression.Call(member, ContainsMethod, constant);
}
else if (startWith)
{
exp = Expression.Call(member, EndsWithMethod, constant);
}
else if (endsWith)
{
exp = Expression.Call(member, StartsWithMethod, constant);
}
else
{
exp = Expression.Equal(member, constant);
}
return Expression.Lambda<Func<TSource, bool>>(exp, param).Compile();
}
public static IEnumerable<TSource> Like<TSource, TMember>(this IEnumerable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
{
return source.Where(LikeExpression(parameter, value));
}
private static PropertyInfo GetPropertyInfo(Expression expression)
{
var lambda = expression as LambdaExpression;
if (lambda == null)
throw new ArgumentNullException("expression");
MemberExpression memberExpr = null;
switch (lambda.Body.NodeType)
{
case ExpressionType.Convert:
memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
break;
case ExpressionType.MemberAccess:
memberExpr = lambda.Body as MemberExpression;
break;
}
if (memberExpr == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");
var output = memberExpr.Member as PropertyInfo;
if (output == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");
return output;
}
}
Upvotes: 0
Eber Camacho
Reputation: 11
List<Categories> categoriess;
private void Buscar()
{
try
{
categoriess = Contexto.Categories.ToList();
categoriess = categoriess.Where(n => n.CategoryID >= Convert.ToInt32(txtCatID.Text) && n.CategoryID <= Convert.ToInt32(txtCatID1.Text) && (n.CategoryName.Contains(txtCatName.Text)) ).ToList();
Upvotes: 1
Eduardo Romero Marin
Reputation: 41
Ideally you should use StartWith or EndWith.
Here is an example:
DataContext dc = new DCGeneral();
List<Person> lstPerson= dc.GetTable<Person>().StartWith(c=> c.strNombre).ToList();
return lstPerson;
Upvotes: 4
Marat Batalandabad
Reputation: 970
In native LINQ you may use combination of Contains/StartsWith/EndsWith or RegExp.
In LINQ2SQL use method SqlMethods.Like()
from i in db.myTable
where SqlMethods.Like(i.field, "tra%ata")
select i
add Assembly: System.Data.Linq (in System.Data.Linq.dll) to use this feature.
Upvotes: 15
NoBrend s
Reputation: 1
Just add to string object extention methods.
public static class StringEx
{
public static bool Contains(this String str, string[] Arr, StringComparison comp)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.IndexOf(s, comp)>=0)
{ return true; }
}
}
return false;
}
public static bool Contains(this String str,string[] Arr)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.Contains(s))
{ return true; }
}
}
return false;
}
}
usage:
use namespase that contains this class;
var sPortCode = Database.DischargePorts
.Where(p => p.PortName.Contains(new string [] {"BALTIMORE"}, StringComparison.CurrentCultureIgnoreCase) )
.Single().PortCode;
Upvotes: 0
NoBrend s
Reputation: 1
public static class StringEx
{
public static bool Contains(this String str, string[] Arr, StringComparison comp)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.IndexOf(s, comp)>=0)
{ return true; }
}
}
return false;
}
public static bool Contains(this String str,string[] Arr)
{
if (Arr != null)
{
foreach (string s in Arr)
{
if (str.Contains(s))
{ return true; }
}
}
return false;
}
}
var portCode = Database.DischargePorts
.Single(p => p.PortName.Contains( new string[] {"BALTIMORE"}, StringComparison.CurrentCultureIgnoreCase) ))
.PortCode;
Upvotes: 0
adobrzyc
Reputation: 305
Well... sometimes it may be uncomfortable to use Contains, StartsWith or EndsWith especially when searching value determine LIKE statment e.g. passed 'value%' require from developer to use StartsWith function in expression. So I decided to write extension for IQueryable objects.
Usage
// numbers: 11-000-00, 00-111-00, 00-000-11
var data1 = parts.Like(p => p.Number, "%11%");
// result: 11-000-00, 00-111-00, 00-000-11
var data2 = parts.Like(p => p.Number, "11%");
// result: 11-000-00
var data3 = parts.Like(p => p.Number, "%11");
// result: 00-000-11
Code
public static class LinqEx
{
private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains");
private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
public static Expression<Func<TSource, bool>> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
{
var param = Expression.Parameter(typeof(TSource), "t");
var propertyInfo = GetPropertyInfo(property);
var member = Expression.Property(param, propertyInfo.Name);
var startWith = value.StartsWith("%");
var endsWith = value.EndsWith("%");
if (startWith)
value = value.Remove(0, 1);
if (endsWith)
value = value.Remove(value.Length - 1, 1);
var constant = Expression.Constant(value);
Expression exp;
if (endsWith && startWith)
{
exp = Expression.Call(member, ContainsMethod, constant);
}
else if (startWith)
{
exp = Expression.Call(member, EndsWithMethod, constant);
}
else if (endsWith)
{
exp = Expression.Call(member, StartsWithMethod, constant);
}
else
{
exp = Expression.Equal(member, constant);
}
return Expression.Lambda<Func<TSource, bool>>(exp, param);
}
public static IQueryable<TSource> Like<TSource, TMember>(this IQueryable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
{
return source.Where(LikeExpression(parameter, value));
}
private static PropertyInfo GetPropertyInfo(Expression expression)
{
var lambda = expression as LambdaExpression;
if (lambda == null)
throw new ArgumentNullException("expression");
MemberExpression memberExpr = null;
switch (lambda.Body.NodeType)
{
case ExpressionType.Convert:
memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
break;
case ExpressionType.MemberAccess:
memberExpr = lambda.Body as MemberExpression;
break;
}
if (memberExpr == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");
var output = memberExpr.Member as PropertyInfo;
if (output == null)
throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");
return output;
}
}
Upvotes: 16
Yannick Turbang
Reputation: 388
A simple as this
string[] users = new string[] {"Paul","Steve","Annick","Yannick"};
var result = from u in users where u.Contains("nn") select u;
Result -> Annick,Yannick
Upvotes: 4
user1930698
Reputation: 61
.Where(e => e.Value.StartsWith("BALTIMORE"))
This works like "LIKE" of SQL...
Upvotes: 2
Jon Skeet
Reputation: 1503749
Typically you use String.StartsWith/EndsWith/Contains. For example:
var portCode = Database.DischargePorts
.Where(p => p.PortName.Contains("BALTIMORE"))
.Single()
.PortCode;
I don't know if there's a way of doing proper regular expressions via LINQ to SQL though. (Note that it really does depend on which provider you're using - it would be fine in LINQ to Objects; it's a matter of whether the provider can convert the call into its native query format, e.g. SQL.)
EDIT: As BitKFu says, Single should be used when you expect exactly one result - when it's an error for that not to be the case. Options of SingleOrDefault, FirstOrDefault or First should be used depending on exactly what's expected.
Upvotes: 174
BitKFu
Reputation: 3697
As Jon Skeet and Marc Gravell already mentioned, you can simple take a contains condition. But in case of your like query, it's very dangerous to take a Single() statement, because that implies that you only find 1 result. In case of more results, you'll receive a nice exception :)
So I would prefer using FirstOrDefault() instead of Single():
var first = Database.DischargePorts.FirstOrDefault(p => p.PortName.Contains("BALTIMORE"));
var portcode = first != null ? first.PortCode : string.Empty;
Upvotes: 7
Zebi
Reputation: 8882
You can call the single method with a predicate:
var portCode = Database.DischargePorts
.Single(p => p.PortName.Contains("BALTIMORE"))
.PortCode;
Upvotes: 3
Marc Gravell
Reputation: 1064184
Regex? no. But for that query you can just use:
string filter = "BALTIMORE";
(blah) .Where(row => row.PortName.Contains(filter)) (blah)
If you really want SQL LIKE, you can use System.Data.Linq.SqlClient.SqlMethods.Like(...), which LINQ-to-SQL maps to LIKE in SQL Server.
Upvotes: 39