EF 4 在 SQL 中生成 UNICODE 字符串常量，其中列类型为 varchar。如何避免？
在我的代码中，我有以下 L2E 查询片段：
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN") && ol.olstatus == "9"
这将转换为以下 SQL 片段：
WHERE ([Extent8].[ohcustno] = @p__linq__1) AND (''IN'' = [Extent7].[ollastdoctype]) AND (''9'' = [Extent7].[olstatus]) ...
对于某个输入，查询执行 3 秒。我以这种方式更改查询：
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN" || ol.ollastdoctype == "CR") && ol.olstatus == "9"
生成的 SQL 更改如下：
WHERE ([Extent6].[ohcustno] = @p__linq__1) AND ([Extent5].[ollastdoctype] IN (N''IN'',N''CR'')) AND (''9'' = [Extent5].[olstatus]) ...
请注意，由于某些奇怪的原因，实体框架决定将我的 IN 和 CR 转换为 unicode。结果是查询现在对同一输入执行 6 秒。如果我手动从 IN 子句中删除 N 前缀并在 SSMS 中重新运行查询，则执行时间将恢复到 3 秒。这当然是因为 SQL Server 查询优化器无法利用索引，因为比较的类型现在不同（varchar 与 nvarchar）
谁能解释一下为什么实体框架突然决定将我的常量转换为 unicode，我该如何避免它吗？
In my code I have the following fragment of a L2E query:
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN") && ol.olstatus == "9"
This translates to following SQL fragment:
WHERE ([Extent8].[ohcustno] = @p__linq__1) AND (''IN'' = [Extent7].[ollastdoctype]) AND (''9'' = [Extent7].[olstatus]) ...
On a certain input the query executes 3 seconds. I change the query this way:
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN" || ol.ollastdoctype == "CR") && ol.olstatus == "9"
and the resulting SQL changes are as follows:
WHERE ([Extent6].[ohcustno] = @p__linq__1) AND ([Extent5].[ollastdoctype] IN (N''IN'',N''CR'')) AND (''9'' = [Extent5].[olstatus]) ...
Note, that for some bizarre reason Entity Framework decided to convert my IN and CR to unicode. The result is that the query now executes 6 seconds on the same input. If I manually remove the N prefix from the IN clause and re-run query in SSMS the execution time goes back to 3 seconds. This is of course because SQL Server Query Optimizer can't get advantage of an index because compared types are now different (varchar vs nvarchar)
Can anyone explain me why Entity Framework all of a sudden decides to convert my constants to unicode and how can I avoid it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以尝试此方法 EntityFunction.AsNonUnicode，如下
这只是最后的希望，下一步是将 bug 报告给微软。
you can try this method EntityFunction.AsNonUnicode, as follow
This is only last hope, next is report bug to microsoft.
EntityFunction.AsNonUnicode 解决方法实际上非常有限，它仅在提供的值是文字或字符串时才有效：
这是 EF4.1 中的一个严重问题，也已记录在此处：
http://connect.microsoft.com/VisualStudio/feedback/details/650410/entity-framework-contains-still-defaulting-to-unicode-for-varchar-fields
直到这是EF 本身已修复，除了拦截查询并使用 EFTraceProvider 等手动替换语法之外，没有其他解决方法。
野蛮。
The EntityFunction.AsNonUnicode workaround is actually quite limited, it only works when the value supplied is either a literal or a string:
This is a serious problem in EF4.1 and has been documented here as well:
http://connect.microsoft.com/VisualStudio/feedback/details/650410/entity-framework-contains-still-defaulting-to-unicode-for-varchar-fields
Until this is fixed in EF itself, there is no workaround short of intercepting the query and manually replacing the syntax using something like EFTraceProvider.
Brutal.
该问题已在最新的 EF 版本中得到正式解决。您可以使用 DataAnnotations 定义列类型。希望这对某人有帮助！
请参阅此答案：EF 数据注释列类型
This issue has been officially resolved in the latest EF versions. You can define the column type using DataAnnotations. Hope this helps someone!
See this answer: EF Data Annotations Column Type
在 ODP.net Beta 2 发布之前这是一个问题，但随着 ODP.net 4.112.2.50 Beta3 版本的发布，这个问题得到了解决。
This was a problem till ODP.net Beta 2 release but with Beta3 release of ODP.net 4.112.2.50 this problem is solved.