Logging and Tracing SQL Queries using Entity Framework 5.0 Code First DbContext

During the development of my last application, I had a problem I encountered when there was a need to look at all queries to our entity framework database. After a lot of googling time I didn’t find the solution that works fine for entity framework 5.0 database code first using DbContext (not ObjectContext or DataContext). After all – I have found the  library called Clutch, that you can install via NuGet and use inside your project. The solution is very simple and elegant and only this works with the given conditions. Only thing you need to do is to implement an interface IDbTracingListener. That is how it looks like in the Clutch:

namespace Clutch.Diagnostics.EntityFramework

{

  public interface IDbTracingListener

  {

    void CommandExecuting(DbTracingContext context);

    void CommandFinished(DbTracingContext context);

    void ReaderFinished(DbTracingContext context);

    void CommandFailed(DbTracingContext context);

    void CommandExecuted(DbTracingContext context);

  }

}

So add new class to your application:

    /// <summary>

    /// Implementation of IDbTracingListener

    /// Class is used for tracing all SQL Queries to the entity framework database

    /// </summary>

    public class DbTracingListener : IDbTracingListener

    {      

        public void CommandExecuting(DbTracingContext context)

        {

            //implementation if needed here..

        }

        public void CommandFinished(DbTracingContext context)

        {

            //implementation if needed here..

        }

        public void ReaderFinished(DbTracingContext context)

        {

            //implementation if needed here..

        }

        public void CommandFailed(DbTracingContext context)

        {

            Debug.WriteLine("\nFAILED\n " + context.Command.CommandText);

            // or Trace.WriteLine("\nFAILED\n " + context.Command.CommandText);

        }

        public void CommandExecuted(DbTracingContext context)

        {

            Debug.WriteLine("\nExecuted\n " + context.Command.CommandText);

            // or Trace.WriteLine("\nExecuted\n " + context.Command.CommandText);

        }

    }

Don’t forget to add using statements:

using System.Diagnostics;

using Clutch.Diagnostics.EntityFramework;

Inside methods like “CommandExecuted” you can do everything you want with SQL statements to your DB that you can get via context.Command.CommandText.

The last thind is to enable tracing in Global.asax file in Application_Start method (for ASP.NET MVC application. For any other – do it in the entry point):

// Enable Tracing queries

DbTracing.Enable();

// Adding the listener (implementation of IDbTracingListener)

DbTracing.AddListener(new DbTracingListener());