Entity Framework referential integrity issue

Question

The problem:

I have the code which is adding/updating properties with EF. Previously it was calling after modifying each property SaveChanges() and in that case it was working. What I have done. I have modified the code to have a batch (10 items at this time) and after inserting number of entries call SaveChanges(). But sometimes it starts to throw such an exception

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.

Issue I found:

The issue I found when I tried to modify 14 entries. First batch of 10 entries it is doing good. Than it is adding 11th entry and when tries to add 12th it was throwing this exception. I checked the code and found part which is causing exception.

property.CountryId = importProperty.CountryId;

It is setting CountryId to 123, but navigation property Country here has Id set to 54 (I am not changing this, just got version from database). Setting navigation property to null manually is throwing such an error

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.

Question:

What should I do? Why it is working when I'm calling SaveChanges after each entry and not working in this case? I found questions in Stackoverflow about this problem, but no one solves my issue.

Some code:

public void InsertOrUpdateEntity(TEntity entity)
{
    if (entity.ObjectState == ObjectState.Added)
    {
           dbSet.Add(entity);
    }
    else
    {
           dbSet.Attach(entity);
           context.ApplyStateChanges();
    }
}

Error occured on the line dbSet.Attach(entityGraph); with a property mentioned above.

I think the crucial part is the Navigation Property and the Foreign key. And the sequence of queries and assignments that lead to the issue. Are you saying they differ in value? If so why? Is 123 a valid ID for a Country? What set the Country navigation property and what set the foreign key? Perhaps these might help... http://www.ladislavmrnka.com/2011/05/foreign-key-vs-independent-associations-in-ef-4/ ...or... http://stackoverflow.com/questions/14915938/in-what-scenarios-do-i-need-foreign-keys-and-navigation-properties-in-entity-fra — Mick, Aug 15 '14 at 06:58
Yes, they differ because previously CountryId was 54, but code tried to modify it to new value - 123. — Chuck Norris, Aug 15 '14 at 07:02
I'd guessed CountryID is non-nullable. I thought perhaps 123 is a dummy value set by the Entity Framework before a value is set. Is the navigation property Country a new entity also being saved in the same batch? — Mick, Aug 15 '14 at 07:05
CountryID is non-nullable. It is just getting Property from database, set Property.CountryId (and other fields) and is calling Attach. — Chuck Norris, Aug 15 '14 at 07:07
If you could post a small bit of code that replicates the issue showing the assignments made to the entities and the call to savechanges I think that'd help people understand the issue — Mick, Aug 15 '14 at 07:08

score 0 · Answer 1 · answered Aug 15 '14 at 07:34

This might be the answer.

Assign the CountryID to 123, either load the Country entity for 123 or create a new instance of the Country entity and assign it's ID to 123, Attach the country entity to the entity context (i.e. dbset) and assign it to the Country property.

score 0 · Answer 2 · answered Aug 19 '14 at 15:09

The problem was that 11th and 12th rows were same entity in database.

Entity Framework changed Country Id from 101 to 54 at 11th record and than to 123 at 12th record (and at the end EF finished with CountryID = 123 and Country.Id = 54 in case when at the beginning both of them were 101!!).

Problem was fixed with condition to not allow multiple changes of same entity.

score 0 · Answer 3 · answered May 21 '20 at 12:14

0

In my case, I've solved by checking "Include foreign key columns in the model" in Update wizard in edmx Model.

answered May 21 '20 at 12:14

Shukry Ishaq

1

This can also be a comment as you are not pointing out to any particular solution. – Chakri May 21 '20 at 12:36

Entity Framework referential integrity issue

3 Answers3