The Conversion of a datetime2 Data Type to a datetime Data Type Resulted In an Out of Range Value

This particular error was produced when using Entity Framework where the edmx is generated from a SQL Server 2008 database.  This particular issue was rather nerve racking giving that I was providing the property of the Entity that a DateTime.Now value.  Which works perfectly in other areas of the application, as expected.

Background:

There was two tables in question, TableOne has a non-nullable foreign key constraint to the primary key field of TableTwo.  This is highly common and not something that is an issue, obviously the record in TableTwo had already been created and the ID was being supplied to the TableOnes entity.  So when attempting to perform db.TableOnes.AddObject(tableone) and calling the db.SaveChanges() produced the following error "The Conversion of a datetime2 data type to a datetime datetype resulted in an out of range value." 

Diagnosis:

I verified through stepping through the code and inspecting the entity that the value being supplied for the only datetime property of the tableone entity was a valid datetime.  I attempted dropping the TableOne entity from the edmx and re-adding it.  Only to still produce the same error.  Given that the inner exception message was being provided from SQL Server, I decided to inspect the sql statement that entity framework was passing to the database since, the emdx has no datetime2 mappings for any of the tables to entities. 

Side Note:
To inspect the sql statements use SQL Profiler - a good tutorial and information is provided at http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-to-sql-server-2008-profiler-2/

Upon executing the same action that produced the errors, I found that it was actually attempting to execute an insert statement for TableTwo instead of insert for TableOne.  Obviously, this means that there was an issue with the edmx file, and something is mapping correctly.

I inspected the mappings for both TableOne and TableTwo and did not see any incorrect mappings either in the designer view or via opening the edmx as an xml file.

Resolution/Workaround:

As a last ditch effort since the deletion and re-addition of the TableOne to the edmx did not resolve the issue and the sql statement was trying to execute against TableTwo, I decided to try deleting and re-adding TableTwo.  This caused any mappings to get regenerated which solved the issue and everything saved properly with no conversion errors of datetime2 to datetime.

*** All Content is provided As Is ***