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!

mm
0 0 vote
Article Rating
Subscribe
Notify of
guest
10 Comments
Inline Feedbacks
View all comments
LUKMAN KUNLE

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

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

Riya

{
“message”: “An error occurred while updating the entries. See the inner exception for details.”,
“detail”: ” at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple2 parameters, CancellationToken cancellationToken)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) in C:\\projects\\npgsql-entityframeworkcore-postgresql\\src\\EFCore.PG\\Storage\\Internal\\NpgsqlExecutionStrategy.cs:line 49\r\n at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList1 entriesToSave, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)\r\n at SmartAssistant.LinkMe.Repository.RepositoryBase1.SaveAsync() in D:\\Projects\\ClientProjects\\Recruitment_Agency_Project\\SmartAssistance\\smartassistantsa-smartassistant.whosfit-5aad80240237\\server\\SmartAssistant.LinkMe.Repository\\RepositoryBase.cs:line 48\r\n at SmartAssistant.LinkMe.Repository.EmployerRepository.CreateEmployerAsync(EmployerDBM EmployerDBM) in D:\\Projects\\ClientProjects\\Recruitment_Agency_Project\\SmartAssistance\\smartassistantsa-smartassistant.whosfit-5aad80240237\\server\\SmartAssistant.LinkMe.Repository\\EmployerRepository.cs:line 45\r\n at SmartAssistant.LinkMe.Api.Controllers.EmployerController.CreateEmployee(EmployerDBM Employeer) in D:\\Projects\\ClientProjects\\Recruitment_Agency_Project\\SmartAssistance\\smartassistantsa-smartassistant.whosfit-5aad80240237\\server\\SmartAssistant.LinkMe.Api\\Controllers\\EmployerController.cs:line 69\r\n at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at System.Threading.Tasks.ValueTask1.get_Result()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()\r\n at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)\r\n at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)\r\n at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)”
}

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.

Katy

I don`t have SaveChanges(). If You let me please, I send my code to you.
So if you can help me, I appreciate it. I need this code for my first job and if I go through the company will mentor me.
Thank You

// GET: VehicleModel
public ActionResult Index(string sortOrder, string searchString, string currentFilter, int? page)
{
List vehicleModels = new List();
try
{
VehicleModelDbService vehicleModelDbService = new VehicleModelDbService();
vehicleModels = vehicleModelDbService.GetAll();
}
catch (Exception ex)
{
ViewBag.Message = ex.Message;
}

//here is the code for sorting, filtering and searching

return View(vehicleModels.ToPagedList(pageNumber, pageSize));
}

[HttpGet]
public ActionResult Create(int? id)
{
ViewBag.VehicleModelId = vehicleModelId;
return View(new VehicleModel());
}

[Http Post]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = “VehicleModelName, VehicleModelAbrv”)]VehicleModel vehicleModel)
{

if (ModelState.IsValid)
{
try
{

ViewBag.VehicleModelId = vehicleModelId;
ViewBag.Poruka = “Zapis uspješno dodan!”;

VehicleModelDbService vehicleModelDbService = new VehicleModelDbService();
vehicleModelDbService.Create(vehicleModel);

TempData[“Message”] = “Zapis uspješno dodan!”;
RedirectToAction(“Index”);
}
catch (Exception ex)
{

ViewBag.Message = ex.Message;
}
}
return View(vehicleModel);

//HERE IS VIEW FOR MODEL/INDEX

@model PagedList.IPagedList
@using PagedList.Mvc;

@{
ViewBag.Title = “Index”;
}

Index

@Html.ActionLink(“Create New”, “Create”)

@using (Html.BeginForm(“Index”, “VehicleModel”, FormMethod.Get))
{

Find by VehicleModelName: @Html.TextBox(“SearchString”, ViewBag.CurrentFilter as string)

}

@Html.ActionLink(“VehicleModelId”, “Index”, new { sortOrder = ViewBag.NameSortParm })

VehicleMakeName

VehicleModelName

VehicleModelAbrv

@foreach (var item in Model)
{

@Html.DisplayFor(modelItem => item.VehicleModelId)

@Html.DisplayFor(modelItem => item.VehicleMake.VehicleMakeName)

@Html.DisplayFor(modelItem => item.VehicleModelName)

@Html.DisplayFor(modelItem => item.VehicleModelAbrv)

@Html.ActionLink(“Edit”, “Edit”, new { id = item.VehicleModelId }) |
@Html.ActionLink(“Details”, “Details”, new { id = item.VehicleModelId }) |
@Html.ActionLink(“Delete”, “Delete”, new { id = item.VehicleModelId })

}

Page @(Model.PageCount Url.Action(“Index”, new { page, sortOrder = ViewBag.CurrentFilter = ViewBag.CurrentFilter }))

//HERE IS VIEW FOR MODEL/CREATE

Create

@using (Html.BeginForm())
{
@Html.AntiForgeryToken()

VehicleModel

@Html.ValidationSummary(true, “”, new { @class = “text-danger” })

@Html.LabelFor(model => model.VehicleModelName, htmlAttributes: new { @class = “control-label col-md-2” })

@Html.EditorFor(model => model.VehicleModelName, new { htmlAttributes = new { @class = “form-control” } })
@Html.ValidationMessageFor(model => model.VehicleModelName, “”, new { @class = “text-danger” })

@Html.LabelFor(model => model.VehicleModelAbrv, htmlAttributes: new { @class = “control-label col-md-2” })

@Html.EditorFor(model => model.VehicleModelAbrv, new { htmlAttributes = new { @class = “form-control” } })
@Html.ValidationMessageFor(model => model.VehicleModelAbrv, “”, new { @class = “text-danger” })