Error when saving ObjectContext after replacing Parent item and linking Child item to it

Question

In my application, the interface allows the user to create and delete entities in an Entity Framework model. All changes are added to the ObjectContext, and saved only when the user selects "Save".

My question is best asked by means of the following simple example:

I have a Foreign Key relation between two entity types, which I will call Parent and Child here. The database, in SQLite, is declared as follows:

CREATE TABLE Parents   (ID INTEGER PRIMARY KEY);

CREATE TABLE Children  
(
    ID INTEGER PRIMARY KEY,
    ParentID INTEGER NOT NULL 
        CONSTRAINT FK_ChildrenToParents REFERENCES [Parents](ID) 
            ON DELETE CASCADE ON UPDATE CASCADE
)

I then build an Entity Framework model on that database, and execute the following actions against it (see code below):

1. Create a Parent
2. Save
3. Delete the Parent
4. Create a new Parent with the same ID number as the original
5. Create a Child and provide it with the new Parent's ID number
6. Save (Fails)

demoDBEntities entities = new demoDBEntities();

Parent originalParent = new Parent { ID = 1 };
entities.Parents.AddObject(originalParent);

entities.SaveChanges(); // First Save

entities.Parents.DeleteObject(originalParent);

Parent newParent = new Parent { ID = 1 };
entities.Parents.AddObject(newParent);
Child newChild = new Child { ID = 2, ParentID = 1 };
entities.Children.AddObject(newChild);

entities.SaveChanges(); // Second Save

I get the following error on the second call to SaveChanges():

"Unable to insert or update an entity because the principal end of the 'demoDBModel.FK_Children_0_0' relationship is deleted."

It seems the problem is that the Entity Framework links the new Child item to the originalParent item, which is then deleted and replaced by the newParent item. This might sound conceited, but happens quite naturally in my application.

Is there any way for me to work around this problem?

PS: I know it's bad practice to re-use ID numbers of DB entries - however, in my case the ID numbers are Asset numbers, which could theoretically be reused. That said, the above scenario typically happens if the client creates a faulty Parent, then deletes it, recreates it, and then creates the Child item.

Answer 1

Instead of setting the foreign key property ParentID you could try to set one of the navigation properties (you must have at least one) and use only one call to AddObject to add the complete object graph to the context:

Either:

//...
entities.Parents.DeleteObject(originalParent);

Child newChild = new Child { ID = 2 };
Parent newParent = new Parent { ID = 1, Children = new List<Child>{ newChild } };
entities.Parents.AddObject(newParent);

entities.SaveChanges();

Or:

//...
entities.Parents.DeleteObject(originalParent);

Parent newParent = new Parent { ID = 1 };
Child newChild = new Child { ID = 2, Parent = newParent };
entities.Children.AddObject(newChild);

entities.SaveChanges();