LIKE operator in LINQ

Question

Is there a way to compare strings in a C # LINQ expression similar to the SQL LIKE statement?

Suppose I have a list of strings. In this list I want to find a string. In SQL, I could write:

 SELECT * FROM DischargePort WHERE PortName LIKE '%BALTIMORE%' 

Instead of the above, the query requires 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 LINQ syntax is not working. What didn’t I understand?

Answer 1

Usually 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 is a way to make the correct regular expressions with LINQ to SQL. (Note that this really depends on which provider you are using - in LINQ to Objects, this will be fine, and the question is whether the provider can convert the call to its own query format, such as SQL.)

EDIT: As BitKFu says, Single should be used when you expect exactly one result - when it is an error, if it is not. The SingleOrDefault , FirstOrDefault or First parameters should be used depending on what was expected.

Answer 2

Regex? no. But for this query, you can simply 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.

Answer 3

Well ... sometimes it can be inconvenient to use Contains , StartsWith or EndsWith , especially when the search defines a LIKE statment value, for example. the past 'value%' requires the developer to use the StartsWith function in the expression. So I decided to write an extension for IQueryable objects.

Using

 // 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 

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

Answer 4

As John Skeet and Mark Gravell already mentioned, you can simply accept the condition of availability. But in the case of your similar query, it is very dangerous to accept the Single () operator, because this means that you will find only 1 result. In case of more results you will get a great exception :)

Therefore, I would prefer to use FirstOrDefault () instead of Single ():

 var first = Database.DischargePorts.FirstOrDefault(p => p.PortName.Contains("BALTIMORE")); var portcode = first != null ? first.PortCode : string.Empty; 

Answer 5

In the native LINQ, you can use a combination of Contains/StartsWith/EndsWith or RegExp.

In the LINQ2SQL 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 function.

Answer 6

Simple like this

 string[] users = new string[] {"Paul","Steve","Annick","Yannick"}; var result = from u in users where u.Contains("nn") select u; 

Result → Annick, Yannick

Answer 7

You can call a single method with a predicate:

 var portCode = Database.DischargePorts .Single(p => p.PortName.Contains("BALTIMORE")) .PortCode; 

Answer 8

  .Where(e => e.Value.StartsWith("BALTIMORE")) 

It works like "LIKE" SQL ...

Answer 9

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; 

Answer 10

  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; 

Answer 11

Just add extension methods for string objects.

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

using:

 use namespase that contains this class; var sPortCode = Database.DischargePorts .Where(p => p.PortName.Contains(new string [] {"BALTIMORE"}, StringComparison.CurrentCultureIgnoreCase) ) .Single().PortCode; 

Answer 12

I found a solution to simulate a SQL LIKE statement. See the answer I wrote here. stack overflow

Answer 13

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