If you use Entity Framework, you know it greatly simplifies database access for the developer. But it can also introduce its own problems particularly when you are writing more complex LINQ to Entities queries which may generate poorly-performing SQL or unexpected results. This is no reason to avoid using Entity Framework, but it helps to know how to inspect and troubleshoot the SQL your code is executing. The following are a few ways I like to inspect and troubleshoot my generated SQL when developing new code or troubleshooting data-related issues.
If you haven’t tried LINQPad, you should go download it, now. At first glance, it may seem like just a simple way to write and run LINQ statements, but it is so much more than that. LINQPad allows you to connect directly to a database so you can write and execute your LINQ statement, but even better, you can import your assembly which contains your DbContext or ObjectContext definitions and write and execute the same code you’d write in your application. Running the code will show you both the results as well as the SQL statements that are generated by your code.
Update: Julie Lerman points out that ToTraceString() is obsolete and best to avoid. I’d take her advice! See EF6 Logging below.
ObjectQuery.ToTraceString() is a simple way to write out the generated SQL query to a debug log or run in the Immediate Window when debugging your code. Depending on the context, you may need to first cast your query to an ObjectQuery before you are able to run ToTraceString(), but this is a quick way to get a look at the generated SQL within your application. The one downside to this approach is that if there are parameters in your generated SQL, you won’t be able to just copy/paste the SQL into SSMS and run it. To do this, I prefer the next option.
3. SQL Server Profiler
This option requires a license for SQL Server and it may take a bit more setup, but it also provides the most accurate way to see exactly what is being run on SQL Server and what is being returned. Unlike ToTraceString(), you will be able to capture the entire SQL query, along with parameters, that you can run and test as needed. I typically will have SQL Server Profiler at the ready any time I am developing a complex query or tuning an existing LINQ to Entities statement. Here is my typical workflow for using SQL Server Profiler:
- Open SQL Server Profiler (either directly, or through the Tools menu in SQL Server Management Studio)
- Connect to your database and optionally select the events you want (I typically uncheck the Audit Events) as well as set column filters (such as Application Name) to only show the results you need.
- Since there may be dozens of SQL statements when loading a single page of your application, I typically run and pause Profiler and put my application into debug mode with a breakpoint set just before the query of interest. Then, when my breakpoint is hit, I can unpause Profiler, step through the code to execute the SQL, then pause Profiler again. This will make it easy to find the SQL I’m looking for in Profiler, rather than hunting through dozens of lines of profiler output. Another option, if you are developing a web application, is to use the “Replay XHR” in the Network tab of your browser’s developer tools. This will send only that request which may simplify and reduce the number of SQL queries being executed.
- Once you identify the line in Profiler related to the query you want, you will see the full T-SQL query in the preview pane. You can now copy this query and paste it into SSMS to run, analyze, and tune the query.
Keep in mind that this option won’t allow you to automatically reverse-engineer the SQL back into LINQ, but it will allow you to see exactly what is being run in SQL Server and confirm that the results are accurate or perhaps show you that Entity Framework is generating inefficient queries based on the code you wrote.
4. Other 3rd-Party Tools (Updated)
It’s been great getting suggestions for other tools in the comments. I’ll add them here for others to try (and for my own reference in the future).
- Glimpse, with EF Extensions
- Hibernating Rhinos EF Profiler
- MiniProfiler, with EF extensions
- EF6 Logging (not 3rd-Party, so even better!) Thanks, @ardalis and @julielerman
Do you have other ways of investigating your Entity Framework queries? If so, let’s hear them in the comments.
Latest posts by Falafel Posts (see all)
- Matching Complex Query String Rewrite Rule in IIS - March 22, 2017
- Disable Content Filters in Sitefinity - March 8, 2017
- On Sitefinity Custom Widget Caching - February 22, 2017
- Dynamic Content Detail Widget Templates in Sitefinity - February 8, 2017
- Using Google Services in UWP C# Apps – Part 2 - February 7, 2017