LINQ TO ENTITY: Removing special characters inside the "where" expression

Question

I am usins LINQ TO ENTITY in order to filter a list of persons based on their phone number.

IEnumerable<Person> personsList = (from person in repoPersons.All()
                                             where person.PhoneNumber.Contains(PhoneSearch)
                                             select person).ToList(); 

repoPersons.All() contains all the persons read from the database. PhoneSearch is the search term entered by the user.

The Phone number is saved in the database as a string in a special format: +1 (555) 555-6608 (as an example.

But I want to allow the user to search for 5555556608 without having to format it properly. (I want him to be able to search for part of the phone number too like 556608 and still get the result.

I tried to create a Method called RemoveSpecialCharactersInPhoneNumber that will remove the special characters and returns it and use it like this:

IEnumerable<Person> personsList = (from person in repoPersons.All()
                                             where RemoveSpecialCharactersInPhoneNumber(person.PhoneNumber).Contains(PhoneSearch)
                                             select person).ToList();

But I get this error:

LINQ to Entities does not recognize the method 'System.String RemoveSpecialCharactersInPhoneNumber(System.String)' method, and this method cannot be translated into a person expression.

So is there a way I can check if the PhoneNumber contains PhoneSearch without the special character, using LINQ TO ENTITY?

I know I can use LINQ TO ENTITY to get all the list and then use LINQ TO OBJECT to filter it but I am trying to avoid this approach.

Any solution or hint is greatly appreciated

Answer 1

The following isn't exactly neat and tidy, but it will solve your problem (as no method is trying to be incorrectly resolved on the SQL end)

IEnumerable<Person> personsList = (from person in repoPersons.All()
         where person.Phone.Replace("+", "").Replace("(", "").Replace(")", "").Replace("-", "").Replace(" ", "").Contains(PhoneNumber)
         select person).ToList()

Answer 2

It will give error because the linq to entity will be converted into SQL query so there is no method for RemoveSpecialCharactersInPhoneNumber in sql.