OData Case In-Sensitive filtering in Web API?

Question

By default ODataController performs case-sensitive search. How do we extend this behaviour to perform case-insensitive search.

public class NammaODataController : ODataController
{

    [EnableQuery]
    public IQueryable<FD> GetFD(ODataQueryOptions qo)
    {
        return _ctx.FDs.AsQueryAble();
        //EF takes care to apply odata query operators internally 
        //after control passes from action
    }
}

I referred this SO seems we can leverage Expression Tree, but at which extensiblility point?

Is there any way I could intercept the query and customise EF / Web Api piplelie to achieve this?

Here's an example: /FD?$filter=tolower(Pr_Name) eq tolower('TAMARA') - this should return all people with name 'Tamara' (case insensitive, could be 'TAMARA', 'tamara', 'Tamara', etc. Hope this will help, to put forward my point.

EDIT:

Case-senitivity in ODATA Web API queries has nothing to do with SQL Collation. This has been an issue with Microsoft OData framework.

---

With substringof operator

void Main()
{
    var lower = _ctx.FD
    .Where(sv => sv.Pr_Name.Contains("tamara"))
    .Take(1)
    .ToList();
    Console.WriteLine("LOWER CASE AZURE SQL OUTPUT");
    Console.WriteLine(lower);

    Console.WriteLine("UPPER CASE AZURE SQL OUTPUT");
    var upper = _ctx.FD
    .Where(sv => sv.Pr_Name.Contains("TAMARA"))
    .Take(1)
    .ToList();
    Console.WriteLine(upper);
}

---

With eq operator

void Main()
{
    var lower = FD
        .Where(sv => sv.Pr_Name == tamara A TOPOLESKI")
        .Take(1)
        .ToList();
    Console.WriteLine("LOWER CASE AZURE SQL OUTPUT");
    Console.WriteLine(lower);

    Console.WriteLine("UPPER CASE AZURE SQL OUTPUT");
    var upper = FD
        .Where(sv => sv.Pr_Name == "TAMARA TOPOLESKI")
        .Take(1)
        .ToList();
    Console.WriteLine(upper);
}

Answer 1

Edit

You are right, the problem is not related to your collation. It has to do with the odata expression that you are using. In your updated test query you are using Contains which gets translated into a LIKE with wild cards (any string of zero or more characters) at either end of the search value. However, in your odata expression you are using eq which will get translated into a = in EF expression and then again in the SQL query. As the Pr_Name values you have shown in your output are not exactly equal to "Tamara" but contain "Tamara" the odata queries will not bring back any data.

What you need is the odata filter expression substringof which would get translated by EF to Contains which is translated in sql to LIKE with wild cards on either side of the search value.

/FD?$filter=substringof(Pr_Name,'tamara')

For more filter expressions refer to Using Filter Expressions

---

As @ThomasKoelle stated in the comments this has to do with the collation of the column(s). The OData pipeline (you have defined above) uses Entity Framework which converts the passed in expression into a query for the database. So the OData expression has nothing to do with it and neither does the built EF query.

If you have a case sensitive collation then you would have to make all your search terms and search columns the same case which is very bad for performance because those search clauses would not be SARGable.

For most DBMS's the collation of the column is inherited from the default collation of the database which is inherited from the instances default collation. You can override at each level but it must be done explicitly at the time the instances (schema / database) are defined.