Logging SQL statements in Entity Framework/Code First

Entity Framework/Code First feature released as part of Feature CTP 3 can work with any EF-enabled data provider.

In addition to regular providers which target databases, it is possible to use wrapping providers which can add interesting functionality, such as caching and tracing. In this post I’m going to explain how to use EFTracingProvider to produce diagnostic trace of all SQL commands executed by EF in Code First.

Setting up EFTracingConnection

In order to use the tracing provider, we need to create a wrapping DbConnection which adds logging every time the command has finished execution. The following helper will set up such connection. As you can see it can log commands to the console, log file or both. Plugging in additional logging mechanisms (such as System.Diagnostics, NLog or log4net) should be trivial.

private EFTracingConnection CreateTracingConnection()
{
    var connection = new EFTracingConnection
    {
        ConnectionString = @"wrappedProvider=" + this.ProviderInvariantName + ";" + this.ConnectionString
    };

    // set up an event which will be raised whenever command has finished executing
    connection.CommandFinished += (sender, e) =>
    {
        try
        {
            if (this.LogToConsole)
            {
                Console.WriteLine(e.ToTraceString());
            }

            if (!string.IsNullOrEmpty(this.LogFileName))
            {
                File.AppendAllText(this.LogFileName, e.ToTraceString() + Environment.NewLine + Environment.NewLine);
            }
        }
        catch
        {
            // catch all exceptions so that we don't pass logging-related failures to user code
        }
    };

    return connection;
}

Object Context Factory

In order to efficiently manage tracing for the application we need to create a central factory class which will create ObjectContext instances for us. This is the place where we will create tracing provider connection and use it to instantiate ObjectContext.

Assuming our Object Context class is called MyContainer, the factory class will be called MyContainerFactory and will have a method called CreateContext, so the usage becomes:

MyContainerFactory factory = ...;

using (MyContainer context = factory.CreateContext())
{
    // use context normally
}

The factory will typically be be long-lived, created at the program startup and stored in a global variable or application state.

Having CreateTracingConnection(), we can now define our factory method by passing instance of the tracing connection to ContextBuilder<T>.Create():

/// <summary>
/// Creates instance of <see cref="MyContainer"/> with tracing enabled.
/// </summary>
/// <returns>Instance of <see cref="MyContainer"/>.</returns>
public MyContainer CreateContext()
{
    return contextBuilder.Create(this.CreateTracingConnection());
}

Simple, isn’t it?

Sample Project

I’ve created a tiny (100 lines of code) sample project which demonstrates this technique. Click here to download it

In the spirit of Code Only the project does not have any non-source artifacts (not even App.config file) and configures everything (model, mapping, tracing) through code:

image

Here is the output you get when running the application – as you can see all statements are logged to the console – log file is also created with similar output.

image

Limitations

There is a known issue with this technique where creating databases is not supported on SqlClient provider. Other providers may or may not support this functionality depending on implementation. In general, because of that it is recommended to use unwrapped connections when using DDL APIs (CreateDatabase, DeleteDatabase, DatabaseExists()) as demonstrated in the sample.

Tags: ,

  1. #1 written by Kris August 24th, 2010 at 11:45

    Hello Jarek

    Thank you for the article and the code.

    When I tried this code with EF 4.0 Code First using SQL Server CE 4.0.
    It is failing in to get the underlying provider information.

    Any suggestions?

    Thanks

    RE Q
  2. #2 written by David October 26th, 2010 at 12:49

    Any chance you can update the sample app to work with the CTP4 version? Running into some syntax issues.

    Thanks

    RE Q
  3. #3 written by bb December 9th, 2010 at 07:31

    That’s rather slick. Thanks a lot. High time to give EFCachingConnection a try…

    RE Q
  4. #4 written by Remco Blok December 23rd, 2010 at 05:15

    Hi Jarek,

    I tried this with CTP5 and a DbContext instead of an ObjectContext and ran into an issue. I posted on that here http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/a501e2c7-d31d-460a-80e5-4d2efc96ca6b.

    kind regards

    Remco

    RE Q

SetPageWidth