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

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.

Answer 2

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 3

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 4

One thing to note is that it also provides protection from SQL Injection because of parameterization. Can't really complain about that one...

Answer 5

This is a great question. This isn't really answer but an exploration of the reasoning already given by other answers. Feel free to update this "answer"

The obvious answer would have been "parameterized query cache". However parameters could just as easily been bound when the statement was executed directly and still get cached.

Syntax and details in this MSDN article...

http://msdn.microsoft.com/en-us/library/ms175580(SQL.90).aspx

Performance claims I doubt without data since the cache appears to be the same for both direct and sp_execsql'd queries.

So if it's not those - what is it?

Answer 6

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.