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

Question

I am in big need of help, i have been trying to do this for some time now. So I have this Query: ``` Select name from BlaBlaBla order by case when name like '9%' then 1 end, case when name like '8%' then 1 end, case when name like '7%' then 1 end, case when name like '6%' then 1 end, case when name like '5%' then 1 end, case when name like '4%' then 1 end, case when name like '3%' then 1 end, case when name like '2%' then 1 end, case when name like '1%' then 1 end, case when name like '0%' then 1 end, name ``` And I want to implement it in a new C#, Asp.Net, class, in my Solution, to the Domain Project, so it will be an OrderType Filter, for some function... for now I have this: ``` var param = Expression.Parameter(typeof(T), "item"); var paramName = Expression.Property(param, "Name"); var regexMatch = Expression.Constant("^[0-9]"); var startsWithDigit = Expression.Call(typeof(Regex), "IsMatch", null, paramName); var lambda = Expression.Lambda>(startsWithDigit, param); r…

Accepted Answer

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]%")) ...
```

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

Solution

Problem