How to make LINQ execute a (SQL) LIKE range search

by

The problem here is that expressions containing Regex can’t be translated to SQL, so even when you’d succeed in building a correct expression, you can’t use it in LINQ to a SQL backend. However, SQL’s LIKE method also supports range wildcards like [0-9], so the trick is to make your LINQ translate to SQL containing a LIKE statement.

LINQ-to-SQL offers the possibility to use the SQL LIKE statement explicitly:

return namesList.OrderBy(r => SqlMethods.Like(r.Name, "[0-9]%")) ...

This SqlMethods class can only be used in LINQ-to-SQL though. In Entity Framework there are string functions that translate to LIKE implicitly, but none of them enable the range wildcard ([x-y]). In EF a statement like …

return namesList.OrderBy(r => r.Name.StartsWith("[0-9]")) ...

… would translate to nonsense:

[Name] LIKE '~[0-9]%' ESCAPE '~'

I.e. it vainly looks for names starting with the literal string “[0-9]”. So as long as you keep using LINQ-to-SQL SqlMethods.Like is the way to go.

In Entity Framework 6.1.3 (and lower) we have to use a slightly different way to obtain the same result …

return namesList.OrderBy(r => SqlFunctions.PatIndex("[0-9]%", c.Name) == 1) ...

… because PatIndex in SqlFunctions also supports range pattern matching.

But in Entity Framwork 6.2 we’re back on track with LINQ-to-SQL because of the new DbFunctions.Like function:

return namesList.OrderBy(r => DbFunctions.Like(r.Name, "[0-9]%")) ...

Finally, also Entity Framework core has a Like function:

return namesList.OrderBy(r => EF.Functions.Like(r.Name, "[0-9]%")) ...

More Related Contents:

  1. Is there a good LINQ way to do a cartesian product?
  2. LINQ to SQL – Left Outer Join with multiple join conditions
  3. Will using LINQ to SQL help prevent SQL injection
  4. LINQ to SQL Where Clause Optional Criteria
  5. Search SQL by date ASP.NET
  6. Sql: Incorrect syntax near ‘(‘ [closed]
  7. Returning IEnumerable vs. IQueryable
  8. What is the syntax for an inner join in LINQ to SQL?
  9. LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression
  10. linq case statement
  11. Understanding .AsEnumerable() in LINQ to SQL
  12. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  13. Equivalent of SQL ISNULL in LINQ?
  14. Distinct in Linq based on only one field of the table
  15. Wildcard search for LINQ
  16. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  17. LINQ Where Ignore Accentuation and Case
  18. Linq query with nullable sum
  19. Find all child controls of specific type using Enumerable.OfType() or LINQ
  20. Join and Include in Entity Framework
  21. ASP.NET ODBC Query with parameters
  22. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  23. Max or Default?
  24. The data source does not support server-side data paging
  25. Last and LastOrDefault not supported
  26. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  27. Select multiple fields group by and sum
  28. Database Error: There is no row at position 0
  29. Linq union usage?
  30. Linq where column == (null reference) not the same as column == null

Leave a Comment