OData query $filter conditions and case-sensitivity

27
votes

Does OData specify whether filter conditions on string fields are to be evaluated case-sensitively or case-insensitively?

Example: (from the docs)

/Suppliers?$filter=Address/City eq 'Redmond'

Is this expected to be case-sensitive or not?

If I want to offer both options, how can this be expressed? There is a tolower() function that can be used like:

/Suppliers?$filter=tolower(Address/City) eq 'redmond'

or

/Suppliers?$filter=tolower(Address/City) eq tolower('Redmond')

Isn't there a more concise way to express case-insensitive matching?

4
odatacase-sensitive
I get the following error using tolower on EMailAddress1 field of Contact entity: Invalid 'where' condition. An entity member is invoking an invalid property or method. Any ideas? - Zaid Masud

4 Answers

26
votes

The "eq" operator is supposed to be case sensitive. Usage of tolower (or toupper) is the currently recommended way of doing this.

0
votes

I would expect this is depended on your database collation setting, as the odata service is just performing a query. If Vitek's answer is correct, then odata is doing some post query filtering on the result set, and that should be strange, right?

0
votes

It doesn't depend on database. Even if you perform query against database in case in-sensitive way then OData will do his own additional filtering and filter your data out.

-1
votes

It is now possible to do case insensitive compare by setting EnableCaseInsensitive = true on the ODataUriResolver. I used this with Microsoft.AspNetCore.OData 7.1.0.

     var oDataUriParser = new ODataUriParser(model, uri)
     {
        Resolver = new ODataUriResolver { EnableCaseInsensitive = true}
     };

Source: https://github.com/OData/WebApi/issues/812