Problem Entity Framework doesn't use sp_executesql to execute in .Net 4.0?

Question

I using Entity Framework in .net 4.0.  I try to using SQL Profiler to monitor sql statement from Entity Framework executed that I saw all sql statement that it doesn't use sp_executesql to execute.   But LinqToSql will use sp_executesql to execute all sql statement.  For my knowledge, sp_executesql will use cached plan to get more performance.  My question is why Entity Framework doesn't use sp_executesql to execute? 

---

Eagle

Answer 1

No any Entity Framework professional or MS professional here?

---

Eagle

Answer 2

Hello Eagle,

 

Welcome to EF forum!

 

Could you please provide us with more detailed information about the queries you are testing in both EF and LINQ to SQL?   Based on my test, EF4 will also use sp_executesql to execute certain SQL statements as LINQ to SQL does.   Also, LINQ to SQL does not use sp_executesql for every SQL statements.  

 

Besides, EF has its own query plan caching in Entity SQL, http://msdn.microsoft.com/en-us/library/bb738562.aspx.  

 

Good day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi Lingzhi,

 

What information you need?

---

Eagle

Answer 4

Hi Eagle,

 

Like what kind of queries you are testing, please.   Some sample codes would be great. 

 

 

Good day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

WMSEntities db = new WMSEntities();

 var x = from zone in db.Zone

             select zone;

var test = x.ToArray();

---

Eagle

Answer 6

Hi Eagle,

 

For such a LINQ to Entities query, a similar LINQ to SQL query won’t use sp_executesql either.  

 

The use of sp_executesql is actually the only way to execute a parameterized dynamic SQL statement in SQL Server.   There are two primary “execute” primitives in TDS, “batch” and “RPC”. A batch request just contains a T-SQL string, and can be used whenever there is no need for parameters.  An RPC request is a request that takes a procedure name (no arbitrary SQL) and a set of parameters. This can be used for example to execute a stored-procedure directly. None of these cover executing a parameterized SQL statement. In order to do that SQL Server has sp_executesql, which is a system stored-procedure that takes a SQL string with parameter markers and a variable number of arguments for the parameters. So the client issues an RPC request to sp_executesql whenever it has to execute a parameterized statement.

 

Such a query won’t use sp_executesql:

==============================================================

                var cust = from c in ctx.Customers
                           where c.CustomerID == "ALFKI"
                           select c;

==============================================================

 

However, such a query does use sp_executesql to pass a parameter:

==============================================================

                string CustID = "ALFKI";
                var cust = from c in ctx.Customers
                           where c.CustomerID == CustID
                           select c;

==============================================================

 

Good day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

Why doesn't use sp_executesql to execute all sql statement?  Why is selective? What benefit in this behavior?

---

Eagle

Answer 8

Hi Eagle,

 

Thanks for your following up!

 

As I said, sp_executesql is used to execute a parameterized dynamic SQL statement.  For non-parameterized queries, we don’t need to use sp_executesql.   That’s orthogonal to whether the query is submitted directly in a batch or wrapped in sp_executesql.  In either case we should generally use parameters to promote plan reuse, and auto-parameterization or forced parameterization might replace a non-parameterized query with a parameterized one, http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx.

Good day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 9

Hi Eagle,

 

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.