EF query execution pattern. Usage of sp_executesql vs direct execution of sql statement

This was posted in an internal discussion. The question was that it was observed that sometimes in the generated sql it appeared that EF was very decisive between directly executing the query and sometimes using sp_executesql.

Before I go ahead and explain this behavior a little background on sp_executesql. The sp_executesql comes into play when there is a client side parameterization helping in the re-use of one parameterized compiled plan. More information about Query Parameterization here . When there is no parameter specified then SQL Server tries to do an auto parameterization helping in reuse of query plan.

It appears that the decision to use sp_executesql or a direct sql batch is governed by ADO.Net’s SQLCommand object. It appears per the Tabular Data Stream (TDS) there’s only 2 ways to execute a SQL query – use RPC to execute SQL stored procedure and use SQL Batch for T-SQL. So when we have a parameterized query we tend to use RPC and call sp_executesql.

Now coming back to Entity Framework. A query like this

 var cust = from c in ctx.Customers

           where c.CustomerID == "ALFKI"

           select c;

would produce the following T-SQL

 SELECT 

[Extent1].[CustomerID] AS [CustomerID], 

[Extent1].[CompanyName] AS [CompanyName], 

[Extent1].[ContactName] AS [ContactName], 

[Extent1].[ContactTitle] AS [ContactTitle], 

[Extent1].[Address] AS [Address], 

[Extent1].[City] AS [City], 

[Extent1].[Region] AS [Region], 

[Extent1].[PostalCode] AS [PostalCode], 

[Extent1].[Country] AS [Country], 

[Extent1].[Phone] AS [Phone], 

[Extent1].[Fax] AS [Fax]

FROM [dbo].[Customers] AS [Extent1]

WHERE N'ALFKI' = [Extent1].[CustomerID]

 This is so because we have a constant value for the parameter will not change and hence there is a direct SQLBatch generated. 
Note that at the SQL Server end this will get converted into an auto parameterized query. 

 However, a query like this 

 string CustID = "ALFKI";

                var cust = from c in ctx.Customers

                           where c.CustomerID == CustID

                           select c;

 Would generate the following query

 exec sp_executesql N'SELECT 

[Extent1].[CustomerID] AS [CustomerID], 

[Extent1].[CompanyName] AS [CompanyName], 

[Extent1].[ContactName] AS [ContactName], 

[Extent1].[ContactTitle] AS [ContactTitle], 

[Extent1].[Address] AS [Address], 

[Extent1].[City] AS [City], 

[Extent1].[Region] AS [Region], 

[Extent1].[PostalCode] AS [PostalCode], 

[Extent1].[Country] AS [Country], 

[Extent1].[Phone] AS [Phone], 

[Extent1].[Fax] AS [Fax]

FROM [dbo].[Customers] AS [Extent1]

WHERE [Extent1].[CustomerID] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'ALFKI'

This is so because we have a variable and the value can get changed and hence a dynamic behavior and the use of a stored proc that allows us to change the parameter
value on each successive run