"Unable to insert or update an entity" error while deleting/re-adding parent-child row sets with EF4 RRS feed

  • Question

  • I'm writing a .Net 4 WPF program to deploy Workflows from one database to the next.  I decided to try making this my first effort with the Entity Framework.  Most of it has gone OK, but I'm having problems deleting and re-adding essentially the same parent/child hierarchy rows in the target database within the same transation.   I would appreciate some advice.

    The database has a Workflow table.   The Workflow table has a child Assembly table with a row for each Assembly (typically Workflow Activity Library assemblies) required by each Workflow.   Each Workflow row has a GUID datafield as a primary key and each associated Assembly row contains that parent GUID.   There is SQL 2008 R2 foreign-key relationship between the two tables based on that GUID (called here "FKey_Assembly_Workflow").

    If a given Workflow is changed in any way between the source database and the target, I need to delete the Workflow row and its associated assembly rows entirely out of the target database; then copy in the revised source database versions while maintaining the source GUID PK.   The Deletes/re-Adds all must be done within the same transaction.   So all required updates are called from within a "using ... new TransactionScope()" block.  The program's code is using the same source database entity context and the same target database entity context throughout.

    My problem is that if I delete the target database's context's Workflow row and assembly rows; then try to add back the revised content from the source context without first applying all the changes from the deletes, I get the following error with I do a "TargetContext.SaveChanges()" of any kind at the end of the "using ... Transaction Scope" block which is wrapping the Delete/Re-Add operations:

    "Unable to insert or update an entity because the principal end of the "WFDBModel.FKey_Assembly_Workflow" relationship is deleted.

    I originally thought this might be related to the way I copied/cloned the source entity objects (I need them to remain in the source context for later operations, so I'm creating new Workflow and Assembly objects rather than detaching them from the source context and attaching them to the target context).   I've tried cloning the source row objects via reflection, via using dynamic IL, etc.   But I get this same error even if I explicitly manually create new replacement objects in the target context and explicitly assign all the member data fields, which I would think means there should be no low-level hidden property containing relationship information left over for the source that is causing the issue.

    After getting the error with no intervening SaveChanges() or "Accept..." calls between Delete and re-Add operations, I've since tried doing an additional TargetContext.SaveChanges(SaveOptions.DetectChangesBeforeSave) between the Delete's and before the primary SaveChanges() and AcceptAllChanges() calls at the end of the "Using...Scope" block.  The error still occured.   I've also tried that same approach with two "SaveChanges(Save.Options.None)" calls, with calling the two apparently required SaveChanges with different combinations of Saveoptions, etc.  Same resulting error.

    (I've seen web threads discussing this type of issue, but all the proposed solutions involved "SaveChanges(false)" which is apparently no longer an option with EF v4.   "SaveOptions.None" definitely does not work.)

    I can make this work if I change either change the call between the Deletes and re-Adds to "SaveChanges(AcceptAllChangesAfterSave)" or else add an intervening "TargetContext.AcceptAllChanges()" call.  

    But from the way the documentation reads, it sounds like I would no longer have both Deletes and re-Add's included in the same transaction scope.    Am I reading the documentation wrong?    If the "Using ... Transaction Scope" trump does indeed trump the ordinary meaning of the "SaveOptions.AcceptAllChangesAfterSave" so that the Deletes/Re-Adds are still Atomic, and if the implied "AcceptAllChanges" is not really happening, why is it the only way this sequence will work?  Why doesn't doesn't the intervening SaveChanges() work with either of the other "SaveOptions"?  

    Thanks for any who can explain what's happening,

    • Edited by Bob Riddle Tuesday, August 2, 2011 3:07 PM Delete blank line
    Tuesday, August 2, 2011 3:04 PM

All replies

  • Hello,

    From your description, here's my understanding. You executed Delete/Add operations in one TransactionScope block. When you deleted the data and save this change after you invoke SaveChanges method. I suggest you can seperate these two operations into two single TransactionScope and call SaveChanges method twice and try it again.


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 4, 2011 10:08 AM
  • Hello,

    I appreciate the reply, but as I said, the "Deletes" / "re-Adds" have to be within the same transaction.

    The target databases into which the Workflows and Assemblies are being deployed are typically Production databases.   If there was a problem in between committing the "Delete" transaction scope and the "re-Add" scope, we would end up with no functional Workflow(s) at all in the Production database.  Production would grind to a halt.  Not good.

    • Edited by Bob Riddle Friday, August 5, 2011 12:58 PM corrected doubled word
    Thursday, August 4, 2011 5:24 PM
  • Hello again,

    I think you can use one step to operate these two steps. You can modify, create, and delete objects in an object context. Before SaveChanges method was called, you can check the data was existed in your context or notand operate modify or insert operations, respectively. Check this reference. http://msdn.microsoft.com/en-us/library/bb738695.aspx

    Hope this helps.

    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 8, 2011 8:16 AM
  • Hi,

    Thank you.  I had thought of that before in terms of detecting Deletes/re-Add sceneraios and mapping them to customized Updates; but had only thought of that at the C# client code level instead of within stored procedures.   I'm glad to hear the latter is an option.  

    The problem with that approach at either layer is that I already have at least a dozen entity types to deal with.   Some have large numbers of properties and are subject to rapid changes in the numbers and types of properties as we get more familiar with Workflow Foundation 4.    (As are the number of child entity types involved).   So I had hoped not to have to specifically code for invididual properties and revised related child entity types so as to prevent adding a new maintenance requirement to match up the required Delete-ReAdd scenario detection and Update logic to new implementation changes.  

    (I wish our WF4 implementation was more static, but as we're deploying our first WF4 efforts we keep finding nuances that require changes).

    I had hoped that simply Deleting and re-Adding top-level entities rather than having to track down every small data change within the entity hierarchies meant I did not have to add and later maintain all that logic, did not have to code for new F-Key dependent row types, etc.   If I do have to manually detect Delete/Re-Add and map it to Updates, I'm currently thinking I'm still better off doing it in the C# code vs. in EF-CRUD-mapped stored procs.   At least in C# code I may be able leverge reflection and so have better luck in minimizing the long-term maintenance burden.  

    At first glance, having to specifically code stored procs does not seem like it would save any effort; it would just move the same effort to an environment in which it's more complicated to develop and test.    Or am I misunderstanding the link you referenced?

    • Edited by Bob Riddle Monday, August 8, 2011 7:48 PM correct spelling
    Monday, August 8, 2011 2:26 PM