实体框架核心 - 包含区分大小写还是不区分大小写? [英] Entity Framework core - Contains is case sensitive or case insensitive?

问题描述

实体框架核心中的包含"应该等同于 SQL %like% 运算符.因此包含"应该不区分大小写，但它区分大小写！(至少在 postgres 中????)

"Contains" in Entity Framework core should equivalent to the SQL %like% operator. Therefore "Contains" should be case insensitive however it is case sensitive! (at least in postgres????)

以下仅在使用正确的关键字大小写时输出结果.

The following only outputs a result when the correct casing for keyword is used.

context.Counties.Where(x => x.Name.Contains(keyword)).ToList();

我做错了什么?

推荐答案

旧版本的 EF 内核曾经是这种情况.现在 string.Contains 区分大小写，例如 sqlite 它映射到 sqlite 函数 `instr()'(我不知道 postgresql).

It used to be the case for older versions of EF core. Now string.Contains is case sensitive, and for exemple for sqlite it maps to sqlite function `instr()' ( I don't know for postgresql).

如果您想以不区分大小写的方式比较字符串，您可以使用 DbFunctions 来完成这项工作.

If you want to compare strings in a case-insensitive way, you have DbFunctions to do the jobs.

context.Counties.Where(x => EF.Functions.Like(x.Name, $"%{keyword}%")).ToList();

更新@Gert:

问题中的部分假设是不正确的.string.Contains 不会转换为 LIKE 表达式，即使在 ef 核心版本 <= 1.0(我认为)中曾经是这种情况.

A part of the assumption in the question is incorrect. string.Contains does NOT convert into a LIKE expression even though it USED to be the case in ef core versions <= 1.0 (I think).

• 在 SQLServerContainsOptimizedTranslator.cs#L32" string.contains 转换成 CHARINDEX()，在 oracle 和 sqlite 进入instr()默认情况下区分大小写除非另外定义了 db 或列排序规则(同样，我不知道 postgresql ).
• 在所有情况下，EF.Functions.Like() 都会转换为 SQL LIKE 表达式，默认情况下该表达式不区分大小写，除非另外定义了 db 或列排序规则.

• In SQLServer string.contains converts into CHARINDEX(), in oracle and sqlite into instr() which are case sensitive by default UNLESS db or column collation is defined otherwise ( Again, I don't know for postgresql ).
• In all cases EF.Functions.Like() converts into a SQL LIKE expression which is case-insensitive by default unless db or column collation is defined otherwise.

所以是的，这一切都归结为整理，但是 - 如果我错了，请纠正我 - 在某种程度上，代码可能会对区分大小写/不敏感的搜索产生影响，具体取决于您使用的上述方法之一.

So yes it all goes down to collation but - correct me if I'm wrong - in a way the code can have an influence on the case-sensitive/insensitive search depending on which one of the above method you use.

现在，我可能没有完全了解最新情况，但我认为 EF 核心迁移不会自然地处理 DB 排序规则，除非您已经手动创建了表，否则最终会使用默认排序规则(区分大小写)老实说，sqlite 和我不知道其他人).

Now, I might not be completely up to date but I don't think EF core migrations deal with DB collation naturally and unless you've already created the table manually you will end up with the default collation (case-sensitive for sqlite and I honestly don't know for the others).

回到最初的问题，您至少有 2 个选项来执行这种不区分大小写的搜索，如果在未来版本中不是 3 个:

Getting back to the original question you have at least 2 options to perform this case-insensitive search if not 3 in a future release :

1. 使用此 技巧
2. 将您的 string.Contains 替换为 EF.Functions.Like()
3. 或者在讨论中等待一个有前途的功能:EF.Functions.Collat​​e() 函数

1. Specify the column collation on creation using DbContext.OnModelCreating() using this trick
2. Replace your string.Contains by EF.Functions.Like()
3. Or wait for a promising feature still in discussion : EF.Functions.Collate() function

这篇关于实体框架核心 - 包含区分大小写还是不区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！