SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint

Question

Hello and thank you in advance for taking time to read this post. I am new to EF Core and I am attempting to setup the following database connection where I have scaffold-ed controllers from the models.

namespace VotingWebApp.Models
{
  public class QuestionObject
  {
    public int QuestionObjectID { get; set; }
    public string Question { get; set; }
    public string AnswerA { get; set; }
    public string AnswerB { get; set; }
    public int AnswerAVote { get; set; }
    public int AnswerBVote { get; set; }
    public int GroupItemID { get; set; }
    public ICollection<ResponseItem> ResponseItems { get; set; }
    public GroupItem GroupItem { get; set; }
  }
}


namespace VotingWebApp.Models
{
    public class GroupItem
    {
        public int GroupItemID { get; set; }
        public string GroupName { get; set; }
        public int MemberCount { get; set; }
        public int Host { get; set; }
        public ICollection<GroupUserLink> GroupUserLinks { get; set; }
        public ICollection<QuestionObject> QuestionItems { get; set; }
    }
}

I receive the following error when I attempt to create a new QuestionObject in the database (Even when I supply an existing GroupItem key).

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_QuestionObject_GroupItem_GroupItemID". The conflict occurred in database "aspnet-VotingWebApp-02945df4-961a-4b8f-8999-19aa61dfd02e", table "dbo.GroupItem", column 'GroupItemID'.

I am not sure how to go about solving this conflict. I have read other posts and they mainly highlight how the person is not supplying an existing key. I am new to EF Core so any help would be appreciated.

    // GET: QuestionObjects/Create
    public async Task<IActionResult> CreateQuestion(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var groupItem = await _context.GroupItem.SingleOrDefaultAsync(m => m.GroupItemID == id);
        if (groupItem == null)
        {
            return NotFound();
        }
        QuestionObject questionObject = new QuestionObject();
        questionObject.GroupItemID = groupItem.GroupItemID;
        return View(questionObject);
    }

    // POST: QuestionObjects/Create
    // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> CreateQuestion([Bind("AnswerA,AnswerAVote,AnswerB,AnswerBVote,GroupID,Question")] QuestionObject questionObject)
    {
        if (ModelState.IsValid)
        {
            _context.Add(questionObject);
            await _context.SaveChangesAsync();
            return RedirectToAction("Index");
        }
        return View(questionObject);
    }

I have added the code for the insertion. This is within the GroupItem controller.

The answer probably lies in how you're supplying that existing GroupItem key. How are you doing that? — cf_en, Feb 10 '17 at 17:22
Also, make sure you have an input or hidden field for GroupItemID or it will come back null on the post. Debug and check the value at the top of the post. — Steve Greene, Feb 10 '17 at 18:40
Also also you don't have GroupItemID in your bind parameters. — Steve Greene, Feb 10 '17 at 18:48

score 1 · Accepted Answer · answered Feb 10 '17 at 19:03

This looks more like a binding problem. First, your white list of included fields does not have GroupItemID so add it (unless GroupId was a typo):

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> CreateQuestion([Bind(Include="AnswerA,AnswerAVote,AnswerB,AnswerBVote,GroupID,GroupItemID,Question")] QuestionObject questionObject)
{
    if (ModelState.IsValid)
    {
        _context.QuestionObjects.Add(questionObject);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(questionObject);
}

Next, you need to either have an input field or hidden field on your view for GroupItemID or it will come back null

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

Thank you very much Steve this solution works perfectly. – Calst Feb 11 '17 at 12:21 — Calst, Feb 11 '17 at 12:21

Shahid Syed · Answer 2 · 2017-02-10T18:49:48.057

0

Try out populating GroupItem object on QuestionObject to help EF understand FK relationship.

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> CreateQuestion([Bind("AnswerA,AnswerAVote,AnswerB,AnswerBVote,GroupID,Question")] QuestionObject questionObject)
{
    if (ModelState.IsValid)
    {
        questionObject.GroupItem = _context.GroupItem.Single(m => m.GroupItemID == questionObject.GroupItemID)
        _context.Add(questionObject);
        await _context.SaveChangesAsync();
        return RedirectToAction("Index");
    }
    return View(questionObject);
}

edited Feb 10 '17 at 18:49

answered Feb 10 '17 at 18:36

Shahid Syed

589
3
15

Not necessarily. You can do it with an exposed FK: http://stackoverflow.com/questions/13148585/entity-framework-add-child-entity – Steve Greene Feb 10 '17 at 18:42
Agreed. It should work with exposed FK. But Calst suggested that exposed FK is not working for him so in his case populating GroupItem object seems to be the only workaround. – Shahid Syed Feb 10 '17 at 18:48
Most likely a binding problem where GroupItemID is coming back null (0). Debug would verify that. Bind list needs GroupItemID added and view needs an input or hidden field for it. – Steve Greene Feb 10 '17 at 18:52

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint

2 Answers2