EF Foreign key constraint preventing me from editing records in table

Question

I have a bit of a problem. I have a table with three relationships to one table:

 public class ResourceAction
{
    public int Id { get; set; }
    public int ResourceId { get; set; }
    public virtual Resource Resource{ get; set; } //main fk
    public virtual Resource ResourceBeingSent { get; set; } //reference
    public virtual Resource ResponseObject { get; set; } //reference
   ResourceAction()
   {
       ResourceBeingSent = new Resource();
       ResponseObject = new Resource();
   }
   }

Now the two references are optional, and I can create and delete just fine, by removing them before deleting the record. But I get a problem when trying to edit an existing item. I get a constraint error, I've removed the constraint on the two references by adding this to my db context:

       modelBuilder.Entity<ResourceAction>()
   .HasOptional(f => f.ResourceBeingSent)
   .WithMany()
   .WillCascadeOnDelete(false);

      modelBuilder.Entity<ResourceAction>()
    .HasOptional(f => f.ResponseObject)
   .WithMany()
   .WillCascadeOnDelete(false);

I'm at wits end here, I've tried just changing the state of the entity to modified and passing in the record, I've also tried passing in just the changed values I still get errors:

    db.Entry(originalResourceAction).CurrentValues.SetValues(vres);
            db.Entry(originalResourceAction).State =         EntityState.Modified;
            db.SaveChanges();

If anyone could give me pointers as to why I can create, delete but not edit.... That would be great!

I've included the stacktrace, essentially its saying that its constrained by the Foreign key (which is required) in the Resource table. Note The user cannot edit the (main) resource foreign key and this does not change during the update.

The EF schema to build the database is (It includes the fields I omitted for clarity).

      CreateTable(
            "dbo.ResourceActions",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false, maxLength: 100),
                    HttpMethods = c.Int(nullable: false),
                    ResourceId = c.Int(nullable: false),
                    Description = c.String(),
                    ObjectSentIsList = c.Boolean(nullable: false),
                    ObjectResponseIsList = c.Boolean(nullable: false),
                    RespondWithObject = c.Boolean(nullable: false),
                    FriendlyName = c.String(),
                    Deprecated = c.Boolean(nullable: false),
                    ResourceBeingSent_Id = c.Int(),
                    ResponseObject_Id = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Resources", t => t.ResourceId, cascadeDelete: true)
            .ForeignKey("dbo.Resources", t => t.ResourceBeingSent_Id)
            .ForeignKey("dbo.Resources", t => t.ResponseObject_Id)
            .Index(t => t.ResourceId)
            .Index(t => t.ResourceBeingSent_Id)
            .Index(t => t.ResponseObject_Id);

Edit added the whole actionResult

Note that before, I was just passing in the whole object, this is my latest attempt at just passing in the bits that the user can modify. (still get the same error).

  public ActionResult Edit(ResourceAction resourceAction)
    {
        if (ModelState.IsValid)
        {
            ResourceAction originalResourceAction = db.ResourceActions.Include(c => c.Resource).FirstOrDefault( c => c.Id == resourceAction.Id);

            ViewResourceAction vres = new ViewResourceAction();

            vres.Description = resourceAction.Description;
            vres.FriendlyName = resourceAction.FriendlyName;
            vres.HttpMethods = resourceAction.HttpMethods;
            vres.ObjectResponseIsList = resourceAction.ObjectResponseIsList;
            vres.Name = resourceAction.Name.Replace(" ", "_");
            vres.ObjectSentIsList = resourceAction.ObjectSentIsList;
            vres.RespondWithObject = resourceAction.RespondWithObject;

            if (resourceAction.ResourceBeingSent.Id > 0)
            {
                 vres.ResourceBeingSent =  db.Resources.Find(resourceAction.ResourceBeingSent.Id);
            }
            else
            {
                 vres.ResourceBeingSent =  null;
            }
            if (resourceAction.ResponseObject.Id > 0)
            {
                 vres.ResourceBeingSent =  db.Resources.Find(resourceAction.ResponseObject.Id);
            }
            else
            {
                 vres.ResourceBeingSent =  null;
            }

            db.Entry(originalResourceAction).CurrentValues.SetValues(vres);
            db.Entry(originalResourceAction).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Edit","Resources", new{id = resourceAction.ResourceId});
        }
        ViewBag.ResourceId = new SelectList(db.Resources, "Id", "Name", resourceAction.ResourceId);
        return View(resourceAction);

My error trace is:

  [SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Resources_dbo._Version_VersionId". The conflict occurred in database "APIEntities-01", table "dbo._Version", column 'Id'.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1767866
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5352418
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c) +9
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +356
   System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +166
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues) +234
   System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() +139

[UpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() +319
   System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut) +9
   System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update(T noChangesResult, Func`2 updateFunction) +120
   System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update() +77
   System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35() +11
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +288
   System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) +163
   System.Data.Entity.Core.Objects.<>c__DisplayClass2a.<SaveChangesInternal>b__27() +22
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +164
   System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) +222
   System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) +7
   System.Data.Entity.Internal.InternalContext.SaveChanges() +114

[DbUpdateException: An error occurred while updating the entries. See the inner exception for details.]
   System.Data.Entity.Internal.InternalContext.SaveChanges() +199
   System.Data.Entity.Internal.LazyInternalContext.SaveChanges() +27
   System.Data.Entity.DbContext.SaveChanges() +20
   RockHopper.Controllers.ResourceActionsController.Edit(ResourceAction resourceAction) in d:\API Manager\RockHopper\Controllers\ResourceActionsController.cs:177
   lambda_method(Closure , ControllerBase , Object[] ) +104
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +157
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22
   System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +29
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
   System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() +50
   System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +225
   System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
   System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +26
   System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
   System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +39
   System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +28
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +29
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +31
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9657896
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Answer 1

The venom is in the constructor:

ResourceAction()
{
   ResourceBeingSent = new Resource();
   ResponseObject = new Resource();
}

You initialize reference properties there, which means that the object will always start with a new Resource and the existing one is never loaded, let alone modified.

Remove these initializations. Add a new Resource in code to create the first new object. Load (Include) it and set its values if you want to modify it.

See also: EF codefirst : Should I initialize navigation properties?