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屋!