Viewing Entity Framework generated SQL statements

Sometimes you might need to peer into the innards of Entity Framework and determine if the SQL statements its generating are valid and efficient. Here are a couple of ways one can do this:

Method 1: in code

Use the following code to view the statements being generated.

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();
(where query is an object of type IQueryable<>)

The above code displays statements that look like the following:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

But what if you need to determine what the value of p_linq__0 is? (look at Method 2)

Note: another problem with method 1 is that it cannot be used view the insert and update statements being generated – just queries.

Method 2: Use a profiler

The profiler can be used to get more in detail information about the sql statements being run against the database. The Profiler tool comes with Sql Server. Unfortunately, the one problem with the profiler tool is that its not available for Express editions of Sql Server.

Solution: AnjLab has a free tool that provides similar profiling capabilities that works pretty well. It is called the AnjLab SqlProfiler. Unfortunately it does not come with much documentation on how to set it up to display the sql statements being executed against the database.

Here is how I had to setup the events to begin capturing the sql statements that were being generated by Entity Framework:

I am sure there will be other events that I might need later – but for now the above shown Events list displays almost all the information to determine what SQL statements EF is generating. (I found the RpcStarting event typically contained the parameters being passed in and the SpStmtStarting and SqlStmtStarting contained the queries being executed).

Using the SqlProfiler here is the output I got for the same statement that I showed in method 1 (that was generated using the ToTraceString method):

exec sp_executesql
N'SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0',
N'@p__linq__0 nvarchar(4000)',
@p__linq__0=N'Raj Rao'

It is interesting to note that a stored procedure was used to execute the query and that the name used in the where clause was passed in as a parameter (parameterized queries make me happy)