Entity Framework's Update-Database throwing an error

Fixing “An error occurred while updating the entries” while running code-first migrations in MVC 5 app

This post was most recently updated on April 8th, 2019.

Reading Time: 5 minutes.

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’s 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

Table of Contents

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 a 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 I’ve struggled the most with. The whole, pretty terrifying error message that actually contains important clues is the following:

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 in the behind is 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… What, 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 want 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!

Antti K. Koskela

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet. Working with the global partner network, he's responsible for the success of Valo deployments happening all around the world.

He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff.

This is his personal professional (e.g. professional, but definitely personal) blog.
mm

10
Leave a Reply

avatar
5000
3 Comment threads
7 Thread replies
4 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
KatyAntti K. KoskelaRiyaLUKMAN KUNLE Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
LUKMAN KUNLE
Guest
LUKMAN KUNLE

you just save my day. Been on this for hours now.

Riya
Guest
Riya

Hello i am having the same problem while posting the data through EF web-api an my problem is not solved by the above solution can u please help me

Katy
Guest
Katy

I have similar problem. “An error occurred while updating the entries. Check your inner exception for details.” I said similar because I don’t have DateTime property in my two models. In first model everything is functional but, In second model, when I start my web app from create view it’s opened but when I want create new entry and click on button create, then appeared to me that error I wrote. My code is in c# and I use EF and code first migration.