skylake skylake - 2 months ago 18
C# Question

The UPDATE statement conflicted with the FOREIGN KEY constraint.. MVC(Database-first)

I got two tables, one

User
and
Comment
. Both
User
and
Comment
got
Id
as Primary Key.
Comment
also have a FK key for
User
Id so called
CreatorUserID
. Once I try to edit a
Comment
row I get the following error:


The UPDATE statement conflicted with the FOREIGN KEY constraint
"FK_Comment_User". The conflict occurred in database "XX", table
"dbo.User", column 'Id'.


I'm not touching FK
CreatorUserId
in
Comment
table. I also made sure to enable Cascade in Update and Delete for FK-relationships in SQL Server.

This is how my Edit action looks like:

public ActionResult Edit(Guid? id) {
Comment comment = db.Comment.Find(id);
if (review == null) {
return HttpNotFound();
}
return View(comment);
}


[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "Id,CreatorUserID,Description,Title")] Comment comment) {
if (ModelState.IsValid) {

db.Entry(comment).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(comment);
}


Additional info:


  • Database-first

  • Using Guids for Id's.



This is how url looks like before I click update which result error:
http://localhost:41003/Comment/Edit/8cab7ab2-3184-e611-9d7a-6c71d98d2a40


User-Table

Id(Guid) PK
Email(nvarchar)
Password(nvarchar)


Comment-table

Id(Guid) PK
CreatorUserID(Guid) FK
Description(nvarchar) // this and title is the only thing I'm trying to edit
Title(nvarchar)


What am I doing wrong? I just want to Update
Description
and
Title
. I don't touch
Id
or
CreatorUserID
during Update.

View: (I have only included form controls for the properties I am editing)

<div class="form-group">
@Html.LabelFor(model => model.Title, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Title, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Title, "", new { @class = "text-danger" })
</div>
</div>

<div class="form-group">
@Html.LabelFor(model => model.Description, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Description, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Description, "", new { @class = "text-danger" })
</div>
</div>

Answer

The reason for the exception is that you are not generating a form control for property CreatorUserID so when you submit, the DefaultModelBinder initializes and instance of Comment sets the value of Id from the route values and the values of Title and Description from the form values. But CreatorUserID remains the default for Guid so when you update the database, its is looking for a User with Id = {00000000-0000-0000-0000-000000000000} which does not exist.

You could solve this by adding a hidden input in the view for CreatorUserID so that its value is posted and bound to the model

@Html.HiddenFor(m => m.CreatorUserID)

However a far better solution that will address other issues as well is to use a view model (refer What is ViewModel in MVC?).

public class CommentVM
{
    public Guid? ID { get; set; }
    public string Title { get; set; }
    public string Descrption { get; set; }
}

and decorate the properties with view specify display and validation attributes as required. Then you view needs to be changed to use the view model rather than the data model

@model yourAssembly.CommentVM

and the controller methods become

public ActionResult Edit(Guid id) { // should not be nullable
    Comment comment = db.Comment.Find(id);
    if (comment == null) {
        return HttpNotFound();
    }
    // Initialize a view model
    CommentVM model = new CommentVM()
    {
        ID = id,
        Title = comment.Title,
        Description = comment.Description,
    }
    return View(model); // return view model
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(CommentVM model) { // BindAttribute not required
    if (!ModelState.IsValid) {
        return View(model);
    }
    // Get the data model
    Comment comment = db.Comment.Where(x => x.Id == model.ID).FirstOrDefault();
    // Update its properties
    comment.Title = model.Title;
    comment.Description = model.Description;
    // Save and redirect
    db.SaveChanges();
    return RedirectToAction("Index");
}

Note that you do not need a hidden input for the ID property because that will be bound from the url's route values (so long as the property is named ID and your using the default route - url: "{controller}/{action}/{id})