Why does LINQ send sp_executesql instead of directly executing the SQL?

Question

For eaxmple, LINQ to SQL is sending the following:

exec sp_executesql 
N'SELECT [t0].[HomeID], 
  [t0].[Bedrooms], 
  [t0].[ImageURL], 
  [t0].[Price], 
  [t0].[Available], 
  [t0].[Description]
FROM 
  [dbo].[Homes] AS [t0]
WHERE 
  ([t0].[Description] LIKE @p0) AND 
    ([t0].[Available] = @p1) AND 
    ([t0].[Price] >= @p2) AND ([t0].[Price] <= @p3)
ORDER BY 
  [t0].[Price] DESC',
N'@p0 nvarchar(4000),@p1 int,@p2 int,@p3 int',
@p0=N'%private%',
@p1=1,
@p2=200000,
@p3=750000

Why does it use sp_executesql?

Answer 1

This notation allows the runtime compiled TSQL statement to be re-used with different parameters; i.e. the statement only gets compiled once which improves efficiency.

Answer 2

This is, at least partially, so that you get query plan reuse. It could put the parameters inline, which means that every time you run the query with different parameters the analyzer sees it as a different query and reparses it. But since it's executed this way, the query plan is cached and it can just plug in the new variables each time you run it.

Answer 3

I do not think it is a performance solution. Execution plans in SQL are cached even for direct queries.

I do think it is a security solution for sql injection.

However if you try to use traces that use Linq to SQL in the Database Engine Tuning Advisor the sp_execute is not interpeted by the Tuning Advisor, I would like to turn it off. SQL injection can also be detected in the Linq To Sql statements / framework (the code) why would you even try to send invalid data to SQL.

Answer 4

EDIT, oops did I say paramterization instead of parameter replacement, thanks stephbu

sp_executesql is preferred over execute because it supports parameter substitution, and tends to perform more efficiently.