Entity Framework: A referential integrity constraint violation on many to many relationship

Question

Hey I have an application with a bunch of inproc caching and entity framework. When I want to write an update to an entity I reattach the cached copy. I track all things I've attached in the life cycle of the context so I don't try to attach them twice.

I have an error occurring on attach (very rarely in most cases this works fine and is really fast) which says the following:

A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship.

I've taken a really careful look at the entity which looks normal. I think this issue is due to the attachment/detachment of a foreign key when fixups runs.

Is there a good way to get any more info on this error or can it occur for reasons other than that the entity was in a state which EF wasnt expecting?

EDIT: DB Diagram (note i'm using codefirst I just used the EDMX tool to make the diagram, I've also chopped a bunch of regular properties off the model for simplicity)

Answer 1

The error could occur for the one-to-many relationship between Person and Location you apparently have in your model in addition to the many-to-many relationship. For example the following code would throw the exception:

using (var context = new MyContext()) {     var person = new Person     {         CurrentLocationId = 1,         CurrentLocation = new Location { Id = 2 }     };     context.People.Attach(person); // Exception } 

"The property values that define the referential constraints" are the foreign key property value CurrentLocationId and the primary key value CurrentLocation.Id. If those values are different the exception is thrown. (Having CurrentLocation as null though is allowed.)

In my opinion this exception can only be thrown for foreign key associations because only for this type of association you have properties that define referential constraints at all in your model. It cannot be thrown for independent associations. Since every many-to-many relationship is an independent association (no foreign key property in the model) my guess is that the error is not related to your many-to-many relationship, but to the one-to-many.

Answer 2

I came across a very similar exception:

"A referential integrity constraint violation occurred:  The property value(s) of 'ObjectA.PropertyX' on one end of a relationship  do not match the property value(s) of 'ObjectB.PropertyY' on the other end." 

The reason was this: The client side of the web API sent a PUT request with the entire object including the navigation property (in this example ObjectA (more correctly ObjectB.ObjectA) was a navigation property and was fully supplied by the client). This occurs because the client receives the entire object from the server and bounces it as-is back to the server with minor changes.

On the other hand, the ObjectB.PropertyY had just been changed (this was the reason for the PUT request in the first place).

Since ObjectB.PropertyY was a reference to the same object ObjectA (a foreign key), EF tried to reconcile this and failed with the above exception.

The solution was simple:

ObjectB.ObjectA = null; 

before the SaveChanges() solved this completely.

I hope this helps someone.