LINQ to Entities 区分大小写比较 [英] LINQ to Entities case sensitive comparison

问题描述

这不是 LINQ to Entities 中区分大小写的比较:

This isn't a case-sensitive comparison in LINQ to Entities:

Thingies.First(t => t.Name == "ThingamaBob");

如何使用 LINQ to Entities 实现区分大小写的比较?

How can I achieve case sensitive comparison with LINQ to Entities?

推荐答案

那是因为您使用的是 LINQ To Entities，它最终将您的 Lambda 表达式转换为 SQL 语句.这意味着区分大小写取决于您的 SQL Server，默认情况下它具有 SQL_Latin1_General_CP1_CI_AS 排序规则，并且不区分大小写.

That's because you are using LINQ To Entities which is ultimately convert your Lambda expressions into SQL statements. That means the case sensitivity is at the mercy of your SQL Server which by default has SQL_Latin1_General_CP1_CI_AS Collation and that is NOT case sensitive.

使用ObjectQuery.ToTraceString查看生成的SQL查询已经实际提交到SQL服务器揭开谜底:

Using ObjectQuery.ToTraceString to see the generated SQL query that has been actually submitted to SQL Server reveals the mystery:

string sqlQuery = ((ObjectQuery)context.Thingies
        .Where(t => t.Name == "ThingamaBob")).ToTraceString();

当您创建 LINQ to Entities 查询时，LINQ to Entities 利用 LINQ 解析器开始处理查询并将其转换为 LINQ 表达式树.然后将 LINQ 表达式树传递给 对象服务 API，将表达式树转换为命令树.然后将其发送到存储提供程序(例如 SqlClient)，后者将命令树转换为本机数据库命令文本.查询在数据存储上执行，结果由对象服务物化成实体对象.两者之间没有考虑区分大小写的逻辑.因此，无论您在谓词中输入什么大小写，SQL Server 始终将其视为相同，除非您更改该列的 SQL Server Collat​​es.

When you create a LINQ to Entities query, LINQ to Entities leverages the LINQ parser to begin processing the query and converts it into a LINQ expression tree. The LINQ expression tree is then passed to Object Services API, which converts the expression tree to a command tree. It is then sent to the store provider (e.g. SqlClient), which convert the command tree into the native database command text. Query get executed on the data store and the results are Materialized into Entity Objects by Object Services. No logic has been put in between to take case sensitivity into account. So no matter what case you put in your predicate, it will always treat as the same by your SQL Server unless you change your SQL Server Collates for that column.

因此，最好的解决方案是将 Thingies 表中 Name 列的排序规则更改为 COLLATE Latin1_General_CS_AS 在 SQL Server 上运行此命令区分大小写:

Therefore, the best solution would be to change the collation of the Name column in the Thingies table to COLLATE Latin1_General_CS_AS which is case sensitive by running this on your SQL Server:

ALTER TABLE Thingies
ALTER COLUMN Name VARCHAR(25)
COLLATE Latin1_General_CS_AS

有关 SQL Server Collat​​es 的更多信息，请查看 SQL SERVER 整理区分大小写的 SQL 查询搜索

For more information on the SQL Server Collates, take a a look at SQL SERVER Collate Case Sensitive SQL Query Search

您可以在客户端应用的唯一解决方案是使用 LINQ to Objects 进行另一个看起来不太优雅的比较:

The only solution that you can apply on client side is to use LINQ to Objects to do yet another comparison which doesn't seem to be very elegant:

Thingies.Where(t => t.Name == "ThingamaBob")
        .AsEnumerable()
        .First(t => t.Name == "ThingamaBob");

这篇关于LINQ to Entities 区分大小写比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！