Can I avoid the Entity structure using SQL_VARIANT for the query?

First I use Framework 6 entity code and I have a simple model:

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

      

And I get the model:

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

      

or

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

      

Of course, quickly, but not ...

I am looking at the SQL in IntelliTrace that is generated by the EF ORM, for example:

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 am adding SET STATISTICS and testing it in SSMS.

Task table. Number of scans 1 , ...

It uses SQL_VARIANT ! The execution plan is a scan table instead of clustering!

Why is EF doing this ?! Can I avoid it?

(LocalDB with SQL Server 2012)

+3


source to share


1 answer


I found the problem!

This calls I am looking at sql in IntelliTrace .

IntelliTrace will hide all variants and show them as SQL_VARIANT .



I got the actual SQL from SQL Server Profiler, 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 to use search queries.

+4


source







All Articles