如果按名称搜索,如果列上有索引,是否需要使列(字符串)区分大小写以进行EF Core查询?

在我见过的大多数应用程序中,用户名通常不区分大小写,只区分密码。

如果您有一个非常大的用户表,那么查询如下

var user = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName.ToLower == userName.ToLower());

将影响性能,因为您需要将每个usename转换为lower。

因此,IMHO,当您保存新用户或更新现有用户时,将用户名转换为小写是一个好主意,但您不需要为此保留专门的列。然后你可以使用这样的代码

var user = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName == userName.ToLower());

行为将完全取决于为数据库选择的排序规则选项。默认情况下,SQL Server将使用不区分大小写(CI)排序规则,因此当您针对数据库编写查询时,无论是EF还是手动,字符串比较都将不区分大小字母。但是,当您针对区分大小写的排序规则(CS)数据库运行应用程序时,您的应用程序将无法匹配字符串。(也就是说,我认为PostgreSQL默认使用CS,或者针对_CS排序规则SQL Server数据库运行。)这在一个客户端站点上有点令人惊讶,因为开发数据库是_CI,而生产服务器使用_CS排序。

让SQL Server(或类似的数据库)处理不区分大小写的搜索是可以的,只要您确保行为被记录下来,并标记系统需要使用不区分大小字母的排序规则。就性能而言,我不知道让SQL Server进行不区分大小写的比较有任何隐含的成本。我希望您能写出以下所有疑问:

.FirstOrDefaultAsync(p => p.UserName.ToLower() == userName.ToLower());

…实际上会产生从小到大的性能成本,并可能抵消索引的使用以及数据库中查询执行的潜在其他内部构建性能度量。

否则,如果要求不区分大小写,则应用程序应确保不区分大小大小写的字段始终以大写或小写一致存储,并相应地使用相同的强制大小写进行搜索。如果外部系统/查询可能会更改数据,那么这里要担心的是,您的代码会期望数据库本身没有强制执行的一致情况。检查约束可以捕获插入/更新混合大小写的尝试,也可以使用“代替插入”触发器替换大小写混合(大写或小写)。当然,这里的选项将完全取决于您的RDBMS。

默认情况下的实体框架 does not make any collation specification 在生成的SQL中。 因此,诸如

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName == userName);

会被翻译成类似的东西

SELECT TOP 1 u.*
FROM Users u
WHERE u.UserName = @userName;

有区分大小写和不区分大小写的排序规则。排序规则可以对每列进行不同的设置,尽管在创建时没有明确指定,但它将采用数据库默认排序规则。

同时,变量的排序规则是当前数据库的排序规则。但与列引用相比,它的优先级较低。 所以这一切都取决于列的排序规则。

另一方面,诸如

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.NormalizedUserName == userName.ToUpper());

变成

SELECT TOP 1 u.*
FROM Users u
WHERE u.NormalizedUserName = UPPER(@userName);

因此核对的大小写敏感性没有区别。 不过,还有其他排序规则属性可能仍会受到影响。

请注意,您也可以通过这样的规范化来释放信息,这可能是您想要的,也可能不是您想要的。单独的排序规则通常不会剥离大小写信息。这就是为什么通常最好只为这样的列设置一个不区分大小写的排序规则,然后就可以完成了。

无论你做什么, 不要 做这个

var poses = await _dbContext.Users
    .FirstOrDefaultAsync(p => p.UserName.ToUpper() == userName.ToUpper());

它将导致

SELECT TOP 1 u.*
FROM Users u
WHERE UPPER(u.UserName) = UPPER(@userName);

不能使用索引,并且 一个全面的坏主意。