我可以避免实体框架使用SQL_VARIANT进行查询吗
本文关键字:VARIANT 查询 SQL 实体 可以避免 框架 | 更新日期: 2023-09-27 18:20:43
我首先使用实体框架6代码,我有一个简单的模型:
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);
它肯定很快,但不会。。。。。。
我在IntelliTrace中查看EF ORM生成的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
我添加了SET STATISTICS并在SSMS中进行了测试。
表"任务"扫描计数1。。。
它使用SQL_VARIANT!执行计划是扫描表而不是集群搜索!
EF为什么这么做?!我能避开它吗?
(带有SQL Server 2012的LocalDB)
我发现了一个问题!
这是因为我在IntelliTrace中查找sql。
IntelliTrace将隐藏所有变体,并显示为SQL_variant。
我通过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
使用查找来查询数据是可以的。