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

Question

I am saving a Cart object to the database that has a nullable datetime. This is the error I get:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

There are quite a few stackoverflow posts documenting fixes to this problem. However, when code first is creating the database it will create the field as a DateTime (allow nulls). But for some reason, code first tries to insert using a DateTime2 field.

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

This is the 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 */
    }    
}

The 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 the OpeningDateUtc and the ClosingDateUtc are of the type DateTime?.

This is how the database gets generated by EF code first:

The OpeningDateUtc and ClosingDateUtc are created as a nullable DateTime field.

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

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 being @4 datetime2(7),@5 datetime2(7).

I understand that I could fix this problem by adding a .HasColumnType("datetime2") to the cart map, but it doesn't answer why EF5 (and probably older versions) set them to nullable 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 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.

Answer 2

The EF Team actually discussed this particular item during one of the design meetings. The decision was to leave the current behavior as is. Here are the meeting notes that can give you more context.