Error Deleting a child object from its parent

Question

I'm using vb.net 2013, Entity Framework 6 and SQL Server 2008 R2.

I'm trying to delete from child entities, and this does not work. But if I try to delete directly from context, this works.

In my database I have 2 tables Students and Result.

This is my code that does not work :

Dim context as Myentities = New myentities.
Dim s as student.
Dim lresult as new list (of result)

s = context.students.where(Function(t1) t1.value>5).Tolist.first

lresult = (from t in s.results where t.vl2=7 select t).Tolist

for each rs as result in lresult
   if rs.vl3=11 then s.results.remove(rs)
Next

Context.SaveChanges

This code produces an error on the last line (context.SaveChanges) :

An unhandled exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll
Additional information: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

But if I change the line that delete the item, like below, it works :

Dim context as Myentities = New myentities.
Dim s as student.
Dim lresult as new list (of result)

s = context.students.where(Function(t1) t1.value>5).Tolist.first
lresult = (from t in s.results where t.vl2=7 select t).Tolist

for each rs as result in lresult
   if rs.vl3=11 then context.results.remove(rs)
Next

Context.SaveChanges

Why does my first snippet of code not work?

Diana Diana · Accepted Answer · 2018-02-14T21:20:40

I guess you defined the relationship between Students and Results by adding a Foreign Fey from Results to Students, a non-nullable field named StudentId or similar. When you do the Remove from the parent Student collection of Results, EF does not really remove the deleted Result from the context, EF just removes the relationship between them and sets the StudentId value in your Result child as null, but the child remains in the context. So you get an orphaned child that can't be saved because the FK field is null.

The problem is that your relationship conceptually is an Identifying relationship, which means that you can't have a Result that does not belong to a Student. But EF sees your relationship as non-identifying.

You have two options:

Make EF recognize the relationship as an Identifying relationship, by adding the Foreign Key field StudentId to the Primary Key of Results. You will have a composite key. This way EF will take care of removing the child from the context when you remove it from the collection in the parent.
Leave the PK as is, and explicitly remove the child from the context before saving. That is, you do both the Removefrom the parent collection and the context.Remove.

Usually the second option is preferred because composite keys are a bit complicated to deal with in EF. You can write a method to encapsulate the two operations that should go together every time you delete a Result from a Student.

More info about Identifying relationships here.

Error Deleting a child object from its parent

1 Answers