A common problem posted all over the web is in regards to DateTime2 errors thrown by Entity Framework.  Those problems are typically associated with the system generating invalid column and data types that are not supported by the older versions of MSSQL.  Fixing those problems is typically pretty easy and requires an update to the EDMX XML to specify the right database version to target.

My issue however is that I actually want to use DateTime2 as the column data type.  There are many reasons why you may want to do this and the benefits outweigh any risks associated with it.  The problem is however that there is no DateTime2 data type in .NET, only DateTime so when the T-SQL code is generated for the database (in model first mode) it will utilize the default DateTime column type and not DateTime2.

The solution below will change all DateTime data types in your model to utilize DateTime2 SQL storage types.  You cannot pick and choose easily which tables should use DateTime2 versus the default DateTime.  The entire solution is based around modifying the default T4 template for your project.

1. Browse to: %ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen
2. Copy the SSDLToSQL10.tt file to your project directory
3. Browse to %ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes
4. Copy the GenerateTSQL.Utility.ttinclude file to your project directory.

Note: I like to keep my template files in a different folder from my models and a sub-directory in the templates folder to store the include files.

1. Edit the SSDLToSQL10.tt file
2. Update the T4 file with the following code: http://codepaste.net/rchy2s
3. Change the template filename in the EDMX Designer:

Now all you have to do is re-generate the model and the DateTime2 type should be used.

-Brent