If user searches for "OConnors", how to get both "Niall O'Connors" & "Niall OConnors"?

Question

In SQL Server 2008 R2, I've some contacts with apostrophe in their names.

From ASP.NET MVC 3 + Entity Framework 5 application, if an user searches for "OConnors", I would like to show "O'Connors" also along with other "OConnors" if any.

var predicate = PredicateBuilder.True<Contact>();

if (!string.IsNullOrWhiteSpace(firstName.Trim()))
{
    predicate = predicate.And(p => p.FirstName.Contains(firstName) 
        || SqlFunctions.SoundCode(firstName) == SqlFunctions.SoundCode(p.FirstName));
}

if (!string.IsNullOrWhiteSpace(lastName.Trim()))
{
    predicate = predicate.And(p => p.LastName.Contains(lastName) 
        || SqlFunctions.SoundCode(lastName) == SqlFunctions.SoundCode(p.LastName));
}

contacts = context.Contacts
    .Where(predicate)
    .Where(contact=> locationIds.Contains(contact.LocationId));

The above code will select "Niall O'Connors" only if user is searching for "O'Connors".

I've tried

char[] charsToTrim = { '\'' };

predicate = predicate.And(p => p.FirstName.Trim(charsToTrim).Contains(firstName));

but got this error: "LINQ to Entities does not recognize the method 'System.String Trim(Char[])' method, and this method cannot be translated into a store expression."

(SoundCode is used for showing name variations also.)

Answer 1

You should change the Trim(char[]) to a replace method:

predicate = predicate.And(p => p.FirstName.Replace("'", "").Contains(firstName));

The Replace method can be resolved to SQL while the Trim(char[]) cannot.