实体框架核心-包含区分大小写还是不区分大小写?

问题描述

包含"应等效于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 expression，即使在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).

• 在 SQLServer string.contains在CHARINDEX() .cs#L33"rel =" noreferrer> oracle 和 sqlite 到instr()中，默认情况下它们区分大小写，除非定义了db或列排序规则(否则，对于postgresql我也不知道). /li>
• 在所有情况下EF.Functions.Like()都将转换为SQL LIKE表达式，默认情况下，该表达式不区分大小写，除非另行定义了数据库或列排序规则.

• 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核心迁移无法自然地处理数据库排序规则，除非您已经手动创建了表，否则最终将使用默认排序规则(区分大小写) 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).

回到原始问题，如果将来的发行版中没有3个选项，则至少有2个选项可以执行此不区分大小写的搜索:

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.Collate()函数

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

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