EF 5, update object gives "A referential integrity constraint violation occurred"

Question

In my model I've got a bunch of domain objects. Now I'm having a problem when trying to update a User-object. The User has a foreignkey relation to the Role object. When I update the User-object without changeing the foreignkey value (FkRoleId) it all works fine. But when I change the role for the current user I want to update I get the error:

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.

Here's how I update my user-object:

public void Update(User user)
{
    using (var context = new DBEntities())
    { 
    context.Entry(user).State = System.Data.EntityState.Modified;
    context.SaveChanges();
    }
}

How can I update my user-object without getting this exception? There must be a way for chaning the foreignkey value for mappen the user to another role.

Here's my domain classes in this case:

public partial class User
{
public User()
{
    this.Advertisers = new HashSet<Advertiser>();
    this.Cases = new HashSet<Case>();
    this.Materials = new HashSet<Material>();
}

public int PkId { get; set; }
public int FkRoleId { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public System.DateTime Created { get; set; }
public bool Active { get; set; }

public virtual ICollection<Advertiser> Advertisers { get; set; }
public virtual ICollection<Case> Cases { get; set; }
public virtual ICollection<Material> Materials { get; set; }
public virtual Role Role { get; set; }
}


public partial class Role
{
public Role()
{
    this.Users = new HashSet<User>();
}

public int PkId { get; set; }
public string Name { get; set; }
public string Description { get; set; }

public virtual ICollection<User> Users { get; set; }
}

However; if I set the navigation property user.Role to null it works just fine. But, is this the recommended way of working?? — Nicke, Jan 17 '13 at 11:39
This may be a duplicate of these QA's http://stackoverflow.com/questions/5679703/how-do-i-save-a-child-entity-in-entityframework-4, http://stackoverflow.com/questions/5716733/updating-foreign-key-associations-in-entity-framework-4-1-code-first, http://stackoverflow.com/questions/8856588/how-to-update-complex-model-in-asp-net-mvc-3 — Kevin Junghans, Jan 17 '13 at 14:09

score 29 · Accepted Answer · answered Mar 14 '13 at 21:36

To quote your comment:

However; if I set the navigation property user.Role to null it works just fine. But, is this the recommended way of working?

Yes, in this case it is.

You must keep in mind that you enter a new context with a detached object user (including the reference to user.Role). By setting the state to Modified you attach the object user together with the related user.Role to this new context.

Because your are using a foreign key association - that means that the foreign key is represented by a property FkRoleId in the model class - the relationship is described in two ways: By the navigation property user.Role and by the scalar property user.FkRoleId. If these are not consistent - i.e. user.Role.PkId != user.FkRoleId - EF doesn't know which one describes the correct relationship and throws the exception you have.

If you set user.Role to null EF will consider the user.FkRoleId alone as the property that describes the relationship between user and role and the ambiguity that was causing the exception is removed.

And what if I need to attach an object with changed navigation property and intact scalar one? Can I somehow tell EF to use navigation property as "primary"? — Vasaka, Aug 26 '13 at 15:21

score 0 · Answer 2 · answered Sep 10 '15 at 14:33

For the benefit of searchers, I was getting the same error but found a quick fix.

I was loading a class with an FK relationship into the view to be edited. Even with no changes, the FK was being dropped and this error occurred. The fix was to include a hidden field into the Razor view that contained the FK. e.g.

@Html.HiddenFor(model => model.ReleaseInfo.BookId)

Code copied below so you can check if your situation is similar.

Controller Actions

public ActionResult Edit(int id = 0)
        {
            Book book = db.Books.Find(id);
            if (book == null)
            {
                return HttpNotFound();
            }
            ViewBag.Id = new SelectList(db.ReleaseInfo, "BookId", "BookId", book.Id);
            return View(book);
        }

        //
        // POST: /Book/Edit/5

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(Book book)
        {
            if (ModelState.IsValid)
            {
                db.Entry(book).State = EntityState.Modified;

                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.Id = new SelectList(db.ReleaseInfo, "BookId", "BookId", book.Id);
            return View(book);
        }

Relationships

Book.cs

  public class Book
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        [MaxLength(100)]
        public string Title { get; set; }


        public virtual ICollection<Author> Authors { get; set; }


        public virtual ReleaseInfo ReleaseInfo { get; set; }


        public virtual ICollection<ReRelease> ReReleases { get; set; }

    }

ReleaseInfo.cs

public class ReleaseInfo
    {
        // One to one relationship uses Books Key as Key.
        [Key, ForeignKey("Book")]
        public int BookId { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public DateTime ReleaseDate { get; set; }


        public virtual Book Book { get; set; }
    }

EF 5, update object gives "A referential integrity constraint violation occurred"

2 Answers2

Linked