score:5

Accepted answer

As far I know you can't apply regular expression in Linq to Entities. What I recommend to do is if you have other conditions call Where method using them first, and then call AsEnumerable to work with Linq to Object which allows you use regular expressions, so you can apply the condition you need:

var query= context.YourDbSet.Where(...)
                            .AsEnumerable()
                            .Where(m => !Regex.IsMatch(m.EmployeeName, @"\d"));

Or you can also do the following:

var query= context.YourDbSet.Where(...)
                            .AsEnumerable()
                            .Where(e=>e.!EmployeeName.Any(char.IsDigit));

Update:

A third solution could be using DbSet.SqlQuery method to execute your raw SQL query:

var query= context.YourDbSet.SqlQuery("SELECT * FROM Table WHERE Name NOT LIKE '%[0-9]%'");

Translating that to your scenario would be:

                     // This column names must match with 
                     //  the property names in your entity, otherwise use *
return Json(db.TEmployees.SqlQuery("SELECT EmployeeID,EmployeeName 
                                    FROM Employees 
                                    WHERE Status=1 AND Name NOT LIKE '%[0-9]%'"), 
           JsonRequestBehavior.AllowGet);// Change the value in the first condition for the real int value that represents active employees

score:1

Try this more information link :

return Json(db.TEmployees
.Where(m => m.Status == Enums.Status.Active && !m.EmployeeName.Any(char.IsDigit))
.Select(m => new { ID = m.EmployeeID, EmployeeName = m.EmployeeName }).ToList(), 
    JsonRequestBehavior.AllowGet);

EDIT: Add ToList() in return json

score:1

The only solution using Linq-To-Entities I can think of is to define a string array and see if your string contains any of them:

string[] digits = new { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };
...Where(m => !digits.Any(m.EmployeeName.Contains(d));

I can imagine this will be a slow query though for big datasets, so I would just execute the sql through EF.

score:1

There is no general LINQ to Entities solution.

However, if you target only Sql Server database, you can use SqlFunctions.PatIndex canonical function like this:

db.TEmployees
    .Where(m => m.Status == Enums.Status.Active &&
        SqlFunctions.PatIndex("%[0-9]%", m.EmployeeName) == 0)
//...

score:2

You can use Regex.IsMatch.

yourEnumerable.Where(m => !Regex.IsMatch(m.EmployeeName, @"\d"));

score:2

EF is limited in its capability to generate the exact SQL you want. I do not know of a specific expression that will generate the pattern [0-9] in your LIKE clause.

The list of String Functions that are supported by EF are documented on MSDN. None of which can be used to determine if a string contains an arbitrary digit or not.

Some other options are:

  • Use the exact SQL you want in C# and call ExecuteStoreCommand
  • Return more objects than you need from the DB and filter in memory using AsEnumerable()

The Equivalent SQL would be something like

SELECT *
FROM TEmployees
WHERE Status = {code for active status}
  AND Name NOT LIKE '%[0-9]%'

Related Query

More Query from same tag