-1

I am currently comparing property values of type string using the string Equals method.

Example: f.rfgmodel.Equals(model, StringComparison.OrdinalIgnoreCase)

Now the problem I am running into is that for strings like ‘M&P9 Shield’ (a Firearm Model stored in my Accessories table in the DB), that has the ‘&’ symbol, the string Equals method doesn’t work.

Sadly, I cannot ignore the ‘&’ symbol in such model values, so stripping out the ‘&’ symbol from existing DB records in the DB is not an option.

Here is what my code looks like:

[Route("filter/{pcategory}")]
        public IQueryable<AccessoryDto> GetAccessoryFilter(string pcategory = "", string model = "")
        {
            return db.Accessories.Where(f => (f.rfgacctype.Equals("holsters", StringComparison.OrdinalIgnoreCase) & f.rfgparentcategory.Equals(pcategory, StringComparison.OrdinalIgnoreCase) & f.rfgmodel.Equals(model, StringComparison.OrdinalIgnoreCase)))
            .Select(AsAccessoryDto);
        }

Example of my DB table: Accessory Table

I have searched quite a bit and the few matches don’t have a solution, much less provide enough hints to lead me in the right direction in attempting to resolve my current problem.

Example 1: C# String comparison fails for string having special character in it In this particular thread, the user @Biki left a comment to the first answer, stating that the Equals method doesn’t work. This is true.

Example 2: This one didn’t help either: Elastic Search-Search string having spaces and special characters in it using C#

Please if anyone could help with a solution of provide hints pointing me in the right direction. Thanks in advance. Much appreciated.

saturobi360
  • 13
  • 5
  • 1
    Does that `.Equals` call compile into SQL? To be sure, comparing strings containing the `&` is *not* going to be a problem. – Lasse V. Karlsen Jul 08 '17 at 19:58
  • I concur with @LasseV.Karlsen, I just ran some code because it was weird to me but that it would fail. The comparison succeeded where it needed to. – Fabulous Jul 08 '17 at 20:01
  • 1
    The '&' character has a special meaning both in HTML and in URL's so the `model` variable may have a different value than you expect. – Martin Liversage Jul 08 '17 at 20:01
  • @MartinLiversage Thanks, your suggestion lead me in the right path towards the cause of the problem and also in finding the solution. – saturobi360 Jul 10 '17 at 21:25

2 Answers2

0

Have you looked at the database collation? In my experience, most are set to

Case Insensitive, Accent Sensitive

The following screenshot shows the database properties of one that I work with. The collation is the default, Latin1_General_CI_AS. CI = Case Insensitive, AS = Accent Sensitive. So "user" and "User" will be treated the same whereas "Como" and "Cómo" will be different.

Database Collation

So if that is the case, you can do without the call the .Equals() that specifies StringComparison.OrdinalIgnoreCase because the db is doing that for you. Consider:

// Search db for items with an ampersand
var item = db.AssetMakes.Where(x => x.Name == "m & n").SingleOrDefault();
Console.WriteLine($"ID: {item?.AssetMakeID}, Name: {item?.Name}");

I used that code against my own database that has an item with that name, below is the output that I got. That may help in your case.

Screenshot of above code's output

Fabulous
  • 2,393
  • 2
  • 20
  • 27
  • Thanks @Fabulous, when i specified a value "M&P9 Shield" in the comparison statement, it gave results after the endpoint request. Example: ... f.rfgmodel.Equals("M&P9 Shield", StringComparison.OrdinalIgnoreCase))) .Select(AsAccessoryDto); When the input parameter 'model' is passed with the endpoint, the comparison fails. Example: http://localhost:56206/api/accessories/filter/pistols?caliber=9mm%20Luger%20(9x19%20Para)&model=M&P9%20Shield – saturobi360 Jul 09 '17 at 01:15
  • It appears that the url encoded value: http://localhost:56206/api/accessories/filter/pistols?caliber=9mm%20Luger%20(9x19%20Para)&model=M&P9%20Shield is the culprit, since it's at this scenario that comparison doesn't work. When i hard code the comparison value of 'M&P9 Shield', it works. – saturobi360 Jul 09 '17 at 01:45
0

Since the problem occurred from the url encoded value used for 'M&P9 Shield' when passed as a input parameter at my endpoint, i realized that the special character '&' was not being encoded properly, because the url encoded value was ending up as: http://localhost:56206/api/accessories/filter/pistols?caliber=9mm%20Luger%20(9x19%20Para)&model=M&P9%20Shield

instead of: http://localhost:56206/api/accessories/filter/pistols?caliber=9mm%20Luger%20(9x19%20Para)&model=M%26P9%20Shield

I used this resource https://www.w3schools.com/tags/ref_urlencode.asp

So the '&' which is considered a unsafe ASCII character is encoded as '%26'

Thanks everyone.

saturobi360
  • 13
  • 5