News Untold - Confessions of a developer

MVC Forum should be database independent, but...

Right from the start it has been the plan to keep MVC Forum database independent. This should be possible without too much work with the data provider model we have in place. At least that was the plan.

Another focus area of mine, is globalization and localization, part of this is to make MVC Forum able to correctly handle timezones. The forum owner can select the default timezone of the forum, and the users can select which timezone suits them best. And of course this should all be handled correctly.

So this is where the problem is. If you use a regular DateTime property with Entity Framework, you get a DateTime column (with MS SQL server). This datatype does not store the timezone. Well, that shouldn't really be a problem I figured. Whenever we use dates in MVC Forum, we'll just make sure they are always in UTC, that way we only have to do something special when the dates are shown in the UI. Good plan, but then Entity Framework enters the mix!

When Entity Framework populates the properties on the POCOs with data from the database, it will set the DateTimeKind on all datetimes to unspecified. Seriously? No way around this? I haven't been able to find any. I was hoping for some way of getting to work with the datetime values, before they are assigned to the properties, but no!? No convention to handle this? No configuration? No nothing!?

A quick, and unfortunately dirty, way around this is that with version 0.8 we're using DateTimeOffset instead of DateTime for the properties on the POCOs. This makes things a bit complex. Most people are used to working with DateTime, and have no idea what DateTimeOffset is etc. Even worst, to store this property in the database, Entity Framework will create a DateTimeOffset column, which only exists in MS SQL 2008 and later. So now all of a sudden MVC Forum is a "one-database-product". I really hate that!

If you know of any way around this? A better way of handling it, or... Please let me know! We're probably releasing version 0.8 with this "defect", but we're certainly not going to release version 1.0 with a dependency on MS SQL 2008+. I'm sure I can work around this issue in a nHibernate data provider, but we're not going to throw away the Entity Framework provider!

Categories: MVC Forum - Entity Framework - DateTime - DateTimeOffset - MS SQL 2008