EF4 & SQL Server Date / DateTime Columns

When using EF4 against SQL Server 2008 (though this would apply to 2005 as well), I kept getting this error on insert:

System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

I had 2 date columns in the table I was attempting to insert into to begin with. One was just a LastDateModified column with a default of GetDate() so I was mentally filtering that column out and focusing on the other column. I changed that column from date to datetime which didn't fix the problem. I then changed that column to DateTime(2,7) and it still didn't fix it. Running a trace on the SQL that EF4 was attempting to insert revealed the problem. EF4 isn't smart enough to see that my LastDateModified column has a default and shouldn't bother sending a value when the programmer doesn't set one. It sent some huge min date that didn't fit into a datetime column.

Thus, the workaround is to either set any columns I won't be explicitly setting in code to type date(2,7) or to explicitly set the value of those columns in code to DateTime.Now. In any case, you can't use the SQL Server 2008 date column data type (which sucks).