Using an ORM like Entity Framework it can often be beneficial to see what goes on "under the hood". When debugging or performance enhancing your applications you need to know how the SQL statements that are executed towards your database looks like. Entity framework has a few ways of doing just that. Lets have a look at a some easy to implement examples.

IQueryable and ToString()

If you want to see how your LINQ query is going to look like when tranformed into SQL all you have to do is call ToString() on your IQueryable

Running the example below

using (var context = new PersonDbContext())
{
    var query = context.Persons.Where(x => x.Age == 34);
    Console.WriteLine(query.ToString());
}

Will output

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[Age] AS [Age]
FROM [dbo].[People] AS [Extent1]
WHERE 34 = [Extent1].[Age]

DbContext.Database.Log

If you want a bit more than just seeing what your generated SQL statements will look like you can use DbContext.Database.Log. This will log all SQL statements executed by the current DbContext. It also adds information about execution time and when the statement was executed.

Running the example below

using (var context = new PersonDbContext())
{
    context.Database.Log = Console.Write;
    var persons = context.Persons.Where(x => x.Age == 34).ToList();
}

will return the following output

Opened connection at 28-04-2016 19:42:54 +02:00

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[Age] AS [Age]
    FROM [dbo].[People] AS [Extent1]
    WHERE 34 = [Extent1].[Age]

-- Executing at 28-04-2016 19:42:54 +02:00

-- Completed in 1 ms with result: SqlDataReader

Closed connection at 28-04-2016 19:42:54 +02:00

The example shown above outputs to the console but you can choose any method that accepts a string.

For instance

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

In an upcoming post I will focus on logging using the IDbCommandInterceptor