LINQ Dynamic WHERE but Clean Special Chars first

Question

Hi Guys I have the code below but it give me an error at

.Where(p => Regex.Replace(p.Phone, rgPattern, "") == Regex.Replace(phone.Trim(), rgPattern, "")

string rgPattern = @"[\\\/:\*\?""<>|()-]";

var members = from m in Context.Members select m;

  if (!String.IsNullOrEmpty(phone))
   members = members.Where(p => Regex.Replace(p.Phone, rgPattern, "") == Regex.Replace(phone.Trim(), rgPattern, ""));

I know the LINQ commnand is not executed until I run:

members.OrderBy(orderBy).Skip(startRow).Take(maxRows).ToList();

Any idea in how to clean the value before comparing?

Answer 1

This is probably a problem with your data architecture. This is why we keep the data clean in the tables all of the time -- so you don't have to do evil things like this.

You have a couple of options here:

1. Clean up your actual data and add protections on the data layer to keep your phone numbers pure.

2. Create a view or computed column in your data which cleans up the phone number on the data layer, then map to that clean phone number and query off of that column instead.

3. Call ToList() on a subset of your rows before running your regex-based query and then use Linq to Objects to run the regex against those rows. This may be prohibitive if you can't narrow down your rows enough before using Linq to Objects.