If you are calling a OData service whether hosted via WCF or Web API (for us .NET folks, or some other hosting mechanism for other technologies), you will find out quickly that the queries may not be returning the expected results or even no results at all. Hopefully it didn't take you too long to jump over to SQL, run the equivalent TSQL query (that is if you own the service and back end), and realized you have a case sensitivity issue.The solution is to use either of the following supported OData functions:

string tolower(string p0)

string toupper(string p0)

What you should do to take the case sensitivity out of the equation so that you get all results back is to use either of the functions on the field name in question, similar to the query below:http://localhost/MyService/api/odata/Locations()?$filter=State eq 'FL' and tolower(City) eq 'ft. myers'Notice how the 'tolower()' function was used to allow all results to be displayed. This would ensure that results with either 'Ft. Myers' or 'FT. MYERS' or any combination thereof be returned.For a list of all of the OData functions, see Section 4.5 of the following link:OData URI Conventions

Related Postings:

3
comments:

Anonymous
said...

This seems to be not a complete story on "Dealing With Case Sensitivity in OData Queries". An OData service seems to be case-sensitive for collections, fields and identifiers as well. For instance, http://services.odata.org/V3/Northwind/Northwind.svc/shippers?$select=ShipperID will sometime give a correct result, but sometimes:{"odata.error":{"code":"","message":{"lang":"en-US","value":"Resource not found for the segment 'shippers'."}}}

http://services.odata.org/V3/Northwind/Northwind.svc/shippers?$SELECT=ShipperID will give {"odata.error":{"code":"","message":{"lang":"en-US","value":"The query parameter '$SELECT' begins with a system-reserved '$' character but is not recognized."}}} and http://services.odata.org/V3/Northwind/Northwind.svc/Shippers?$select=shipperid will give {"odata.error":{"code":"","message":{"lang":"en-US","value":"Could not find a property named 'shipperid' on type 'NorthwindModel.Shipper'."}...

@jan - yeah this is not the C# ToLower() but rather the OData function which you'd have to drill into separately to see if it has the same shortcomings. http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Post a Comment

About Me

I am a Magenic Associate Principal Consultant that is an advocate of web and Microsoft .NET technologies both professionally and personally. I enjoy the challenge and creativity behind software engineering, and hope during this process to extract some of my thoughts and ideas in order to give back to others in the community through public speaking and here on this blog.