CODEWITHSUNDEEP
c#sqlentity-framework
Oleg Sh
Oleg Sh

Reputation: 9013

Entity Framework children collection

I have a table named "Notaries":

NotaryID int,
NotaryName nvarchar(MAX)

and table named "NotaryPhones":

PhoneID int,
NotaryID int,
PhoneNumber nvarchar(50)

So, relationship "one-to-many". Now I want to clear all phones, depending on the notary. My code:

            Notary.Models.Notary notary = (from i in db.Notaries where i.NotaryID == model.NotaryID.Value select i).FirstOrDefault();
            notary.CityID = Convert.ToInt32(model.City.SelectedItem);
            notary.NotaryPhones.Clear();
            db.SaveChanges();

but I get an error:

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.

If I remove the string

notary.NotaryPhones.Clear();

it works. I understand, that this is a trivial thing, but don't understand how to fix it

Upvotes: 1

Views: 196

Answers (3)

krillgar
krillgar

Reputation: 12805

What's going on is that your notary.NotaryPhones.Clear(); is removing the foreign-key reference from your NotaryPhone table to your Notary table. Because this is set up as a non-nullable key so that you don't have orphan phone records, you're receiving that error.

What you'll want to do instead is set up a method in your repository that will call context.NotaryPhones.Remove(**instance variable pointing to one of the phones to delete**); and that will delete them from the database.

CLARIFICATION

The reason why it removes the foreign key reference is that notary.NotaryPhones.Clear() just removes those objects from the notary object. The objects will continue to exist the way that you have written this.

So when the .Clear() is executed, it takes your phone.NotaryID property and gets rid of the ID pointer to the notary. Since you're not assigning it anything else, the value it tries to assign is null (the only value that couldn't possibly point to a Notary object). But, because you have it set up as an int, and not an int?, it can't make that assignment and it throws the error you see.

You said you're trying to delete the objects, so you need to remove them from the database. To do that, you do what I explained above.

Upvotes: 3

Dennis
Dennis

Reputation: 37770

Clear just removes entity from the related collection. it doesn't remove entity from database. you have to delete each NotaryPhone from corresponding DbSet.

Upvotes: 2

user3703582
user3703582

Reputation:

please excuse the example if there are syntax errors, I am currently working in VB but the approach is the same

foreach (NotaryPhones np in notary.NotaryPhones)
{
      db.NotaryPhones.DeleteObject(np);
}

then save your changes with

db.SaveChanges();

Upvotes: 2

Related Questions