Why does EF5 code first use datetime2 when pasting a date with a null value into the database?

Question

I store the Cart object in a database with zero date-time. This is the error I get:

Converting the datetime2 data type to the datetime data type has exceeded the value.

There are quite a few stackoverflow posts fixing fixes to this problem. However, when the code first creates the database, it creates the field as a DateTime (allows null). But for some reason, the code first tries to insert into the DateTime2 field.

I am wondering why EF creates a field in one way, but inserts a different type for the same field.

This is a domain object:

using System; using System.Collections.Generic; namespace Core.Domain.Cart { public partial class Cart : BaseEntity, ILocalizedEntity { private ICollection<Catalog> _catalogs; /// <summary> /// Gets or sets the name /// </summary> public virtual string Name { get; set; } /// <summary> /// Gets or sets the zone identifier /// </summary> public virtual int ZoneId { get; set; } /// <summary> /// Gets or sets the brand identifier /// </summary> public virtual int BrandId { get; set; } /// <summary> /// Gets or sets the customer type identifier /// </summary> public virtual int CustomerTypeId { get; set; } /// <summary> /// Gets or sets the date and time of the opening of a cart /// </summary> public virtual DateTime? OpeningDateUtc { get; set; } /// <summary> /// Gets or sets the date and time of the closing of a cart /// </summary> public virtual DateTime? ClosingDateUtc { get; set; } /// <summary> /// Gets or sets a value indicating whether the entity is online or not /// </summary> public virtual bool IsOnline { get; set; } /* Truncated for relevance */ } } 

Model:

 using FluentValidation.Attributes; using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Web.Mvc; using Telerik.Web.Mvc; namespace Admin.Models.Cart { [Validator(typeof(CartValidator))] public partial class CartModel : BaseNopEntityModel, ILocalizedModel<CartLocalizedModel> { public CartModel() { Locales = new List<CartLocalizedModel>(); Catalogs = new List<CatalogModel>(); UnassociatedCatalogs = new List<CatalogModel>(); } [NopResourceDisplayName("Admin.Carts.Fields.Name")] [AllowHtml] public string Name { get; set; } //Zone dropdown [NopResourceDisplayName("Admin.Carts.Fields.ZoneList")] public SelectList ZoneList { get; set; } //The dropdown with zones public int ZoneId { get; set; } //The selected value of the dropdown once the form is submitted public string ZoneName { get; set; } //The name of the zone to display in data-grid List view. //Brand dropdown [NopResourceDisplayName("Admin.Carts.Fields.BrandList")] public SelectList BrandList { get; set; } //The dropdown with brands public int BrandId { get; set; } //The selected value of the dropdown once the form is submitted public string BrandName { get; set; } //The name of the brand to display in the data-grid List view. //Customer type dropdown [NopResourceDisplayName("Admin.Carts.Fields.CustomerTypeList")] public SelectList CustomerTypeList { get; set; }//The dropdown with CustomerType public int CustomerTypeId { get; set; } //The selected value of the dropdown once the form is submitted public string CustomerTypeName { get; set; } //The name of the CustomerType to display in the data-grid List view. [NopResourceDisplayName("Admin.Carts.Fields.OpeningDateUtc")] [UIHint("DateNullable")] public DateTime? OpeningDateUtc { get; set; } [NopResourceDisplayName("Admin.Carts.Fields.ClosingDateUtc")] [UIHint("DateNullable")] public DateTime? ClosingDateUtc { get; set; } [NopResourceDisplayName("Admin.Carts.Fields.IsOnline")] public bool IsOnline { get; set; } /* Truncated for relevance */ } } 

So both OpeningDateUtc and ClosingDateUtc are of type DateTime ?.

Here's how to first create a database using EF code:

OpeningDateUtc and ClosingDateUtc are created as a null DateTime field.

So why, when I save using IDBContext.SaveChanges() , the SQL generated for the query:

 exec sp_executesql N'update [dbo].[Cart] set [Name] = @0, [ZoneId] = @1, [BrandId] = @2, [CustomerTypeId] = @3, [OpeningDateUtc] = @4, [ClosingDateUtc] = @5, [IsOnline] = @6, [IsReadonly] = @7, [IsPreviewMode] = @8, [CreatedOnUtc] = @9 where ([Id] = @10) ',N'@0 nvarchar(100),@1 int,@2 int,@3 int,@4 datetime2(7),@5 datetime2(7),@6 bit,@7 bit,@8 bit,@9 datetime2(7),@10 int',@0=N'Cart1',@1=7,@2=4,@3=5,@4='2013-01-09 00:00:00',@5='2013-01-18 00:00:00',@6=0,@7=0,@8=1,@9='0001-01-01 00:00:00',@10=1 

The interesting part is @4 datetime2(7),@5 datetime2(7) .

I understand that I can fix this problem by adding .HasColumnType("datetime2") to the cart map, but does not answer why EF5 (and probably older versions) set them to NULL for datetime.

Answer 1

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 a type that can contain the entire DateTime range in .NET, which is datetime2 . Converting to DateTime will fail if the DateTime property is not within the DateTime range in SQL Server.

The problem that throws an exception is, by the way, not two columns with a zero value of OpeningDateUtc and ClosingDateUtc , but the value of CreatedOnUtc , which is '0001-01-01 00:00:00' in your SQL fragment, i.e. CreatedOnUtc does not seem to initialize in your model. The earliest date that SQL Server DateTime can store is in year 1750, so year 0001 will not fit into the type (but it will fit into datetime2 ).

Thus, the solution should either set CreatedOnUtc to a valid DateTime value, or, as you know, define types as datetime2 in your mapping.

But I agree, there would be less confusion if EF would display the DateTime properties by default on datetime2 .

Answer 2

The EF team actually discussed this specific item during one of the project meetings. The solution was to leave the current behavior as it is. Below are notes that may give you more context.