How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer

Question

I m using the DbContext and Code First APIs introduced with Entity Framework 4 1   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   public virtual DateTime Start   get  set      The DbContext subclass is also simple and looks like   public class EventsContext   DbContext       public DbSet lt Event gt  Events   get  set         protected override void OnModelCreating DbModelBuilder modelBuilder                modelBuilder Entity lt Event gt    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        The conversion of a datetime2 data   type to a datetime data type resulted   in an out-of-range value  The   statement has been terminated    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   Any ideas

User · Answer

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

User · Answer

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  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   This can be done by fluent API or data annotation    Fluent API  In DbContext class overide OnModelCreating and configure property Start  for  explanation reasons it s a property of EntityClass class    protected override void OnModelCreating DbModelBuilder modelBuilder          Configure only one property      modelBuilder Entity lt EntityClass gt             Property e   gt  e Start           HasColumnType  datetime2          or configure all DateTime Preperties globally EF 6 and Above      modelBuilder Properties lt DateTime gt             Configure c   gt  c HasColumnType  datetime2        Data annotation   Column TypeName  datetime2    public virtual DateTime Start   get  set       2nd Approach  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   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-  public virtual DateTime  Start   get  set      For more explanation read this post

User · Answer

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

User · Answer

initialize the Start property in the constructor  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   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

User · Answer

In my case  after some refactoring in EF6  my tests were failing with the same error message as the original poster but my solution had nothing to do with the DateTime fields    I was just missing a required field when creating the entity  Once I added the missing field  the error went away  My entity does have two DateTime  fields but they weren t the problem

User · Answer

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   modelBuilder Properties lt DateTime gt    Configure c   gt  c HasColumnType  datetime2       That will get all the DateTime and DateTime  properties on all your entities

User · Answer

Simple  On your code first  set the type of DateTime to DateTime   So you can work with nullable DateTime type in database  Entity example   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

User · Answer

One line fixes this   modelBuilder Properties lt DateTime gt    Configure c   gt  c HasColumnType  datetime2       So  in my code  I added   protected override void OnModelCreating DbModelBuilder modelBuilder        modelBuilder Properties lt DateTime gt    Configure c   gt  c HasColumnType  datetime2         Adding that one line to the DBContext subclass override void OnModelCreating section should work

User · Answer

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    HasColumnType  datetime2

User · Answer

In case anyone is as dopey as me  double check the year of your date   I was converting a date from a text file in YYMMDD format so was creating a date with a year of 0020  not 2020   Obvious error but I spent more time looking at it but not seeing it than I should have

User · Answer

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

User · Answer

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

User · Answer

In some cases  DateTime MinValue  or equivalenly  default DateTime   is used to indicate an unknown value    This simple extension method can help handle such situations   public static class DbDateHelper            lt summary gt          Replaces any date before 01 01 1753 with a Nullable of          DateTime with a value of null           lt  summary gt           lt param name  date  gt Date to check lt  param gt           lt returns gt Input date if valid in the DB  or Null if date is          too early to be DB compatible  lt  returns gt      public static DateTime  ToNullIfTooEarlyForDb this DateTime date                return  date  gt    DateTime  SqlDateTime MinValue    date    DateTime  null            Usage    DateTime  dateToPassOnToDb   tooEarlyDate ToNullIfTooEarlyForDb

User · Answer

Based on user  andygjp s answer  its better if you override the base Db SaveChanges   method and add a function to override any date which does not fall between SqlDateTime MinValue and SqlDateTime MaxValue   Here is the sample code  public class MyDb   DbContext       public override int SaveChanges                 UpdateDates            return base SaveChanges               private void UpdateDates                 foreach  var change in ChangeTracker Entries   Where x   gt   x State    EntityState Added    x State    EntityState Modified                          var values   change CurrentValues              foreach  var name in values PropertyNames                                var value   values name                   if  value is DateTime                                        var date    DateTime value                      if  date  lt  SqlDateTime MinValue Value                                                values name    SqlDateTime MinValue Value                                            else if  date  gt  SqlDateTime MaxValue Value                                                values name    SqlDateTime MaxValue Value                                                                            Taken from the user  sky-dev s comment on https   stackoverflow com a 11297294 9158120

User · Answer

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

[c#] How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer?

Examples related to c#

Examples related to .net

Examples related to entity-framework

Examples related to ef-code-first

Examples related to dbcontext