Entity framework changing foreign key throws constraint violation with the correct ids

Question

Can someone help me work out whats going on? I'm trying to update a foreign key and I'm getting strange behaviour. We have change tracking and lazy loading disabled and are using POCO classes. I am unable to update the foreign key Id without it throwing the following error:

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 'Child.ChildId' on one end of a relationship do not match the property value(s) of 'Link.ChildId' on the other end.

var linkTable = await Context.LinkTable
                .Include(x => x.Child).FirstAsync(_ => _.LinkId == linkId);

// .... Log details of linkTable.Child

var newChild = await Context.ChildTable.FirstAsync(_ => _.ChildId == newChildId);

linkTable.Child = newChild;
linkTable.ChildId = newChild.ChildId;  // (1)

Context.Entry(linkTable).State = EntityState.Modified;

await Context.SaveChangesAsync();

var parent = await Context.ParentTable
            .Include(x => x.Links.Select(y => y.Child))
            .FirstAsync(x => x.ParentId == parentId); // (2)

If I remove line (1), the error goes away at the SaveChanges call but it doesn't update the ChildId so the change is lost. I have also tried just updating the ChildId on it's own, and also nulling the child object first. Any changes to the ChildId throw this error.

The parent at line (2) loads the link with the new child even though it still shows the old ChildId. The db is not updated so the change is never committed.

The POCO:

[Table("LinkTable")]
public class LinkTable
{
    [Key]
    public int LinkTableId { get; set; }   

    //[ForeignKey("Child")] // (1)
    public int ChildId { get; set; }

    [ForeignKey("ChildId")] 
    public virtual ChildTable Child { get; set; } // (2)
}

(1) I have tried with both variants of the ForeignKey attribute.

(2) I have tried with and without the virtual key word, I believe without lazy loading we shouldn't need it.

The error makes no sense to me as the ChildId is taken from the child object, how can they not match?

You can checkout this https://stackoverflow.com/questions/25320296/referential-integrity-constraint-violation-when-attempting-to-set-a-fk-to-null From the answer, you have tried option 1, try options 2 and 3. — M Bakardzhiev, Apr 06 '18 at 09:35
It has to be related to *"We have **change tracking** and lazy loading **disabled**"*. Do you mean that the `AutoDetectChangesEnabled` is `false`? If yes, try replacing the (redundant) `Context.Entry(linkTable).State = EntityState.Modified;` with `Context.ChangeTracker.DetectChanges();` — Ivan Stoev, Apr 06 '18 at 09:59
@IvanStoev Thank you that fixed it. I was under the impression `Context.Entry(linkTable).State = EntityState.Modified;` was **required** with `AutoDetectChangesEnabled` set to false. That's what the documentation pointed us to, and without it changes were not saved. — Magpie, Apr 06 '18 at 10:16
It's usually used when you have disconnected entity and want to "blindly" apply the changes of any primitive property. When you have attached (tracked) entity, it's better to let the change tracking detect and apply only modified properties if any. — Ivan Stoev, Apr 06 '18 at 10:33

Entity framework changing foreign key throws constraint violation with the correct ids

0 Answers0