Search code examples
sql-servervb.netentity-frameworklinqdatabase-performance

Why does Entity Framework fire two SQL queries when simply fetching table rows?


I am using Entity Framework 6.1.3, when i fetch the user by username/password as shown below. EF actually fires two SQL queries to the database. I used SQL Server Profiler to inspect the query calls. By preventing second call I can reduce the execution time.

C# code:

Dim oSelectedUser As User = (From oUser As User In oDataContainer.Users Where (oUser.UserName = pUserName And oUser.msPassword = pPassword) Select oUser Where oUser.IsActive = True).SingleOrDefault

Query #1:

SELECT TOP (2) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserName] AS [UserName], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[Image] AS [Image]
FROM [dbo].[User] AS [Extent1]
WHERE 
    ([Extent1].[UserName] = @p__linq__0) AND ([Extent1].[Password] = @p__linq__1)

Query #2:

exec sp_executesql N'SELECT TOP (2) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserName] AS [UserName], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[Image] AS [Image]
    FROM [dbo].[User] AS [Extent1]
    WHERE ([Extent1].[UserName] = @p__linq__0) AND ([Extent1].[Password] = @p__linq__1)',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'usr',@p__linq__1=N'zReNgKelg'

Sql profiler event:

enter image description here


Solution

  • The two events are essentially showing you the same statement. the RPC:Completed event is for when the whole call is completed, and the SP:Stmt is for when a statement within the stored procedure is completed. In this case, they are both one and the same thing.