我可以避免使用SQL_VARIANT查询实体框架吗？

I use entity framework 6 code first and I have a simple model:

我首先使用实体框架6代码，我有一个简单的模型：

public class Task
{
    [Key]
    public int aid {get;set;}
    [MaxLength(256)]
    public string Memo {get;set;}
}

And I get a model:

我得到一个模型：

int id = 3;
from t in db.Tasks
where t.aid == id
select t;

要么

int id = 3;
db.Tasks.Find(id);

It's sure be fast but not......

肯定是快但不......

I look the SQL in IntelliTrace that generated by EF ORM, like this:

我查看由EF ORM生成的IntelliTrace中的SQL，如下所示：

DECLARE @p__linq__0 AS SQL_VARIANT;
SET @p__linq__0 = 3;

SET STATISTICS TIME ON 
SET STATISTICS IO ON 

SELECT 
    [Limit1].[aid] AS [aid], 
    [Limit1].[Memo] AS [Memo]
    FROM ( SELECT TOP (1) 
        [Extent1].[aid] AS [aid], 
        [Extent1].[Memo] AS [Memo]
        FROM [dbo].[Task] AS [Extent1]
        WHERE [Extent1].[aid] = @p__linq__0
    )  AS [Limit1]

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

I add SET STATISTICS and test it in SSMS.

我添加了SET STATISTICS并在SSMS中测试它。

Table 'Task'. Scan count 1, ...

表'任务'。扫描计数1，...

It use SQL_VARIANT! Execution plan is scan table instead of clustered seeking!

它使用SQL_VARIANT！执行计划是扫描表而不是集群搜索！

Why EF do it?! Can I avoid it?

EF为什么这样做？！我可以避免吗？

(LocalDB with SQL Server 2012)

（使用SQL Server 2012的LocalDB）

1 个解决方案

#1

I found a problem!

我发现了一个问题！

It's cause I look the sql in IntelliTrace.

这是因为我在IntelliTrace中查看sql。

IntelliTrace will hide all variant and show them as SQL_VARIANT.

IntelliTrace将隐藏所有变体并将它们显示为SQL_VARIANT。

I got actual SQL by SQL Server Profiler, the sql is:

我通过SQL Server Profiler得到了实际的SQL，sql是：

exec sp_executesql N'SELECT 
[Limit1].[aid] AS [aid], 
[Limit1].[Memo] AS [Memo]
FROM ( SELECT TOP (1) 
    [Extent1].[aid] AS [aid], 
    [Extent1].[Memo] AS [Memo]
    FROM [dbo].[Task] AS [Extent1]
    WHERE [Extent1].[aid] = @p__linq__0
)  AS [Limit1]',N'@p__linq__0 int',@p__linq__0=3

It's OK that use seeking to query data.

使用寻求查询数据是可以的。

#1