NVarchar Prefix会导致选择错误的索引 [英] NVarchar Prefix causes wrong index to be selected

问题描述

我有一个实体框架查询，其核心在于：

I have an entity framework query that has this at the heart of it:

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = N'SOME VALUE HERE'

其执行计划选择一个由三列组成的索引。 这需要10到12秒。

The execution plan for this chooses an index that is a composite of three columns. This takes 10 to 12 seconds.

但是，有一个索引只是[SomeOtherOrderId]与include中的其他一些列。这是应该使用的索引。当我运行以下查询时，它被使用：

However, there is an index that is just [SomeOtherOrderId] with a few other columns in the "include". That is the index that should be used. And when I run the following queries it is used:

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = CAST(N'SOME VALUE HERE' AS VARCHAR(200))

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = 'SOME VALUE HERE'

这将立即返回。它使用的索引只是 SomeOtherOrderId

所以，我的问题是我无法真正改变Entity Framework如何进行查询。

我可以从索引的角度做些什么，可能会导致选择正确的索引？

推荐答案

据我所知，从版本4.0起，EF不会为非unicode列生成unicode参数。但是您总是可以通过 DbFunctions.AsNonUnicode （在EF6之前， DbFunctions 是 EntityFunctions ）：

As far as I know, since version 4.0, EF doesn't generate unicode parameters for non-unicode columns. But you can always force non-unicode parameters by DbFunctions.AsNonUnicode (prior to EF6, DbFunctions is EntityFunctions):

from o in db.WidgetOrder
where o.SomeOtherOrderId == DbFunctions.AsNonUnicode(param)
select o

这篇关于NVarchar Prefix会导致选择错误的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！