Stripping characters from PredicateBuilder to improve search accuracy

Question

I have a method that I pass search terms into to build a custom Linq query from a database. This works really well but for one minor issue, when I search phone numbers it has to be exactly as per the database value i.e.

Typical phone number formatting stored in the database: 1234 567 8910 (1234) 567 8910 1234-567-8910

I would like to search for 12345678910 and get the same result as searching for 1234 567 8910. I have tried to resolve this as below but to no avail:

public Expression<Func<Customer, bool>> ContainsInDescription(
                                                        params string[] keywords)
        {
            var predicate = PredicateBuilder.False<Customer>();
            foreach (string keyword in keywords)
            {
                string temp = keyword;

                predicate = predicate.Or(p => p.Name.Contains(temp));
                predicate = predicate.Or(p => p.Company.Contains(temp));

                // This works but must same be as stored in database
                predicate = predicate.Or(p => p.PhoneHome.Contains(temp));
                predicate = predicate.Or(p => p.PhoneMobile.Contains(temp));
                predicate = predicate.Or(p => p.PhoneWork.Contains(temp));

                // My attempt to strip out the chacters from the search
                predicate = predicate.Or(p => p.PhoneHome.ToString().Replace(" ", "").Replace("-", "").Contains(temp.ToString().Replace(" ", "").Replace("-", "")));
                predicate = predicate.Or(p => p.PhoneMobile.ToString().Replace(" ", "").Replace("-", "").Contains(temp.ToString().Replace(" ", "").Replace("-", "")));
                //predicate = predicate.Or(p => p.PhoneWork.ToString().Replace(" ", "").Replace("-", "").Contains(temp.ToString().Replace(" ", "").Replace("-", "")));

                predicate = predicate.Or(p => p.Email.Contains(temp));
                predicate = predicate.Or(p => p.Address.AddressLine1.Contains(temp));
                predicate = predicate.Or(p => p.Address.AddressLine2.Contains(temp));
                predicate = predicate.Or(p => p.Address.City.Contains(temp));
                predicate = predicate.Or(p => p.Address.County.Contains(temp));
                predicate = predicate.Or(p => p.Address.PostalCode.Contains(temp));
                predicate = predicate.Or(p => p.Address.Country.Contains(temp));
            }
            return predicate;
        }

I have added this code to show how I use the method above (in case there is a better way which I am sure there is!).

var SearchVals = ContainsInDescription(keywords);

            using (DataClassesDataContext data = new DataClassesDataContext(cDbConnection.GetConnectionString()))
            {
                var query = (from customer in data.Customers.Where(SearchVals)
                             where customer.Deleted == false
                             orderby customer.Name ascending
                             select new
                             {
                                 CustomerID = customer.CustomerID.ToString(),
                                 Name = customer.Name,
                                 Company = customer.Company,
                                 Home = customer.PhoneHome,
                                 Mobile = customer.PhoneMobile,
                                 Work = customer.PhoneWork,
                                 Email = customer.Email,
                                 Address1 = customer.Address.AddressLine1,
                                 Address2 = customer.Address.AddressLine2,
                                 City = customer.Address.City,
                                 County = customer.Address.County,
                                 Postcode = customer.Address.PostalCode,
                                 Country = customer.Address.Country
                             }).Skip(totalToDisplay * page).Take(totalToDisplay);

If there's a restricted number of known formats it's better to convert the search term into these formats and try them all. `p.PhoneMobile.Replace() ... .Contains()` isn [sargable](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). — Gert Arnold, Jul 23 '19 at 07:34

score 0 · Answer 1 · answered Jul 22 '19 at 19:09

0

I think you need to replace "(" and ")" then add Trim() at the end. Compare it by using == instead of using Contains method.

predicate = predicate.Or(p => p.PhoneHome.ToString().Replace(" ", "").Replace("-", "").Replace("(","").Replace(")","").Trim() == temp.ToString().Replace(" ", "").Replace("-", "").Replace("(","").Replace(")","").Trim());

answered Jul 22 '19 at 19:09

Han Eui-Jun

142
1
8

I did try this also to no avail. The string after the "==" is correctly being manipulated but I get no results from the query. Reverting back to the original code always works but I have to compare the last few characters: predicate = predicate.Or(p => p.PhoneMobile.Contains(temp)); – Belliez Jul 23 '19 at 08:01

Stripping characters from PredicateBuilder to improve search accuracy

1 Answers1