I generally consider DateTimeKind.Unspecified dangerous or maybe even broken therefore I was really surprised one day when I saw that some code generation templates in the Entity Framework actually use Unspecified DateTimeKind. I wanted to check what happens during materialization and, sure enough, the materialized DateTime values that came from EF were of unspecified kind. It seemed weird to me and I wanted to understand why this was happening. To my own surprise I concluded that this behavior (at least for Sql Server – I did not check other databases) actually made sense. Here is why:
DateTime properties can be mapped to columns of the following types (again this is for SqlServer):

• date
• smalldatetime
• datetime
• datetime2

Neither of these types is aware of nor store information about time zones. What it means for date time objects of Utc or Local kind is that the information about their kind is actually lost when the data is persisted in the Sql Server. Now, when the values are read from the SqlServer they will be of Unspecified type (because SqlServer does not store information about time zones and this is the default kind). To understand what it really means you need to take a look at how conversions to Local and Utc times work for DateTime values of Unspecified kind. Basically, if you call DateTime.ToLocalTime() the date is assumed to be a Utc date and if you call DateTime.ToUniversalTime() the date is assumed to be a local date (more details here: http://msdn.microsoft.com/en-us/library/system.datetime.tolocaltime.aspx, http://msdn.microsoft.com/en-us/library/system.datetime.touniversaltime.aspx). In practice it looks like this:

Source Date                           .ToLocalTime()          .ToUniversalTime()
12/12/2000 10:58:49 AM (Unspecified)  12/12/2000 2:58:49 AM   12/12/2000 6:58:49 PM
12/12/2000 10:58:49 AM (Utc)          12/12/2000 2:58:49 AM   12/12/2000 10:58:49 AM
12/12/2000 10:58:49 AM (Local)        12/12/2000 10:58:49 AM  12/12/2000 6:58:49 PM

Based on this table let’s take a look what happens if you write a Utc date value to the database. When I store the following date in the database: 12/12/2000 10:58:49 AM (Utc) and then read it I will get: 12/12/2000 10:58:49 AM (Unspecified). It may seem that these dates are the same they actually are not – the Utc values for these dates are respectively: 12/12/2000 10:58:49 AM and 12/12/2000 6:58:49 PM. The first conclusion is that dates of non-Uspecified kind actually *don’t roundtrip*. The second conclusion is that dates of Unspecified kind *only roundtrip if they are saved and read and used in the same time zone*. The final conclusion is that if your app uses dates that need to be time zone aware (and in today’s highly connected world it probably applies to the vast majority of applications) you should use properties of DateTimeOffset type which will be mapped to columns of datetimeoffset type that is aware of time zone information.

Pawel Kadluczka

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