我能避免实体框架使用SQL_VARIANT查询？ [英] Can I avoid Entity framework use SQL_VARIANT to query?

问题描述

我6码第一次使用实体框架和我有一个简单的模型：

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

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

和我得到一个模型：

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......

我期待，通过EF ORM产生的的IntelliTrace的SQL，像这样的：

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

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

我添加SET统计和SSMS测试

I add SET STATISTICS and test it in SSMS.

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

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

它使用 SQL_VARIANT ！执行计划扫描表，而不是寻求集群！

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

为什么EF做到这一点？我能否避免呢？

Why EF do it?! Can I avoid it?

（的LocalDB与SQL Server 2012）

(LocalDB with SQL Server 2012)

推荐答案

我发现一个问题！

它的原因的我期待中的IntelliTrace的sql

的IntelliTrace将隐藏所有变种并显示他们的为SQL_VARIANT

IntelliTrace will hide all variant and show them as SQL_VARIANT.

我通过SQL服务器获得实际的SQL探查时，SQL是：

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

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.

这篇关于我能避免实体框架使用SQL_VARIANT查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！