How to View SQL Queries Generated by Entity Framework

Entity Framework works by translating C# code into SQL statements, executing those statements on a target database, and taking care of mapping any return values back to C# objects. But have you ever wondered what the generated SQL statements actually look like? In this post we will be using a free and simple tool that lets us view the SQL executed on the database.

The Schema

For demonstration purposes, we will be using a very simple class called Book with only two properties:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
}

// ...

public class ApplicationContext : DbContext
{
    public DbSet<Book> Books { get; set; }
}

This translates into a table with only two columns - an Id primary key and a Title:

Express Profiler

The tool we will be using is ExpressProfiler. This is an extremely lightweight and simple program that can monitor a database server for the queries being executed. You can download the tool from http://expressprofiler.codeplex.com/.

Once you download and open the program, you will see that it has a nice, clean interface. These are the steps to start using Express Profiler (see accompanying image at the bottom):

1. Type in the server name.
2. Choose the authentication: Windows auth or SQL Server auth. If using SQL Server auth, also put in the username and password.
3. Click on the Start trace button, marked with a green arrow pointing right.
4. Each SQL statement executed will appear in the grid, one statement for each row.
5. Clicking on any item on the grid will populate the lower pane with the entire query.

Let's get started!

SELECT

The first thing we will try is to retrieve all the books in the database. Here is the C# code:

using (var db = new ApplicationContext())
{
    var allBooks = db.Books.ToList();
}

When this code is run, what does the executed SQL look like? It looks like this:

Woah, there are a lot of queries! Why is that?

Well, one query is for checking migrations. Before Entity Framework tries to do anything with the database, it checks first if the model classes are in sync with the database schema and if all the migrations have been applied. Other queries are also used to get information about the database. There are also several calls to sp_reset_connection, which indicates the use of connection pooling.

But we aren't interested in the other queries at the moment; for now, let's turn our attention to the last query executed:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
    FROM [dbo].[Books] AS [Extent1]
go

Our db.Books.ToList() call has been converted to this statement. It's pretty simple, but what's up with the use of the [Extent1] alias? Well, typically aliases are used for JOIN statements. But to cover all scenarios possible, Entity Framework uses theses aliases even when there is no JOIN involved.

Let's try some other queries with filters.

Find, First, and Single

Now let's try using Find, First, and Single:

using (var db = new ApplicationContext())
{
    var bookOne = db.Books.Find(1);
    var bookTwo = db.Books.First(b => b.Id == 2);
    var bookThree = db.Books.Single(b => b.Id == 3);
}

Here is the query generated for Find:

exec sp_executesql N'SELECT TOP (2) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
    FROM [dbo].[Books] AS [Extent1]
    WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1
go

Notice that we are now using sp_executesql and introducing a parameter p0 in the query. Why is it using parameters and not put the value in the WHERE check directly? The reason is that the Find method works for any key type, not just ints. Using this parameter style also covers the case where the key is not an int (for example, a Guid).

There is another peculiarity here - why is it selecting the TOP 2? Doesn't Find return only a single result? That is because Find throws an exception when there is more than one result found. Therefore, Entity Framework selects 2 rows; it throws an exception when there are two matches.

Here is the SQL statement corresponding to the First method:

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
    FROM [dbo].[Books] AS [Extent1]
    WHERE 2 = [Extent1].[Id]
go

Except for the reverse-ordered WHERE check at the end, this statement is pretty much what we'd expect.

And now for the SQL executed from the Single method:

SELECT TOP (2) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
    FROM [dbo].[Books] AS [Extent1]
    WHERE 3 = [Extent1].[Id]
go

This is very similar to the SQL generated by the First method, with the exception that it selects a TOP 2, like the Find method. The reason is similar - it throws an exception when there are two results found. The difference with Find is that Single will throw an exception when there are no matches. The other difference is that it didn't use any parameters, since in our C# code we used the Id property directly.

Add

Now let's try adding a book:

using (var db = new ApplicationContext())
{
    var newBook = new Book
    {
        Title = "Things Fall Apart"
    };

    db.Books.Add(newBook);
    db.SaveChanges();
}

Here is the generated SQL query:

exec sp_executesql N'INSERT [dbo].[Books]([Title])
VALUES (@0)
SELECT [Id]
FROM [dbo].[Books]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 nvarchar(max) ',@0=N'Things Fall Apart'
go

This time, the single statement is actually executing twice: one to insert the book and one to retrieve the Id of the newly inserted book. That is how Entity Framework can auto-populate the entity Ids after calling SaveChanges whenever a new entity is added.

Update

Now let's try updating:

using (var db = new ApplicationContext())
{
    var existingBook = new Book
    {
        Id = 1,
        Title = "The Hitchhiker's Guide to the Galaxy"
    };

    db.Books.Attach(existingBook);
    db.Entry(existingBook).State = EntityState.Modified;
    db.SaveChanges();
}

And here is the generated SQL code:

exec sp_executesql N'UPDATE [dbo].[Books]
SET [Title] = @0
WHERE ([Id] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'The Hitchhiker''s Guide to the Galaxy',@1=1
go

We see that the query now uses two parameters, @0 for the Title and @1 for the Id. The important part here is that Entity Framework was able to detect that the Id is the primary key and therefore use it in the WHERE statement.

Conclusion

In this post we saw how to view the SQL statements generated by Entity Framework. We used the easy-to-use ExpressProfiler program to help us. We also experimented with different flavors of SELECT, and also saw examples of INSERT and UPDATE statements.

Having the ability to view the SQL statements helps us understand how Entity Framework works and can also help use diagnose performance issues.