Web API中的OData区分大小写过滤? [英] OData Case In-Sensitive filtering in Web API?

问题描述

默认情况下，ODataController执行区分大小写的搜索.我们如何扩展此行为以执行不区分大小写的搜索.

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
    }
}

我提到了这种SO 似乎我们可以利用表达式树，但是在 哪个可扩展点?

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

有什么办法可以拦截查询并自定义EF/Web Api piplelie来实现这一目标?

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

这里是一个示例:/FD?$ filter = tolower(Pr_Name)eq tolower('TAMARA')- 这应该返回所有名称为"Tamara"的人(案例 不敏感，可能是"TAMARA"，"tamara"，"Tamara"等.希望这会 帮助，提出我的观点.

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.

ODATA Web API queries中的区分大小写与SQL排序规则无关.这是Microsoft OData框架的问题.

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

---

带有substringof运算符

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);
}

使用eq运算符

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);
}

推荐答案

编辑

您是对的，问题与您的排序规则无关.它与您使用的odata表达式有关.在更新的测试查询中，您使用的是Contains，它将转换为LIKE并在搜索值的两端带有通配符(零个或多个字符的任何字符串).但是，在odata表达式中使用的是eq，它将在EF表达式中转换为=，然后在SQL查询中再次转换为=.由于您在输出中显示的Pr_Name值并不完全等于"Tamara"，而是包含"Tamara"，因此odata查询将不会带回任何数据.

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.

您需要的是odata过滤器表达式substringof，该表达式将被EF转换为Contains，该表达式在sql中被转换为LIKE，并且在搜索值的任一侧都有通配符.

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

如注释中的@ThomasKoelle所述，这与列的排序规则有关. OData管道(您在上面定义的 )使用实体框架，该框架将传入的表达式转换为对数据库的查询.因此，OData表达式与它无关，而内置的EF查询也与此无关.

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.

如果您使用区分大小写的排序规则，则必须将所有搜索词和搜索列都设置为相同的大小写，这对性能非常不利，因为这些搜索子句不可SARGable.

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.

对于大多数DBMS而言，列的排序规则是从数据库的默认排序规则继承的，而数据库的默认排序规则是从实例默认排序规则继承的.您可以在每个级别进行覆盖，但是必须在定义实例(模式/数据库)时明确地进行覆盖.

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.

这篇关于Web API中的OData区分大小写过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！