问题描述
实体框架核心中的包含"应该等同于 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 核心版本
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()
都会转换为 SQLLIKE
表达式,默认情况下该表达式不区分大小写,除非另外定义了 db 或列排序规则.
In SQLServer
string.contains
converts intoCHARINDEX()
, in oracle and sqlite intoinstr()
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 SQLLIKE
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 :
Specify the column collation on creation using DbContext.OnModelCreating() using this trick
Replace your
string.Contains
byEF.Functions.Like()
Or wait for a promising feature still in discussion :
EF.Functions.Collate()
function
这篇关于实体框架核心 - 包含区分大小写还是不区分大小写?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!