Regex in LINQ query

Question

I have a LINQ statement like this:

var query = from s in persons
            where (string.IsNullOrEmpty(name) || s.Name.trim().contains(name)) &&
                  (string.IsNullOrEmpty(email) || s.Email.contains(email)) &&
                  (string.IsNullOrEmpty(zip) || s.Zip == zip)
            select s;

The name field is coming from the user who can type the name on the web page and then I need to search the persons table for that particular name. Unfortunately, the name First Name and Last name are in the same column in the database and the other issue is there are two spaces between first Name and Last Name so for e.g. if the name is Steve Ramsey. the name in the database is this way:

Steve  Ramsey

Not all the names are stored this way, only few names are like this. I have the Regex to eliminate one extra space:

Regex regex = new Regex("[ ]{2,}", options);
CustName = regex.Replace(CustName, " ");

I am not sure how to put this regex in LINQ query

you can use split with space : https://stackoverflow.com/questions/20056557/using-split-in-linq-query — abolfazl sadeghi, Jun 08 '23 at 00:43
Instead of Regex you can split input `name` into first and last parts, concatenate them using one and two spaces and then search for either concatenations. — Ed'ka, Jun 08 '23 at 04:22
What LINQ are you using: LINQ to Objects / SQL / EF 4.x / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x / 7.x / 8.x? What database provider? — NetMage, Jun 09 '23 at 18:14

score 0 · Accepted Answer · answered Jun 08 '23 at 02:14

Directly using Regex.Replace in LINQ-to-SQL is not possible. There are two options in my mind and I think the first one is the best one. Use SqlMethods.Like method to do what you wanted to do.

My Solution:

var query = from s in persons
            where (string.IsNullOrEmpty(name) || SqlMethods.Like(s.Name, "%" + name + "%")) &&
                  (string.IsNullOrEmpty(email) || s.Email.Contains(email)) &&
                  (string.IsNullOrEmpty(zip) || s.Zip == zip)
            select s;

This should fix your problem. Don't forget to import this class:

using System.Data.Linq.SqlClient;

Regex in LINQ query

1 Answers1