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:
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
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
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:
ReplyDelete{"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'."}...
Using ToLower() helps with languages using "nice" alphabets, but will fail with the rest of the world.
ReplyDelete@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/
ReplyDeleteCan i query the combination of tolower with substringof such as:
ReplyDeletehttp://localhost/MyService/api/odata/Locations()?$filter=substring(tolower(State), 'ft. myers')