如何使用DbContext和SetInitializer修复datetime2的范围外转换错误?

I'm using the DbContext and Code First APIs introduced with Entity Framework 4.1.

我使用的是实体框架4.1中引入的DbContext和代码优先api。

The data model uses basic data types such as string and DateTime. The only data annotation I'm using in some cases is [Required], but that's not on any of the DateTime properties. Example:

数据模型使用基本的数据类型，如string和DateTime。我在某些情况下使用的唯一数据注释是[Required]，但这不在任何DateTime属性上。例子:

public virtual DateTime Start { get; set; }

The DbContext subclass is also simple and looks like:

DbContext子类也很简单，如下所示:

public class EventsContext : DbContext
{
    public DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>().ToTable("Events");
    }
}

The initializer sets dates in the model to sensible values in either this year or next year.

初始化器将模型中的日期设置为今年或明年的合理值。

However when I run the initializer, I get this error at context.SaveChanges():

但是当我运行初始化器时，我在context.SaveChanges()上得到了这个错误:

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

将datetime2数据类型转换为datetime数据类型会导致超出范围的值。声明已被终止。

I don't understand why this is happening at all because everything is so simple. I'm also not sure how to fix it since there is no edmx file to edit.

我不明白为什么会这样，因为一切都很简单。我也不确定如何修复它，因为没有要编辑的edmx文件。

Any ideas?

什么好主意吗?

11 个解决方案

#1

165

You have to ensure that Start is greater than or equal to SqlDateTime.MinValue (January 1, 1753) - by default Start equals DateTime.MinValue (January 1, 0001).

您必须确保Start大于或等于SqlDateTime。MinValue(1753年1月1日)-默认情况下Start等于DateTime。MinValue(0001年1月1日)。

#2

Simple. On your code first, set the type of DateTime to DateTime?. So you can work with nullable DateTime type in database. Entity example:

简单。首先在代码上，将DateTime类型设置为DateTime?因此可以在数据库中使用nullable DateTime类型。实体的例子:

public class Alarme
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public DateTime? DataDisparado { get; set; }//.This allow you to work with nullable datetime in database.
        public DateTime? DataResolvido { get; set; }//.This allow you to work with nullable datetime in database.
        public long Latencia { get; set; }

        public bool Resolvido { get; set; }

        public int SensorId { get; set; }
        [ForeignKey("SensorId")]
        public virtual Sensor Sensor { get; set; }
    }

#3

In some cases, DateTime.MinValue (or equivalenly, default(DateTime)) is used to indicate an unknown value. This simple extension method should help handle the problem:

在某些情况下,DateTime。MinValue(或等效的默认值(DateTime))用于表示一个未知值。这个简单的扩展方法应该有助于解决这个问题:

public static class DbDateHelper
{
    /// <summary>
    /// Replaces any date before 01.01.1753 with a Nullable of 
    /// DateTime with a value of null.
    /// </summary>
    /// <param name="date">Date to check</param>
    /// <returns>Input date if valid in the DB, or Null if date is 
    /// too early to be DB compatible.</returns>
    public static DateTime? ToNullIfTooEarlyForDb(this DateTime date)
    {
        return (date >= (DateTime) SqlDateTime.MinValue) ? date : (DateTime?)null;
    }
}

Usage:

用法:

 DateTime? dateToPassOnToDb = tooEarlyDate.ToNullIfTooEarlyForDb();

#4

You can make the field nullable, if that suits your specific modeling concerns. A null date won't be coerced to a date that isn't within the range of the SQL DateTime type the way a default value would. Another option is to explicitly map to a different type, perhaps with,

如果该字段适合特定的建模关注点，则可以使其为nullable。一个null日期不会被强制到一个不在SQL DateTime类型范围内的日期，这是默认值的方式。另一种选择是显式地映射到另一种类型，

.HasColumnType("datetime2")

#5

Even though this question is quite old and there are great answers already, I thought I should put one more which explains 3 different approaches to solve this problem.

虽然这个问题已经很老了，而且已经有了很好的答案，我想我应该再提出一个来解释解决这个问题的三种不同的方法。

1st Approach

1号的方法

Explicitly map DateTime property public virtual DateTime Start { get; set; } to datetime2 in corresponding column in the table. Because by default EF will map it to datetime.

显式映射DateTime属性public virtual DateTime Start {get;设置;}到表中相应列中的datetime2。因为默认情况下EF会将它映射到datetime。

This can be done by fluent API or data annotation.

这可以通过fluent API或数据注释来实现。

Fluent API

流利的API

In DbContext class overide OnModelCreating and configure property Start (for explanation reasons it's a property of EntityClass class).

在DbContext类中，overide onmodelcreate和configure属性Start(出于解释原因，它是EntityClass类的属性)。

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //Configure only one property 
    modelBuilder.Entity<EntityClass>()
        .Property(e => e.Start)
        .HasColumnType("datetime2");

   //or configure all DateTime Preperties globally(EF 6 and Above)
    modelBuilder.Properties<DateTime>()
        .Configure(c => c.HasColumnType("datetime2"));
}

Data annotation

数据注释

[Column(TypeName="datetime2")]
public virtual DateTime Start { get; set; }

2nd Approach

2方法

Initialize Start to a default value in EntityClass constructor.This is good as if for some reason the value of Start is not set before saving the entity into the database start will always have a default value. Make sure default value is greater than or equal to SqlDateTime.MinValue ( from January 1, 1753 to December 31, 9999)

在EntityClass构造函数中初始化一个默认值。这很好，因为某种原因，在将实体保存到数据库Start之前，Start的值没有设置，而这个值总是默认值。确保默认值大于或等于SqlDateTime。MinValue(从1753年1月1日至12月31日，9999)

public class EntityClass
{
    public EntityClass()
    {
        Start= DateTime.Now;
    }
    public DateTime Start{ get; set; }
}

3rd Approach

第三个方法

Make Start to be of type nullable DateTime -note ? after DateTime-

使Start的类型为nullable DateTime -note ?DateTime,后

public virtual DateTime? Start { get; set; }

For more explanation read this post

更多的解释请阅读这篇文章

#6

My solution was to switch all datetime columns to datetime2, and use datetime2 for any new columns. In other words make EF use datetime2 by default. Add this to the OnModelCreating method on your context:

我的解决方案是将所有datetime列转换为datetime2，并对任何新列使用datetime2。换句话说，EF默认使用datetime2。将此添加到上下文上的onmodelcreation方法:

modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));

That will get all the DateTime and DateTime? properties on all your entities.

会得到所有的DateTime和DateTime吗?所有实体上的属性。

#7

If your DateTime properties are nullable in the database then be sure to use DateTime? for the associated object properties or EF will pass in DateTime.MinValue for unassigned values which is outside of the range of what the SQL datetime type can handle.

如果您的DateTime属性在数据库中是空的，那么一定要使用DateTime吗?对于关联的对象属性或EF将传入DateTime。MinValue表示未赋值，该值超出SQL datetime类型可以处理的范围。

#8

initialize the Start property in the constructor

在构造函数中初始化Start属性

Start = DateTime.Now;

This worked for me when I was trying to add few new fields to the ASP .Net Identity Framework's Users table (AspNetUsers) using Code First. I updated the Class - ApplicationUser in IdentityModels.cs and I added a field lastLogin of type DateTime.

当我尝试在ASP . net身份框架的用户表(AspNetUsers)中首先使用代码时，这对我起了作用。我在标识模型中更新了类ApplicationUser。cs和我添加了DateTime类型的字段lastLogin。

public class ApplicationUser : IdentityUser
    {
        public ApplicationUser()
        {
            CreatedOn = DateTime.Now;
            LastPassUpdate = DateTime.Now;
            LastLogin = DateTime.Now;
        }
        public String FirstName { get; set; }
        public String MiddleName { get; set; }
        public String LastName { get; set; }
        public String EmailId { get; set; }
        public String ContactNo { get; set; }
        public String HintQuestion { get; set; }
        public String HintAnswer { get; set; }
        public Boolean IsUserActive { get; set; }

        //Auditing Fields
        public DateTime CreatedOn { get; set; }
        public DateTime LastPassUpdate { get; set; }
        public DateTime LastLogin { get; set; }
    }

#9

I had the same issue and in my case I was setting the date to new DateTime() instead of DateTime.Now

我遇到了同样的问题，在我的例子中，我将日期设置为new DateTime()，而不是DateTime. now

#10

In my case this happened when I used entity and the sql table has default value of datetime == getdate(). so what I did to set a value to this field.

在我的例子中，当我使用entity时，sql表的默认值为datetime = getdate()。所以我为这个字段设置了一个值。

#11

I'm using Database First and when this error happened to me my solution was to force ProviderManifestToken="2005" in edmx file (making the models compatible with SQL Server 2005). Don't know if something similar is possible for Code First.

我首先使用数据库，当这个错误发生时，我的解决方案是在edmx文件中强制ProviderManifestToken="2005"(使模型与SQL Server 2005兼容)。不知道代码是否有类似的可能。

#1

165