CODEWITHSUNDEEP
c#entity-frameworklinq
Web Develop Wolf
Web Develop Wolf

Reputation: 6326

Removing white space from db result in LINQ query

I'm attempting to remove white space from a post code field in a database so that when I compare it to the users input I'm comparing both strings with no spaces in the post code at all so it shouldn't matter how the post code is entered.

This is my LINQ query with the replace function that doesn't appear to be working:

List<SchoolReferanceDTO> res = db.SchoolReferences.Where(x => x.SchoolReferencePostcode.Replace(" ", "").Contains(Postcode)).Select(x => new SchoolReferanceDTO()
                {
                    SchoolReferenceSchoolId = x.SchoolReferenceSchoolId,
                    SchoolReferenceEstablishmentName = x.SchoolReferenceEstablishmentName,
                    SchoolReferenceStreet = x.SchoolReferenceStreet,
                    SchoolReferenceLocality = x.SchoolReferenceLocality,
                    SchoolReferenceAddress3 = x.SchoolReferenceAddress3,
                    SchoolReferenceTown = x.SchoolReferenceTown,
                    SchoolReferenceCounty = x.SchoolReferenceCounty,
                    SchoolReferencePostcode = x.SchoolReferencePostcode,
                    SchoolReferenceEmail = x.SchoolReferenceEmail
                }).ToList();

And the string I'm comparing it to:

postcode = postcode.Replace(" ", string.Empty);

Upvotes: 1

Views: 775

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726909

One approach is to drop Replace, and use LIKE instead. Since postal codes are generally short, you could transform the target code ST14BJ to %S%T%1%4%B%J% (demo), and use LIKE operator:

var postPattern = Regex.Replace(postcode, "(?<=.|^)(?=.|$)", "%");
List<SchoolReferanceDTO> res = db.SchoolReferences
    .Where(x => SqlFunctions.PatIndex(postPattern, x.SchoolReferencePostcode) >= 0)
    .Select(...);

Upvotes: 2

Related Questions