Entity framework Update fails when object is linked to a missing child

Question

I’m having trouble updating an objects child when the object has a reference to a nonexising child record. eg. Tables Car and CarColor have a relationship. Car.CarColorId >> CarColor.CarColorId If I load the car with its color record like so this

 var result = from x in database.Car.Include("CarColor")
              where x.CarId = 5
              select x;

I'll get back the Car object and it’s Color object. Now suppose that some time ago a CarColor had been deleted but the Car record in question still contains the CarColorId value. So when I run the query the Color object is null because the CarColor record didn’t exist. My problem here is that when I attach another Color object that does exist I get a Store update, insert error when saving.

Car.Color = newColor
Database.SaveChanges();

It’s like the context is trying to delete the nonexisting color. How can I get around this?

Answer 1

ok wow - i just spent 40 minutes trying to work around this and I couldn't... not directly anyway. There seems to be no way in which you can set the Entity Framework to not enforce the relationship constraint.

You will have to drop down to "bare" sql in some whay. So what I would do in your situation is make a sql view with the cars that reference valid car colors (do a sub query count or something similar) and load cars with valid color references from there.

If you want to set the color for a car that does NOT have a valid color, load the car but without the Include(), and set the color object directly.

Answer 2

Nope, EF will always enforce constraints. The only way I could think of to do it would be to gen a second model that has no constraints, or has them removed that is used when you do not wish to enforce them.

But I think the correct answer here is to fix the violations. They should be removed or fixed in the database. Having a proper FK relationship in the first place with update and delete integrity actions would have prevented it in the first place.

Answer 3

Not EF enforces the constraints but the database, that is why you get the error when you are trying to save it. The only way (as far as i know) is to be sure that the child exists. You should set the appropriate foreign key constraint in your database between the Car and CarColor table.