CODEWITHSUNDEEP
.netentity-framework-4.1code-firstdatetime2
UniqueMan
UniqueMan

Reputation: 56

How to work around EF 4.1, CodeFirst C# DateTime datetime2 vs. datetime incompatibility

My Code First models have System.DateTime properties. When the database seeding code is invoked, it throws this exception:

SqlException (0x80131904): The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

I am creating a new DateTime object with the constructor. Code First is creating the database schema. How do I overcome this apparent bug in Code First? I am not particular about my datatype. I merely need to store a date, and optionally a time of day, but not crucial.

I have searched and read a lot of posts, but none with this error specify they come from CodeFirst-generated data scheme. The closest answer I found involved making a change to the ProviderManifestToken in the edmx file, but my project does not have an edmx file. I did not use the designer, I am using Code First.

Upvotes: 4

Views: 4159

Answers (2)

UniqueMan
UniqueMan

Reputation: 56

Sorry I found out after more careful debugging that the error was coming from a data entity initialization that involved me not specifying any value for the DateTime.

I had thought it was coming from a long data seeding statement that involved new DateTime(2012, 2, 19, 19, 0, 0) or some such. Thanks to Slauma for his efforts - he was correct that it was due to unitialized values.

Upvotes: 0

Slauma
Slauma

Reputation: 177163

EF maps DateTime in .NET to datetime in SQL Server. The types have a different range: datetime can store only dates later than around the year 1750. If you try to store a DateTime in your entity with an earlier date or an unitialized DateTime (which has year 1) you get the exception because SQL Server cannot store this.

Solution:

  • Either make sure that the dates you want to store are later than 1750
  • Or map you DateTime properties explitely to datetime2 in SQL Server which has a wider range. Example how to define this mapping with Fluent API: https://stackoverflow.com/a/8044310/270591

Actually I also had expected a default mapping to datetime2 because it fits better to DateTime in .NET. But for some reason they decided to use datetime as default.

Upvotes: 3

Related Questions