为什么在向数据库中插入可空的datetime时，EF5代码首先使用datetime2？ [英] Why does EF5 code first use datetime2 when inserting a nullable datetime into the database?
问题描述
我将一个Cart对象保存到具有可空的datetime的数据库中。这是我得到的错误：
将datetime2数据类型转换为datetime数据类型
导致out-范围值。
有很多stackoverflow文章记录了这个问题的修复。但是，当代码首先创建数据库时，它将创建一个DateTime（允许空值）的字段。但是由于某些原因，代码首先尝试使用DateTime2字段插入。
我想知道为什么EF以一种方式创建字段，但是插入使用不同的类型
这是域对象：
using System;
使用System.Collections.Generic;
命名空间Core.Domain.Cart
{
public partial class Cart：BaseEntity，ILocalizedEntity
{
private ICollection< Catalog> _catalogs;
///< summary>
///获取或设置名称
///< / summary>
public virtual string Name {get;组; }
///< summary>
///获取或设置区域标识符
///< / summary>
public virtual int ZoneId {get;组; }
///< summary>
///获取或设置品牌标识符
///< / summary>
public virtual int BrandId {get;组; }
///< summary>
///获取或设置客户类型标识符
///< / summary>
public virtual int CustomerTypeId {get;组; }
///< summary>
///获取或设置购物车开始的日期和时间
///< / summary>
public virtual DateTime？ OpeningDateUtc {get;组; }
///< summary>
///获取或设置购物车关闭的日期和时间
///< / summary>
public virtual DateTime？ ClosingDateUtc {get;组; }
///< summary>
///获取或设置一个值，表示该实体是否在线
///< / summary>
public virtual bool IsOnline {get;组;
/ *截断相关性* /
}
}
模型：
使用FluentValidation.Attributes;
使用系统;
使用System.Collections.Generic;
使用System.ComponentModel.DataAnnotations;
使用System.Web.Mvc;
使用Telerik.Web.Mvc;
命名空间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;组;
//区域下拉菜单
[NopResourceDisplayName（Admin.Carts.Fields.ZoneList）]
public SelectList ZoneList {get;组; } //带有区域的下拉列表
public int ZoneId {get;组; } //提交表单后，下拉列表的选定值
public string ZoneName {get;组; } //要在数据网格列表视图中显示的区域的名称。
//品牌下拉
[NopResourceDisplayName（Admin.Carts.Fields.BrandList）]
public SelectList BrandList {get;组; } //品牌的下拉菜单
public int BrandId {get;组; } //提交表单后，下拉列表的选定值
public string BrandName {get;组; } //要在数据网格列表视图中显示的品牌的名称。
//客户类型下拉列表
[NopResourceDisplayName（Admin.Carts.Fields.CustomerTypeList）]
public SelectList CustomerTypeList {get;组; } //使用CustomerType
的下拉菜单public int CustomerTypeId {get;组; } //提交表单后，下拉列表的选定值
public string CustomerTypeName {get;组; } //要在数据网格列表视图中显示的CustomerType的名称。
[NopResourceDisplayName（Admin.Carts.Fields.OpeningDateUtc）]
[UIHint（DateNullable）]
public DateTime？ OpeningDateUtc {get;组; }
[NopResourceDisplayName（Admin.Carts.Fields.ClosingDateUtc）]
[UIHint（DateNullable）]
public DateTime？ ClosingDateUtc {get;组; }
[NopResourceDisplayName（Admin.Carts.Fields.IsOnline）]
public bool IsOnline {get;组;
/ *截断相关性* /
}
}
$ b所以$ code> OpeningDateUtc 和 ClosingDateUtc 的类型是DateTime？ / p>
这是数据库首先通过EF代码生成的方式：
OpeningDateUtc 和 ClosingDateUtc 被创建为一个可空的DateTime字段。
所以为什么当我使用 IDBContext.SaveChanges（），为查询生成的SQL是：
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
其中（[Id] = @ 10）
'，N'@ 0 nvarchar（100） ，@ 1 int，@ 2 int，@ 3 int，@ 4 datetime2（7），@ 5 datetime2（ 7），@ 6位，@ 7位，@ 8位，@ 9 datetime2（7），@ 10 int'，@ 0 = N'Cart1'，@ 1 = 7，@ 2 = 4，@ 3 = @ 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
有趣的部分是 @ 4 datetime2（7），@ 5 datetime2（7）。
我明白我可以解决这个问题通过在购物车地图中添加一个 .HasColumnType（datetime2），但它并没有回答为什么EF5（可能是较旧的版本）将其设置为可空的datetime。 p>
.NET中的 DateTime 类型的范围和精度与 datetime2 在SQL Server中。当EF插入或更新SQL Server中的 datetime 或 datetime2 列时，将模型属性转换为可以保存的类型.NET的整个范围 DateTime ，这是 datetime2 。如果 DateTime 属性不在 datetime的范围内，转换为 datetime 将失败code>在SQL Server。
导致异常的问题，顺便说一句，不是两个可空的 OpeningDateUtc 和 ClosingDateUtc 列，但是 CreatedOnUtc 值'0001-01 -01 00:00:00'在您的SQL代码段中，即 CreatedOnUtc 显然未在您的模型实体中初始化。 SQL Server可以存储的 datetime 的最早日期是1750年，因此，0001年将不适合该类型（但它将适合 datetime2 ）。
所以，解决方案是将 CreatedOnUtc 设置为有效$ / code> datetime 的值或者你知道的 - 在你的映射中将类型定义为 datetime2 。
但是我同意，如果EF将默认映射到$ code> datetime2 。
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.
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.
这篇关于为什么在向数据库中插入可空的datetime时，EF5代码首先使用datetime2？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
