Why does EF5 code first use datetime2 when inserting a nullable datetime into the database?

by

The DateTime type in .NET has the same range and precision as datetime2 in SQL Server. When EF inserts or updates a datetime or datetime2 column in SQL Server it converts the model property to the type that can hold the whole range of DateTime in .NET, that’s datetime2. Converting into datetime would fail if the DateTime property is not inside the range of datetime in SQL Server.

The problem that causes the exception are, by the way, not the two nullable OpeningDateUtc and ClosingDateUtc columns, but the CreatedOnUtc value which is '0001-01-01 00:00:00' in your SQL snippet, i.e. CreatedOnUtc is apparently not initialized in your model entity. The earliest date that datetime in SQL Server can store is in the year 1750, so year 0001 won’t fit into the type (but it would fit into datetime2).

So, solution is to either set CreatedOnUtc to a valid datetime value or – as you know – define the types as datetime2 in your mapping.

But I agree, there would be less confusion if EF would map DateTime properties by default to datetime2.

More Related Contents:

  1. Code-first vs Model/Database-first [closed]
  2. Entity Framework: One Database, Multiple DbContexts. Is this a bad idea? [closed]
  3. Unique Key constraints for multiple columns in Entity Framework
  4. Entity Framework 6 Code first Default value
  5. EntityFramework code-first custom connection string and migrations
  6. EF Code First “Invalid column name ‘Discriminator'” but no inheritance
  7. Improve navigation property names when reverse engineering a database
  8. Should I enable or disable dynamic proxies with entity framework 4.1 and MVC3?
  9. How do I detach objects in Entity Framework Code First?
  10. EF5 Code First Enums and Lookup Tables
  11. Debug code-first Entity Framework migration codes
  12. Order navigation properties when using Include and/or Select methods with EF 4.1 Code-First?
  13. Entity framework code-first null foreign key
  14. Entity Framework loading child collection with sort order
  15. How do I singularize my tables in EF Code First?
  16. Set decimal(16, 3) for a column in Code First Approach in EF4.3 [duplicate]
  17. How to create index in Entity Framework 6.2 with code first
  18. DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID
  19. How do you ensure Cascade Delete is enabled on a table relationship in EF Code first?
  20. mapping private property entity framework code first [duplicate]
  21. What is the syntax for self referencing foreign keys in EF Code First?
  22. Entity Framework Code First : Setting up One-To-One foreign key association using Annotations
  23. Mocking or faking DbEntityEntry or creating a new DbEntityEntry
  24. How to use Entity Framework to map results of a stored procedure to entity with differently named parameters
  25. EF Code First foreign key without navigation property
  26. LINQ to Entities for subtracting 2 dates
  27. Entity Framework Code First Using Guid as Identity with another Identity Column
  28. LINQ To Entities + Include + Anonymous type issue
  29. Can anyone spot why I keep getting this error testing the EF 5 beta
  30. Entity framework code first delete with cascade

Leave a Comment