LINQ-to-SQL 中不区分大小写的字符串比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/841226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case insensitive string compare in LINQ-to-SQL
提问by BlueMonkMN
I've read that it's unwise to use ToUpper and ToLower to perform case-insensitive string comparisons, but I see no alternative when it comes to LINQ-to-SQL. The ignoreCase and CompareOptions arguments of String.Compare are ignored by LINQ-to-SQL (if you're using a case-sensitive database, you get a case-sensitive comparison even if you ask for a case-insensitive comparison). Is ToLower or ToUpper the best option here? Is one better than the other? I thought I read somewhere that ToUpper was better, but I don't know if that applies here. (I'm doing a lot of code reviews and everyone is using ToLower.)
我已经读到使用 ToUpper 和 ToLower 来执行不区分大小写的字符串比较是不明智的，但在涉及 LINQ-to-SQL 时，我认为别无选择。LINQ-to-SQL 会忽略 String.Compare 的 ignoreCase 和 CompareOptions 参数（如果您使用区分大小写的数据库，即使您要求进行不区分大小写的比较，也会得到区分大小写的比较）。ToLower 或 ToUpper 是这里的最佳选择吗？这个比那个好吗？我以为我在某处读到 ToUpper 更好，但我不知道这是否适用于此。（我做了很多代码，每个人都在使用 ToLower。）
Dim s = From row In context.Table Where String.Compare(row.Name, "test", StringComparison.InvariantCultureIgnoreCase) = 0
This translates to an SQL query that simply compares row.Name with "test" and will not return "Test" and "TEST" on a case-sensitive database.
这转化为一个 SQL 查询，它只是将 row.Name 与“test”进行比较，并且不会在区分大小写的数据库上返回“Test”和“TEST”。
采纳答案by Andrew Arnott
As you say, there are some important differences between ToUpper and ToLower, and only one is dependably accurate when you're trying to do case insensitive equality checks.
正如您所说，ToUpper 和 ToLower 之间存在一些重要差异，当您尝试进行不区分大小写的相等性检查时，只有一个是可靠准确的。
Ideally, the best way to do a case-insensitive equality check would be:
理想情况下，进行不区分大小写的相等性检查的最佳方法是：
String.Equals(row.Name, "test", StringComparison.OrdinalIgnoreCase)
NOTE, HOWEVERthat this does not workin this case! Therefore we are stuck with ToUpperor ToLower.
注意，但是，这并不能工作在这种情况下！因此，我们坚持使用ToUpperor ToLower。
Note the OrdinalIgnoreCase to make it security-safe. But exactly the type of case (in)sensitive check you use depends on what your purposes is. But in general use Equals for equality checks and Compare when you're sorting, and then pick the right StringComparison for the job.
注意OrdinalIgnoreCase 以使其安全。但您使用的大小写（不）敏感检查的确切类型取决于您的目的。但通常在排序时使用 Equals 进行相等性检查和 Compare，然后为工作选择正确的 StringComparison。
Michael Kaplan (a recognized authority on culture and character handling such as this) has relevant posts on ToUpper vs. ToLower:
Michael Kaplan（公认的文化和性格处理方面的权威）在 ToUpper 与 ToLower 上有相关帖子：
He says "String.ToUpper – Use ToUpper rather than ToLower, and specify InvariantCulture in order to pick up OS casing rules"
他说“String.ToUpper –使用 ToUpper 而不是 ToLower，并指定 InvariantCulture 以获取 OS 大小写规则”
回答by Andrew Davey
I used
System.Data.Linq.SqlClient.SqlMethods.Like(row.Name, "test")in my query.
我System.Data.Linq.SqlClient.SqlMethods.Like(row.Name, "test")在我的查询中使用
过。
This performs a case-insensitive comparison.
这将执行不区分大小写的比较。
回答by vinahr
I tried this using Lambda expression, and it worked.
我使用 Lambda 表达式尝试了这个，它奏效了。
List<MyList>.Any (x => (String.Equals(x.Name, name, StringComparison.OrdinalIgnoreCase)) && (x.Type == qbType) );
List<MyList>.Any (x => (String.Equals(x.Name, name, StringComparison.OrdinalIgnoreCase)) && (x.Type == qbType) );
回答by Jim Davies
The following 2-stage approach works for me (VS2010, ASP.NET MVC3, SQL Server 2008, Linq to SQL):
以下两阶段方法适用于我（VS2010、ASP.NET MVC3、SQL Server 2008、Linq to SQL）：
result = entRepos.FindAllEntities()
.Where(e => e.EntitySearchText.Contains(item));
if (caseSensitive)
{
result = result
.Where(e => e.EntitySearchText.IndexOf(item, System.StringComparison.CurrentCulture) >= 0);
}
回答by Julio Silveira
where row.name.StartsWith(q, true, System.Globalization.CultureInfo.CurrentCulture)
回答by TAHA SULTAN TEMURI
Sometimes value stored in Database could contain spaces so running this could be fail
有时存储在数据库中的值可能包含空格，因此运行它可能会失败
String.Equals(row.Name, "test", StringComparison.OrdinalIgnoreCase)
Solution to this problems is to remove space then convert its case then select like this
解决此问题的方法是删除空格然后转换大小写然后像这样选择
return db.UsersTBs.Where(x => x.title.ToString().ToLower().Replace(" ",string.Empty).Equals(customname.ToLower())).FirstOrDefault();
Note in this case
在这种情况下注意
customnameis value to match with Database value
customname是与数据库值匹配的值
UsersTBsis class
UsersTBs是类
titleis the Database column
标题是数据库列
回答by John Hansen
To perform case sensitive Linq to Sql queries declare ‘string' fields to be case sensitive by specifying the server data type by using one of the following;
要执行区分大小写的 Linq to Sql 查询，请使用以下方法之一指定服务器数据类型，从而将“字符串”字段声明为区分大小写；
varchar(4000) COLLATE SQL_Latin1_General_CP1_CS_AS
or
或者
nvarchar(Max) COLLATE SQL_Latin1_General_CP1_CS_AS
Note: The ‘CS' in the above collation types means ‘Case Sensitive'.
注意：上述归类类型中的“CS”表示“区分大小写”。
This can be entered in the “Server Data Type” field when viewing a property using Visual Studio DBML Designer.
这可以在使用 Visual Studio DBML 设计器查看属性时在“服务器数据类型”字段中输入。
For more details see http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html
有关更多详细信息，请参阅http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html
回答by Andrew Hare
If you pass a string that is case-insensitive into LINQ-to-SQL it will get passed into the SQL unchanged and the comparison will happen in the database. If you want to do case-insensitive string comparisons in the database all you need to to do is create a lambda expression that does the comparison and the LINQ-to-SQL provider will translate that expression into a SQL query with your string intact.
如果您将不区分大小写的字符串传递到 LINQ-to-SQL，它将被传递到 SQL 中，并且将在数据库中进行比较。如果您想在数据库中进行不区分大小写的字符串比较，您需要做的就是创建一个进行比较的 lambda 表达式，并且 LINQ-to-SQL 提供程序将该表达式转换为 SQL 查询，并且您的字符串保持不变。
For example this LINQ query:
例如这个 LINQ 查询：
from user in Users
where user.Email == "[email protected]"
select user
gets translated to the following SQL by the LINQ-to-SQL provider:
由 LINQ-to-SQL 提供程序转换为以下 SQL：
SELECT [t0].[Email]
FROM [User] AS [t0]
WHERE [t0].[Email] = @p0
-- note that "@p0" is defined as nvarchar(11)
-- and is passed my value of "[email protected]"
As you can see, the string parameter will be compared in SQL which means things ought to work just the way you would expect them to.
如您所见，字符串参数将在 SQL 中进行比较，这意味着事情应该按照您期望的方式工作。
回答by Andrew H
Remember that there is a difference between whether the query works and whether it works efficiently! A LINQ statement gets converted to T-SQL when the target of the statement is SQL Server, so you need to think about the T-SQL that would be produced.
请记住，查询是否有效和是否有效之间是有区别的！当语句的目标是 SQL Server 时，LINQ 语句会转换为 T-SQL，因此您需要考虑将生成的 T-SQL。
Using String.Equals will most likely (I am guessing) bring back all of the rows from SQL Server and then do the comparison in .NET, because it is a .NET expression that cannot be translated into T-SQL.
使用 String.Equals 很可能（我猜）从 SQL Server 带回所有行，然后在 .NET 中进行比较，因为它是一个无法转换为 T-SQL 的 .NET 表达式。
In other words using an expression will increase your data access and remove your ability to make use of indexes. It will work on small tables and you won't notice the difference. On a large table it could perform very badly.
换句话说，使用表达式将增加您的数据访问并消除您使用索引的能力。它适用于小桌子，您不会注意到差异。在一张大桌子上，它的表现可能非常糟糕。
That's one of the problems that exists with LINQ; people no longer think about how the statements they write will be fulfilled.
这是 LINQ 存在的问题之一；人们不再考虑他们写的陈述将如何实现。
In this case there isn't a way to do what you want without using an expression - not even in T-SQL. Therefore you may not be able to do this more efficiently. Even the T-SQL answer given above (using variables with collation) will most likely result in indexes being ignored, but if it is a big table then it is worth running the statement and looking at the execution plan to see if an index was used.
在这种情况下，没有一种方法可以在不使用表达式的情况下执行您想要的操作 - 即使在 T-SQL 中也不行。因此，您可能无法更有效地执行此操作。即使上面给出的 T-SQL 答案（使用带有排序规则的变量）也很可能导致索引被忽略，但是如果它是一个大表，那么值得运行该语句并查看执行计划以查看是否使用了索引.
