Tracing Entity Framework Code First Calls

Microsoft has published, some time ago, a set of Entity Framework providers, for adding caching and tracing capabilities to Entity Framework. One of these providers, tracing, is now available as a NuGet package. This offers an interesting functionality: the ability to see all SQL that Entity Framework is sending to the database, and its results. The provider works with “classic” Entity Framework as well as with Code First, and I am going to focus on the latter and show you how you can use it.

Open up your Code First project and add the CommunityEFProviderWrappers.EFTracingProvider NuGet package:

Now, there are a couple of ways by which you can implement this. I chose to have a static method called CreateTracingContext that returns an instance of my context configured for tracing. For this to work, we need to have in our context a constructor that takes a DbConnection, like the one in DbContext, and just calls the base implementation:

   1: protected MyContext(DbConnection existingConnection) : base(existingConnection, true)

   2: {

   3: }

Next, we need a couple of methods that create a connection wrapper by calling our protected constructor:

   1: private static DbConnection CreateConnection(String nameOrConnectionString)

   2: {

   3:     var connectionStringSetting = ConfigurationManager.ConnectionStrings[nameOrConnectionString];

   4:     var connectionString = "";

   5:     var providerName = "";

   6:  

   7:     if (connectionStringSetting != null)

   8:     {

   9:         connectionString = connectionStringSetting.ConnectionString;

  10:         providerName = connectionStringSetting.ProviderName;

  11:     }

  12:     else

  13:     {

  14:         connectionString = nameOrConnectionString;

  15:         providerName = "System.Data.SqlClient";

  16:     }

  17:  

  18:     return (CreateConnection(connectionString, providerName));

  19: }

  20:  

  21: private static DbConnection CreateConnection(String connectionString, String providerInvariantName)

  22: {

  23:     var wrapperConnectionString = String.Format(@"wrappedProvider={0};{1}", providerInvariantName, connectionString);

  24:     var connection = new EFTracingConnection { ConnectionString = wrapperConnectionString };

  25:     return (connection);

  26: }

And the static method for creating the tracing context:

   1: public static ProjectsContext CreateTracingContext(String nameOrConnectionString, Action<CommandExecutionEventArgs> logAction, Boolean logToConsole = true, String logToFile = null)

   2: {

   3:     EFTracingProviderConfiguration.LogToFile = logToFile;

   4:     EFTracingProviderConfiguration.LogToConsole = logToConsole;

   5:     EFTracingProviderConfiguration.LogAction = logAction;

   6:  

   7:     var ctx = new ProjectsContext(CreateConnection(nameOrConnectionString));

   8:     (ctx as IObjectContextAdapter).ObjectContext.EnableTracing();

   9:     return (ctx);

  10: }

The parameters to the CreatingTracingContext are:

• The name of a registered connection string, or the connection string itself;
• An action delegate that will be executed for each command sent to the database;
• If the tracing provider should log the commands to the console;
• The name of an optional file to which the tracing provider should write all commands.

This leverages on the ADO.NET extensibility, namely, on the possibility to have multiple connection providers. We are almost there! The only thing that’s left is the registration of the new provider. This can either be done on the XML configuration:

   1: <system.data>

   2:   <DbProviderFactories>

   3:     <add name="EF Tracing Data Provider" invariant="EFTracingProvider" description="Tracing Provider Wrapper" type="EFTracingProvider.EFTracingProviderFactory, EFTracingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />

   4:   </DbProviderFactories>

   5: </system.data>

Or by code:

   1: EFTracingProviderFactory.Register();

If you use the code approach, be warned, this must be called very early, that is, before any other ADO.NET call.

Now, you can start tracing:

   1: using (var ctx = MyContext.CreateTracingContext("MyContext", x => {

   2:     Console.WriteLine(x.ToFlattenedTraceString()); }, true, "MyContext.output"))

   3: {

   4:     //perform database operations...

   5: }

This will call the delegate two times for each command: one with the SQL and parameters that are about to be sent to the database, and the second, with its results (or the error). You can tell which is which by looking at the Status property, get the parameters from Command.Parameters, the SQL from Command.CommandText, the time that the execution took from Duration, and the actual result from the Result property of the lambda parameter.

If the third parameter of CreateTracingContext, logToConsole, is set to true, all SQL will be output to the console, and if logToFile has something not null or empty, all of the output will be sent to a file of the same name. Of course, you can achieve all of this and more just by using the delegate.

Let me know if you find this useful!