C# – LINQ TO ENTITY: Removing special characters inside the “where” expression

centity-frameworklinqstring

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

Best Answer

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()

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – Should ‘using’ directives be inside or outside the namespace

There is actually a (subtle) difference between the two. Imagine you have the following code in File1.cs:

// File1.cs
using System;
namespace Outer.Inner
{
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now imagine that someone adds another file (File2.cs) to the project that looks like this:

// File2.cs
namespace Outer
{
    class Math
    {
    }
}

The compiler searches Outer before looking at those using directives outside the namespace, so it finds Outer.Math instead of System.Math. Unfortunately (or perhaps fortunately?), Outer.Math has no PI member, so File1 is now broken.

This changes if you put the using inside your namespace declaration, as follows:

// File1b.cs
namespace Outer.Inner
{
    using System;
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now the compiler searches System before searching Outer, finds System.Math, and all is well.

Some would argue that Math might be a bad name for a user-defined class, since there's already one in System; the point here is just that there is a difference, and it affects the maintainability of your code.

It's also interesting to note what happens if Foo is in namespace Outer, rather than Outer.Inner. In that case, adding Outer.Math in File2 breaks File1 regardless of where the using goes. This implies that the compiler searches the innermost enclosing namespace before it looks at any using directive.

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – Should ‘using’ directives be inside or outside the namespace

Related Topic