Using Tracing and Caching Provider Wrappers with CodeFirst

Several people have asked recently if it is possible to use tracing and caching provider wrappers (http://code.msdn.microsoft.com/EFProviderWrappers-c0b88f32) with CodeFirst and DbContext and, if it is, how to do it.
Tracing and caching provider wrappers wrap the actual provider that is used to “talk” to the database (e.g. SqlClient provider if you work with Sql Server) to extend the provider with additional functionality. Since the provider layer is one of the lowest layers in the EF stack and CodeFirst is built on top of core EF libraries (think: ObjectContext class and friends) it should be possible to use provider wrappers with CodeFirst and DbContext.  In order to check this I created a very simple CodeFirst (EF 4.3.1 based) console app. I added EFProviderWrapperToolkit, EFCachingProvider and EFTracingProvider projects downloaded from http://code.msdn.microsoft.com/EFProviderWrappers-c0b88f32 to the same solution and added references to the EFCachingProvider and EFTracingProvider projects to my console app project. Finally I registered the EFTracingProvider in my config file as follows:

<connectionStrings>
  <add name="EFProviderWrapperTest" connectionString="wrappedProvider=System.Data.SqlClient; Server=.\SQLEXPRESS;Database=EFProviderWrapperTest; Integrated Security=True; MultipleActiveResultSets=True" providerName="EFTracingProvider" />
</connectionStrings>
<system.data>
  <DbProviderFactories>
    <add name="EF Caching Data Provider" invariant="EFCachingProvider" description="Caching Provider Wrapper" type="EFCachingProvider.EFCachingProviderFactory, EFCachingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
    <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" />
    <add name="EF Generic Provider Wrapper" invariant="EFProviderWrapper" description="Generic Provider Wrapper" type="EFProviderWrapperToolkit.EFProviderWrapperFactory, EFProviderWrapperToolkit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
  </DbProviderFactories>
</system.data>

I also added a setting for the tracing provider to write traces to the console:

<appSettings>
  <!-- write log messages to the console. -->
  <add key="EFTracingProvider.logToConsole" value="true" />
</appSettings>

I started my app and… it did not work. I got this exception:

Unhandled Exception: System.ArgumentException: The provider manifest given
is not of type 'System.Data.SqlClient.SqlProviderManifest'.

The stack trace shows that the problem is somewhere deep in the EntityFramework stack:

   at System.Data.SqlClient.SqlProviderServices.GetSqlVersion
      (StoreItemCollection storeItemCollection)
   at System.Data.SqlClient.SqlProviderServices.DbCreateDatabase
      (DbConnection connection, Nullable`1 commandTimeout, 
      StoreItemCollection storeItemCollection)
   at System.Data.Common.DbProviderServices.CreateDatabase
      (DbConnection connection, Nullable`1 commandTimeout, 
      StoreItemCollection storeItemCollection)
   at EFProviderWrapperToolkit.DbProviderServicesBase.DbCreateDatabase
      (DbConnection connection, Nullable`1 commandTimeout, 
      StoreItemCollection storeItemCollection) in 
      D:\...\EFProviderWrapperToolkit\DbProviderServicesBase.cs:line 143

This not good news. I looked at the sources and the problem is in the SqlClient provider. It expects the SqlProviderManifest but instead gets the DbProviderManifestWrapper. (Interestingly, since the bug is in the provider itself, what I tried so far may actually work for other providers). There wasn’t a lot I could do to fix this bug, so I needed to work it around. The problem is with creating the database, so I was wondering what would happen if I created the database differently – e.g. by using migrations. Enabling migrations with Enable-Migrations command from Package Manager Console (Tools→Library Package Manager→Package Manager Console) ended with the following message (yeah, yet another exception):

System.Collections.Generic.KeyNotFoundException: The given key was not 
present in the dictionary.

Pretty scary but in reality this isn’t something to worry about. The exception is thrown because by default Migrations knows only about SqlClient provider and the provider I have in my connection string in the config file is either EFTracingProvider (in the next version of EF the user should get a more useful message if the provider from the connection string is not registered). The most important thing is that despite of the exception Migrations folder with Configuration.cs file was created. Now, Migrations feature creates the database in the same way as the DbContext – by invoking ObjectContext.CreateDatabase(). It did not work for DbContext so it won’t work for Migrations either. However Migrations allows “to override the connection of the database to be migrated” by setting the Configuration.TargetDatabase property. In my case I am just wrapping the provider to get the tracing but I don’t really care about traces for commands used to migrate database. Therefore for Migrations related tasks I can configure the connection so that the provider wrapper is skipped (btw. I also enabled automatic migrations):

var connectionString = string.Join(
    ";", 
    ConfigurationManager
        .ConnectionStrings["EFProviderWrapperTest"]
        .ConnectionString.Split(';')
        .Where(s => !s.StartsWith("wrappedProvider=")));
TargetDatabase = new DbConnectionInfo(connectionString, "System.Data.SqlClient");
AutomaticMigrationsEnabled = true;

After doing this I ran Update-Database –Verbose command from the Package Manager Console and *surprisingly* I did not get any errors. The SQL script looks OK. The database was created. Finally there is some progress here!
So, I started my app again and… it did not work.
This time I got a NullReferenceException. The good news is that now it is from EFProviderWrapperToolkit.DbConnectionWrapper.Dispose() method – something I should be able to fix as I have sources. The bug can be reproduced very easily:

new EFTracingConnection().Dispose();

The DbConnectionWrapper.Dispose() method is trying to dispose the wrapped connection which has not been initialized yet. I fixed this by simply preventing from trying to dispose the wrapped connection if it was not initialized yet:

if (disposing && this.wrappedConnection != null)
{
    this.wrappedConnection.Dispose();
}

I started my app and… this time it worked – I finally can see traces in my console window!
The road was bumpy but I learned a couple of things:

• it is possible to use provider wrappers with CodeFirst even though the way to set everything up is far from ideal
• I know what needs to be fixed in the EF stack to make it work out of the box and to improve the developer experience

Pawel