Entity Framework and DateTime of Unspecified kind

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