This post was most recently updated on June 23rd, 2022.
4 min read.This post describes an issue with EF’s code-first migrations, when mapping between DB’s DateTime (datetime2) and C#’s DateTime simply fails, and results in the Update-Database cmdlet failing, too. It is more or less a prime example of a situation, where the error itself tells very little about the actual issue, and since debugging code-first migrations is kind of difficult (see the best tips for that here!), it’s cumbersome to investigate.
Symptoms
So what’s the error that we’re running into, exactly? Below, you can find the most typical, very non-descriptive version of this error:
An exception of type ‘System.Data.Entity.Infrastructure.DbUpdateException’ occurred in EntityFramework.dll but was not handled in user code.
I’ve encountered this error in 2 different situations. Either while inserting new entries into the database (in which case you might encounter the error above), or while running Update-Database in a code-first ASP.NET MVC5 + EF6 -project, you get the following (or similar) error:
An error occurred while updating the entries. See the inner exception for details.
This can be thrown by a plethora of different issues. This post describes the one that I have struggled with the most. The whole, pretty terrifying error message that actually contains important clues is as follows:
Update-Database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201602070905103_xx].
Applying explicit migration: 201602070905103_xx.
Running Seed method.
System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__0(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
--- End of inner exception stack trace ---
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.b__2(UpdateTranslator ut)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
at System.Data.Entity.Core.Objects.ObjectContext.b__35()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.b__27()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
--- End of inner exception stack trace ---
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at System.Data.Entity.DbContext.SaveChanges()
at System.Data.Entity.Migrations.DbMigrator.SeedDatabase()
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.SeedDatabase()
at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.b__b()
at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.< .ctor>b__0()
at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
An error occurred while updating the entries. See the inner exception for details.
This long and worrisome error, and the accompanying stack trace, deserve a closer look.
Problem
The best clue to the actual error lies in this row:
---> System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Wait a minute – what does that mean? How did I end up with a datetime2 being converted to…an out-of-range DateTime value? That’s a bit odd, right, and I did not ask for that?
I had specified an AddOrUpdate -call, in which no value for a DateTime-typed property was set in the model. My naíve assumption was, that this would set a non-required property to null even if I didn’t explicitly set it to nullable, but that was of course wrong.
C#’s DateTime -type in the Model, if not nullable, sets itself to a min value in the Seed method of the migration. This minimum value is actually {1/1/0001 12:00:00 AM}.
However, the SQL Server won’t have that. DateTime in SQL Server has a minimum value of 1/1/1753 12:00:00 AM – you can try this by trying to set a value lower than that in SQL Server Management Studio. You should get an error like this:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
So, the error above is just returned from the SQL server, details are kind of omitted, and it’s finally returned for Entity Framework like the error below:
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. the statement has been terminated.
Luckily, this is easy to fix!
Solution
Finally, the solution was simple.
Modifying my Model to use nullable DateTime -typed objects (Nullable? -type) solved the issue. So, in short, to resolve the error “The conversion of a datetime2 data type to a DateTime data type resulted in an out-of-range value”, change the DateTime properties in your Model to DateTime?.
Example:
// "Nullable DateTime", DateTime?, won't throw an error in the cast in EF
public DateTime? StartDate { get; set; }
And there you go!
If you would like to know more about different DateTime format errors with .NET (and probably Entity Framework), check out this article as well:
Did it help? Let me know!
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
