1

Using Entity Framework I have a parent and child relationship between two tables. I am retrieving data from an online source and saving the data locally.

The List of children changes in all ways as the parent goes through it's lifespan. Children get added, removed or edited.

Step 1: In my scenario I get a parent with children 1 - 2. I create that parent/children using:

Parent p = new Parent();

Child c1 = new Child ();
c1.Name = "Test1";

Child c2 = new Child ();
c2.Name = "Test2";

p.Children.Add(c1);
p.Children.Add(c2);

context.AddToParents(p);
context.SaveChanges();

Step 2: The next time I update child #2 is no longer there. In this case I have queried my online source and received a list of Children that does not contain the child "Test2". I am SIMULATING this (because the code is in no way reproducable) by using linq to remove the child from the parent.Children list.

Children is defined as (in Parent):

ObjectSet<Child> Children

I remove child #2 like so:

var parent = (from p in context.Parents
    where p.Name == "Parent"
    select p).FirstOrDefault();

string childNameToRemove = "Test2";

var child = (from c in context.Children
    where c.Name == childNameToRemove
    select c).FirstOrDefault();

parent.Children.Remove(child);
context.SaveChanges();

When this happens the child is not removed from the table but the parent key is nulled.

Step 3: Later when I get an update from the online source child #2 is now one of the children again. When I go to add that back in I get a Key violation because that record is still in the table. 

var parent = (from p in context.Parents
    where p.Name == "Parent"
    select p).FirstOrDefault();

Child child = new Child();
child.Name = "Test2";

parent.Children.Add(child);
context.SaveChanges();

I get this error:

"Violation of PRIMARY KEY constraint 'PK_Slave'. Cannot insert duplicate key in object 'dbo.Child'. The duplicate key value is (Test2).\r\nThe statement has been terminated."

I am guessing that I need to either delete the orphaned child from the table after I update the parent with new data (in step 2) -or- check for an existing child record with a null pointer to parent first and reuse that record instead of adding a new child record. Or perhaps both?

Is there a better design pattern for this? I have been looking most the afternoon and the answer is evading me.

Eric Snyder
  • 1,816
  • 3
  • 22
  • 46
  • first, lets note that scenario you've mentioned in your question that gets data from a difference source and then projects it to a new instance that is added/created in the entity framework context isn't represented in the code snippet you've provided: you've giving us sudo code reflecting your effort. Whereas I don't think that's an issue with step 1, I am interested in how you're implementing step 2 as it relates to instances\entities. is `c2` an instance of `Child` queried from the context? is `Children` a `HashTable` on `Parent` type, and is `p` an instance\entity of that context? – Brett Caswell Oct 17 '19 at 22:42
  • @BrettCaswell - Actually, that is not pseudo code. I set up a couple of test tables that are ultra simplified.That code is taken from testing I have been doing. I will edit my post above to answer your questions. – Eric Snyder Oct 18 '19 at 12:58
  • thanks for updating your question.. I can actually see where one might desire deleting the relationship of records without deleting the child records, so I'm inclined to believe that the act of deleting a child in the scope of the parent represents that; where as deleting the instance from `context.Children` implies the intent to remove the record itself. Though, I do believe there is a `DbConfiguration` or other means of dictating this behavior. see https://stackoverflow.com/a/16655911/1366179 – Brett Caswell Oct 21 '19 at 06:08
  • EF has changed a bit in it's design since I used it regularly (which was Db First with edmx)... did you generate these entities\models from your database? have you tried getting the instances from the parent (lazy loading them in essentially) and deleting those and saving. does `parent.Children.Where(c => c.Name == childNameToRemove).FirstOrDefault()` return the instance or some IKeyLookup type?.. Also, I would recommend you add a DbConfiguration Interceptor that outputs to your debug console so that you can see the expressions output when you do a `SaveChanges` – Brett Caswell Oct 21 '19 at 06:14

0 Answers0