How can I query to use Entity Framework with alpha digital comparison?
How can I make a query using Entity Framework and LINQ for alphanumeric comparison only? I have a database table with a field AccountNumber, which contains special characters, so a sample account number could be as follows: 803-2234502-345
. But the account number I got to search for won't have any special characters:8032234502345
string accountNumber = "8032234502345";
Provider provider = dbContext.Providers.Where(p => p.AccountNumber == accountNumber).FirstOrDefault();
I tried adding Regex.Replace
to both sides of the comparison, but I get an exception:
LINQ to Entities does not recognize the "System.String Replace (System.String, System.String)" method, and this method cannot be translated into a store expression.
Any help or suggestions are greatly appreciated.
source to share
You are using Linq for Entities, so you can only use what functions fall to SQL in mensers enities. You can either change the functions you use to display the SQL correctly (although I suspect you won't be able to do this with Regex - it should be a more manual comparison) or (as @Dabblernl mentioned in the comments) load your providers (or a subset of suppliers) to a list / or list, and then run a regex over it.
So something like:
Provider provider = dbContext.Providers.Where(p => p != null && p != "").ToList()
.Where(p => *Do regex comparison here*).FirstOrDefault();
Possible SQL mappings: http://msdn.microsoft.com/en-us/library/bb738681.aspx
source to share