'Datetime2' error when using entity framework in VS 2010.net 4.0

Question

Getting this error:

System.Data.SqlClient.SqlException: Converting the datetime2 data type to the datetime data type resulted in a value out of range.

My entity is all consistent with DB objects.

I found only one link to this error via Google:

Google result

After reading this, I remember that we added 2 fields and then updated the entity model from VS 2010. I'm not sure what it means by “manual encoding” of the differences. I do not see.

All I do in the code is filling in the entity object and then saving. (I also fill in new fields in the code). I filled in the DateTime.Now date field ..

The important part of the code is: ctx.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

The database is SQL Server 2008.

Thoughts?

The rest of the error:

in System.Data.Mapping.Update.Internal.UpdateTranslator.Update (IEntityStateManager stateManager, adapter IEntityAdapter) in System.Data.EntityClient.EntityAdapter.Update (IEntityStateManager entityCache) in System.Data.Objects.ObjectCestext.SaveTave.Sontext SafariAdmin.Site.WebServices.SpeciesPost.SaveOrUpdateSpecies (String sid, String fieldName, String authToken) in SpeciesPost.svc.cs: line 58 in SafariAdmin.TestHarness.Tests.Site.WebServices.SpeciesPostSVC_Tester.SaveNewCTests () 33 --SqlException in System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection) in System.Data.SqlClient.SqlInternalConnection.OnError (SqlException exception, logical breakConnection) in System.Data.SqlClndTh.ndrntntrnterning in System.Data.SqlClient.TdsParser.Run (RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCop ySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) in the System.Data.SqlClient.SqlDataReader.ConsumeMetaData () in the System.Data.SqlClient.SqlDataReader.get_MetaData (SQL) SQL Server in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) in System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehaviorhbehbhunebhbhopehbehdbhopehbhdbhdhdbhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh onh on System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) in System.Data.SqlClient.SqlCommand.ExecuteReader (CommandBehavior behavior, String method) in System.Data.Slqbclient.qclclient CommandBehavior behavior) in System.Data.Common.Db Command.ExecuteReader (CommandBehavior behavior) in System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute (UpdateTranslator translator, EntityConnection connection, dictionary 2 identifierValues, List 1 generatedValues) in System.Data.Mapping.Update.Internal.Update.Update (IEntityStateManager stateManager, IEntityAdapter adapter)

Answer 1

Entity framework treats all dates as Datetime2, so if your fields in the database are Datetime, this can be a problem. We had the same problem, and from what we found, filling all the date fields and changing the data type are the most common solutions

Answer 2

If you are using Code First, should you declare any optional DateTime property as DateTime? or Nullable<DateTime> . Removing DateTime objects can cause problems.

If the property is null in the database and the default DateTime in the code (not DateTime? ), ADO.NET will send an insert command with the date 0001-01-01 (not NULL ), but the minimum SQL DateTime is 1753-01-01, which causes a mistake. If your DateTime property in your code is NULL (for example, DateTime? Or Nullable<DateTime> ), the insert command will try to insert NULL instead of a date out of range.

Answer 3

Use this SQL script to convert all columns from datetime to datetime2 . It skips all tables containing "aspnet" for your convenience.

 DECLARE @SQL AS NVARCHAR(1024) DECLARE @TBL AS NVARCHAR(255) DECLARE @COL AS NVARCHAR(255) DECLARE @NUL AS BIT DECLARE CUR CURSOR FAST_FORWARD FOR SELECT SCHEMA_NAME(t.schema_id)+'.'+t.name, c.name, c.is_nullable FROM sys.tables AS t JOIN sys.columns c ON t.object_id = c.object_id JOIN information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name WHERE i.data_type = 'datetime' and t.name not like '%aspnet%' ORDER BY t.name, c.name OPEN CUR FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = 'ALTER TABLE ' + @TBL + ' ALTER COLUMN [' + @COL + '] datetime2' + (CASE WHEN @NUL=1 THEN '' ELSE ' NOT' END) + ' NULL;' EXEC sp_executesql @SQL FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL END CLOSE CUR; DEALLOCATE CUR; 

It works for me!

Answer 4

Another possible solution is to set the column type of the sql field to datetime2. this can be done with fluentapi.

 Property(x => x.TheDateTimeField) .HasColumnType("datetime2"); 

Note. This solution is up for SQL Server 2008, because datetime2 is not available for SQL Server 2005 or lower.

Answer 5

I had the same problem and solved it by placing the [Column(TypeName = "datetime2")] attribute in the appropriate properties, for example, below the sample:

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

Answer 6

I know this is an old question, but since I was looking here, someone else could do the same ;-) For those that change from DateTime to DateTime2, is not an option (as for SQL2005 users), I think which in most cases is more reasonable to fill in the fields left empty with something like (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue
and not with DateTime.now, since it is easier to recognize it as a "pseudo-zero" value (and if you need to convert it to real null in the partial class of the parent object)

Answer 7

When using Entity structure code for the first time, declare it as follows:

 public Nullable<System.DateTime> LastLogin { get; set; } 

Answer 8

Is there a ModifiedTime property in your entity that is updated only on the database side? If so, you should use DatabaseGeneratedOption.Computed (for EF CodeFirst). Also visit https://stackoverflow.com>

Thank.

Answer 9

We had the same problem. This was due to the mssql version. We did this on all of our versions using this method.

Open the edmx file with an xml editor. Find this line at the top of the file

 <Schema Namespace="XXXXX.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" 

Replace 2008 with 2005. Save the file, recompile the project.

Hope this helps someone else in futur.

I just tried this solution with dbfirst approche.

Answer 10

Everything that fits in datetime will match the datetime2 data type, on the contrary, it’s not, you can stick to the January 1500 date in the datetime2 data type, but datetime only returns to 1753, the datetime2 column can go back to the path to year 1. I would check what min date you are passing and if your tables have columns of datetime2 or datetime data type

Answer 11

After trying to solve this problem for several days, did I use DateTime? as the data type in my model with Entity Framework Code-First instead of DateTime .

Answer 12

Make sure that none of the nonzero fields in the database (datetime) is specified when inserting / updating. I had the same error and when pasting values ​​into those datetime fields the problem was solved. This happens if the non-empty datetime fields are not assigned the correct value.

Answer 13

I had the same problem in my ASP.Net MVC application.

In my application, there were two classes of models that had DateTime properties.

when researching, I noticed that the DateTime property for one model is NULL, i.e. in order to assign it the date of the optional property Birth

another model had a DateTime property with mandatory data annotation (an error occurs when saving this model)

my application is code first, so I solved the problem by setting the data type as DateTime2

 [Column(TypeName="datetime2")] 

then I migrated in the package manager console.

Answer 14

Simple In the code, first specify the DateTime type for DateTime ?. This way you can work with a null DateTime type in the database.

Answer 15

This follows from stepanZ's answer ... I got this error when using Entity Framework Code First with AutoMapper .

When configuring AutoMapping we have the createddt , updateddt , createdby and updatedby fields that are automatically set in our public override int SaveChanges() function. In doing so, you need to ensure that these fields are ignored using AutoMapper , otherwise the database will be updated with null for these fields if they are not sent from View .

My problem was that I set the source and destination incorrectly, so I try to ignore the fields when installing ViewModel , and not when installing Model .

Mapping looked like this when I got this error (note: cfg.CreateMap<Source, Destination>() on the second line displays Model on ViewModel and sets Ignore() )

 cfg.CreateMap<EventViewModel, Event>(); cfg.CreateMap<Event, EventViewModel>() .ForMember(dest => dest.CreatedBy, opt => opt.Ignore()) .ForMember(dest => dest.CreatedDt, opt => opt.Ignore()) .ForMember(dest => dest.UpdatedBy, opt => opt.Ignore()) .ForMember(dest => dest.UpdatedDt, opt => opt.Ignore()); 

The source and destination should be ignored for display from ViewModel To Model (note: the code below is correct where Ignore() placed against the display for ViewModel in Model )

 cfg.CreateMap<Event, EventViewModel>(); cfg.CreateMap<EventViewModel, Event>() .ForMember(dest => dest.CreatedBy, opt => opt.Ignore()) .ForMember(dest => dest.CreatedDt, opt => opt.Ignore()) .ForMember(dest => dest.UpdatedBy, opt => opt.Ignore()) .ForMember(dest => dest.UpdatedDt, opt => opt.Ignore()); 

Answer 16

Two solutions:

• Declare properties in your class, as shown below, and create an update database:

   public DateTime? MyDate {get; set;} 

• Or set a date for the property in the Seed method that populates the database; there is no need for an update database:

   context.MyClass.AddOrUpdate(y => y.MyName,new MyClass { MyName = "Random", MyDate= DateTime.Now })