score:4
Agree with Adam. Use of sp_executesql in entity framework/ADO.net is intentional. The queries are then executed similar to parameterized stored proc and SQL optimizer can reuse the query plan.
If you are looking to tune your DB, you should consider these queries as well. I suggest you,take a backup of your DB, capture the queries using replay trace template (in your SQL profiler) , restore your DB, run your tuning advisor settings its workload to this trace
score:2
The usage of sp_executesql is an ADO.NET effect, not something specific to the Entity Framework. As far as I am aware, there's no way to instruct it not to use that stored procedure.
score:3
That is just an artifact of how RPC calls are shown in profiler. There are two main types of client requests: Language (type 0x01) and RPC (type 0x03), as documented by the Free TDS protocol documentation. When the call is a SQL batch with parameters, the RPC call will be of type 0x03 with length 0x0A which is a shortcut for sp_executesql.
So you see, what really happens is that when a client, any client, submits a batch that contains parameters, it will appear as if sp_executesql is being called. This is true with ODBC, with OleDB, SqlClient, Sql Native Client, as I said, any client. So is not Entity Framework, nor ADO.Net that actually calls sp_executesql (in fact, the procedure is not even really called, although the requests executes as if it was called. Is complicated...). It is an artifact of the protocol that happens anytime you add an @parameter to your request.
score:6
LINQ/.NET runs SQL queries by sending them as sp_executesql calls to SQL server. However, Database Engine Tuning Advisor can't parse such, by SQL Server Profiler, captured statements, so the result of the tuning session will be poor.
This is how I 'unbox' the sp_executesql statements before feeding them to Database Engine Tuning Advisor so it can analyze them correctly.
- After you've run (or opened an existing) tuning session in SQL Server Profiler, use File>Export>Extract SQL Server Events>Extract Transact-SQL Events... to save a .SQL file containing all SQL statements.
- Open the .SQL file (in e.g. Notepad++ 6 - http://notepad-plus-plus.org/) and run the following Find & Replacement Regular Expression to unbox all
sp_executesqlstatements to plain TSQL. Find:^EXEC[\s]+SP_EXECUTESQL[\s]+[N]*'((''|[^'])*)'[\s]*,[\s]*[N]*'((''|[^'])*)'[\s]*,[\s]*([^\n]+)$Replace:BEGIN\nDECLARE \3\nSELECT \5\n\1\nEND - Next, use the 'unboxed' .SQL filed that we've just constructed in Database Engine Tuning Advisor to find out what indexes you neeed to add.
Happy tuning! Please note that I've selected Notepad++ 6 (or above) since it has a pretty good Regular Expression implementation, other texteditors may fail at running the expression above.
Source: stackoverflow.com
Related Articles
- Enumerable.Empty<T>().AsQueryable(); This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code
- LINQ Source Code Available
- Refactor Linq code and "LINQ to Entities does not recognize the method"
- creating Linq to sqlite dbml from DbLinq source code
- EF Code First - Linq to Entities Union EqualityComparer
- linq to entities changing database connection string in code
- code first approach error: the specified type member 'yyyxx' is not supported in linq to entities
- LINQ to Entities similar code
- source code for LINQ 101 samples
- How to LINQ Query Code First generated EF6 hierarchical entities (entities within entities)?
- Linq to entities hard code list
- Entity Framework 4 and Linq to Entities specifications: How to code it?
- Why is my Linq to Entities code duplicating rows?
- c# Linq or code to extract groups from a single list of source data
- Entity Framework Code First - The entity or complex type cannot be constructed in a LINQ to Entities query
- Convert string[] to int[] in one line of code using LINQ
- Linq to Entities - SQL "IN" clause
- Code equivalent to the 'let' keyword in chained LINQ extension method calls
- Entity framework linq query Include() multiple children entities
- EF LINQ include multiple and nested entities
- LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression
- LINQ to Entities does not recognize the method
- Linq code to select one item
- LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object)'
- Bulk-deleting in LINQ to Entities
- Using LINQ to Update A Property in a List of Entities
- LINQ to Entities does not recognize the method Int32 get_Item(Int32)
- The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities
- How are people unit testing code that uses Linq to SQL
- Problem with LINQ to Entities and String.StartsWith
- Mongodb C# FindAsync. Filter on list inside document using linq
- Query multiple tables c# linq efficiently
- Linq count and sum in join
- How to join group item of Linq Query Group By?
- ToListAsync() in a DbContext using statement : "The ObjectContext disposed", How to deal?
- save image order after drag and drop into database mvc
- Access one column from a list to use in another query
- Getting all objects from all IEnumerables within all IEnumerables
- Ordering list by many conditions using Linq
- select properties of entity ef linq
- Can this fragment of XML be parsed with LINQ?
- Linq to Entites Query does not retrieve the records
- Where command, XML Feed Windows phone 8
- How include another field on the Group BY
- How to group a list and display a inner list in a datatable by linq
- Performing a Contains within an Intersect
- Convert downgrade Linq to normal C# .NET 2.0 for domainname-parser codes that use publicsuffix.org
- How to convert sql query to linq query
- How to get the list of items of a particular class using LINQ?
- how to print the innertext of an element based on attribute search of a particular node using LINQ?
