Quick Tip: Fixing the datetime2 problem with Entity Framework

Quick Tip

Ever run into the error “Type datetime2 is not a defined system type.” when working with your newly polished and adored Entity Framework model? I did, and here’s how you fix it.Most of the time you probably won’t run into this error. You WILL most likely run into this error if you are on a larger development team with some folks running SQL Server 2008 and some folks are running SQL Server 2005. If you open up your .edmx file for your data model in the XML editor, you’ll find this code around line 7:

<Schema Namespace="CPTTModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store=....

The important thing to look at is the “ProviderManifestToken” parameter. According to Microsoft, this parameter helps the entity model function while not connected to the database, I think it also helps best structure the queries it generates against the target database. The tricky thing is that every time you update the data model, even to move an object around in the display editor, this value is updated and saved to the database it is connected to.

Case in point, one of the team members I work with has SQL Server 2008 R2, and I’m running SQL Server 2005. When the model was updated on their machine, it set the ProviderManifestToken value to 2008, which included using the datetime2 data type on all date related queries it performed. The problem with this is that SQL Server 2005 doesn’t have this data type, and the generated queries would fail.

The solution? Simply change that value back to 2005 (as reported here) and recompile the app. You need to remember to do this every time you update the data model when working with SQL Server 2008. Apparently there’s no “permanent fix” for this.

It’s always the small oddities that make programming so interesting 8^D Enjoy!

8 thoughts on “Quick Tip: Fixing the datetime2 problem with Entity Framework

    1. I’ll admit I’m on my first major project using it, and before that I’ve just done some basic stuff with it. However, for not being polished, I’ve really been impressed with it. How about you?

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s