How to check for the presence of a substring in a string by removing third-party characters?

Question

I have a SQL Server database. In the orders table, I have a column for phone number. At the moment it contains different values like "+38 (453) 454-35-45", "0506070188", "+23 (434) 434-34-43". Now I'm putting it in order, but there was one problem. I have a search in the admin panel for finding a phone. It was performed using a LINQ query, which the Entity Framework converted into an SQL query. The snippet of this LINQ query was something like this: ... && dbItem.Telephone.Contains(telephoneFilter) ...

In the telephoneFilter variable, I store part of the phone number without the country code and at the same time without any third-party characters other than numbers. How can I change my LINQ query so that in the SQL query, in the value of the dbItem.Telephone column, all characters except numbers are first removed, and then the Contains method is applied to this string?

First, I made it so that I had all items selected, and then using LINQ in C# I searched the phone number column, after applying to it: Regex.Replace(phone, @"\D", "")

But this method is not suitable, since it first selects all phones from the database. It is necessary for me that these actions were carried out in request to a DB.

Answer 1

Unfortunately, you can't do Regex.Replace in LINQ Entities. As mentioned, to normalize the data going into the DB is the best solution. Having said that, you can try EF Functions as below, but it may not work in all cases:

var results = context.Orders
    .Where(o => EF.Functions.Like(o.Telephone, $"%{telephoneFilter}%"))
    .ToList();