A referential integrity constraint violation occurred – Entity Framework

When working on a project with the Entity Framework I came across the following exception.

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: A referential integrity constraint violation occurred: The property value(s) of 'Person.Id' on one end of a relationship do not match the property value(s) of 'Contract.PersonId' on the other end.

It took me several days to figure out what happened. In this blog I want to explain what I did to get this error and how I solved it because the supplied solutions on the Internet didn’t apply to this problem.

The Database

Because I work with a lot of data that have to be imported or transformed I turned the auto detect changes and validate on save option off. By turning this option off you have to notify the database context of any change that you make to an object. Below you can find a simplified version of my code.

public class Contract

{

    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]

    public long Id { get; set; }

    public string Name { get; set; }

    public long PersonId { get; set; }

    public virtual Person Person { get; set; }

}

public class Person

{

    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]

    public long Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Contract> Contracts { get; set; }

}

public class TestContext : DbContext

{

    public DbSet<Person> Persons { get; set; }

    public DbSet<Contract> Contracts { get; set; }

    public TestContext()

    {

        // Disable auto detect changes and validate on save

        // for much beter performance.

        Configuration.AutoDetectChangesEnabled = false;

        Configuration.ValidateOnSaveEnabled = false;

    }

}

We just have two objects in our database (Contract and Person) and auto the detect changes and validate on save option off is turned off by default in the database context TextContext.

Initialize Database

Before we can start to reproduce the problem we have to create a database with some initial data. This is done by the following code.

/// <summary>

/// Initializes the database with initial data.

/// </summary>

private void InitDatabase()

{

    // Make sure we are working with an empty database

    Database.SetInitializer<TestContext>(new DropCreateDatabaseAlways<TestContext>());

           

    using (var db = new TestContext())

    {

        // Create Items

        var mike = new Person()

        {

            Id = 1,

            Name = "Mike"

        };

        var scott = new Person()

        {

            Id = 2,

            Name = "Scott"

        };

        var contract = new Contract1()

        {

            Id = 1,

            Name = "Car Rental",

            PersonId = mike.Id,

        };

        // Add items to database

        db.Persons.Add(mike);

        db.Persons.Add(scott);

        db.Contracts.Add(contract);

        // Save changes to database

        db.SaveChanges();

    }

}

Test Scenarios

The following example shows how we change the linked person to the contract from Mike to Scott and save it to the database. So far so good, the code works like expected.

private void ValidCode()

{

    using (var db = new TestContext())

    {

        // Person Id 1 is Mike

        // Person Id 2 is Scott

        var scott = db.Persons.Find(2);

        var contract = db.Contracts.Find(1);

        // Change the person that is linked

        // to the contract from Mike to Scott

        contract.PersonId = scott.Id;

        // Because we turned off auto detect changes

        // we have to mark the entity as changed manualy.

        db.Entry(contract).State = EntityState.Modified;

        // Save changes to database.

        db.SaveChanges();

    }

}

But when we check if the contract is linked to Mike before we change the linked person we get the following exception.

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: A referential integrity constraint violation occurred: The property value(s) of 'Person.Id' on one end of a relationship do not match the property value(s) of 'Contract.PersonId' on the other end.

private void InvalidCode()

{

    using (var db = new TestContext())

    {

        // Person Id 1 is Mike

        // Person Id 2 is Scott

        var scott = db.Persons.Find(2);

        var contract = db.Contracts.Find(1);

        // Make sure the contract is linked

        // to Mike.

        if (contract.Person.Name != "Mike")

        {

            throw new Exception("The contract isn't linked to Mike");

        }

        // Change the person that is linked

        // to the contract from Mike to Scott

        contract.PersonId = scott.Id;

        // Because we turned off auto detect changes

        // we have to mark the entity as changed manualy.

        db.Entry(contract).State = EntityState.Modified;

        // Save changes to database.

        // The "The ObjectContext might be in an inconsistent state"
        // exception is thrown here.

        db.SaveChanges();

    }

}

In this case the exception isn’t strange. The entity framework makes use of lazy loading. Because we read the property Person of the contract object the Person Mike is loaded from the database. The PersonId property is now referring to Scott and the Person property is now referring to Mike. In this case the exception makes sense.

Lets change the code and make sure the PersonId and Person property of the contract object are linked to the same person.

private void StillInvalidCode()

{

    using (var db = new TestContext())

    {

        // Person Id 1 is Mike

        // Person Id 2 is Scott

        var scott = db.Persons.Find(2);

        var contract = db.Contracts.Find(1);

        // Make sure the contract is linked

        // to Mike.

        if (contract.Person.Name != "Mike")

        {

            throw new Exception("The contract isn't linked to Mike");

        }

        // Change the person that is linked

        // to the contract from Mike to Scott

        contract.PersonId = scott.Id;

        contract.Person = scott;

        // Because we turned off auto detect changes"

        // we have to mark the entity as changed manualy.

        db.Entry(contract).State = EntityState.Modified;

        // Save changes to database.

        // The "The ObjectContext might be in an inconsistent state"

        // exception is thrown here.

        db.SaveChanges();

    }

}

That’s strange… the code still throws and inconsistent state exception.

It gets even stranger when we read Person Mike directly from the database and not via contract. Because the entity framework is very smart and caches entities for you it knows that Person Mike is loaded when you read the property Person from the contract object so it doesn’t try to retrieve Mike twice from the database. This is a nifty feature but in this case it also means we aren’t able to change the PersonId property of the contract object because the save changes will now also throw an inconsistent state exception.

private void StillInvalidCode()

{

    using (var db = new TestContext())

    {

        // Person Id 1 is Mike

        var mike = db.Persons.Find(1);

        // Person Id 2 is Scott

        var scott = db.Persons.Find(2);

        var contract = db.Contracts.Find(1);

        // Change the person that is linked

        // to the contract from Mike to Scott

        contract.PersonId = scott.Id;

        // Because we turned off auto detect changes"

        // we have to mark the entity as changed manualy.

        db.Entry(contract).State = EntityState.Modified;

        // Save changes to database.

        // The "The ObjectContext might be in an inconsistent state"

        // exception is thrown here.

        db.SaveChanges();

    }

}

I’ve tried several variations like setting the Person property to null but nothing seemed to work. In the end I just found two workarounds to solve the problem.

Solutions

1. Don’t disable the auto detect changes.
   
   
   public class TestContext : DbContext
   {
       public DbSet<Person> Persons { get; set; }
       public DbSet<Contract> Contracts { get; set; }
   
   
       public TestContext()
       {
           Configuration.AutoDetectChangesEnabled = true;
       }
   }
   
2. Don’t load the linked object directly from the database or via the property if you want to change is.
   
   
   private void ValidCode()
   {
       using (var db = new TestContext())

       {

           // Person Id 1 is Mike

           var mike = db.Persons.Find(1);
       }

       using (var db = new TestContext())
       {
           // Person Id 2 is Scott
           var scott = db.Persons.Find(2);
           var contract = db.Contracts.Find(1);
   
   
           // Change the person that is linked
           // to the contract from Mike to Scott
           contract.PersonId = scott.Id;
   
   
           // Because we turned off auto detect changes"
           // we have to mark the entity as changed manualy.
           db.Entry(contract).State = EntityState.Modified;
   
   
           // Save changes to database.
           db.SaveChanges();
       }
   }
   
3. Detach the previous linked object.
   
   
   private void ValidCode2()
   {
       using (var db = new TestContext())
       {
           // Person Id 1 is Mike
           var mike = db.Persons.Find(1);
           // Person Id 2 is Scott
           var scott = db.Persons.Find(2);
           var contract = db.Contracts.Find(1);
   
   
           // Change the person that is linked
           // to the contract from Mike to Scott
           contract.PersonId = scott.Id;
   
   
           // Because we turned off auto detect changes"
           // we have to mark the entity as changed manualy.
           db.Entry(contract).State = EntityState.Modified;
   
   
           // Detach Mike from the database context

           // so we don't get the incosistent state exception.

           context.Entry(mike).State = EntityState.Detached;

   
   

           // Save changes to database.
           db.SaveChanges();
       }
   }