ASP.NET提交表单时Foreign Key MERGE语句错误解决

定义了Poll和PollOption两个模型，PollOption包含外键PollId。在Create动作中尝试同时添加一个Poll及对应的PollOptions列表时，触发外键约束冲突错误：

The MERGE statement conflicted with the FOREIGN KEY constraint "FK_PollOptions_Polls_PollId"...

推测原因是Poll的Id在调用Save方法前未生成，导致PollId为0或null。相关代码如下：

Models #

public class Poll
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Question { get; set; } = "";
}

public class PollOption
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Option { get; set; } = "";

    [Required]
    public int PollId { get; set; }

    [ForeignKey("PollId")]
    [ValidateNever]
    public Poll Poll { get; set; }
}

ViewModel #

public class PollVM
{
    public Poll Poll { get; set; }

    [ValidateNever]
    public List<PollOption> PollOptions { get; set; }
}

Action #

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(PollVM pollVM) 
{
    _unitOfWork.Poll.Add(pollVM.Poll);

    foreach (var option in pollVM.PollOptions)
    {
        _unitOfWork.PollOption.Add(option);
    }

    _unitOfWork.Save();

    return View(pollVM);
}

View #

@model PollVM
<div class="index-container">
    <div class="subcontainer">
        <h1 class="title">Poll Creator</h1>
        <h2 class="subtitle">Complete the form below to create your poll!</h2>
        <form method="POST" asp-action="Create">
            <input asp-for="Poll.Id" type="hidden" />
            <div class="form-container">
                <div class="form-input">
                    <label asp-for="Poll.Question">Question:</label>
                    <input asp-for="Poll.Question" type="text" placeholder="Type the poll question here" required>
                </div>
                <div class="form-input">
                    <div class="form-input form-options">
                        <label>Answer Options:</label>
                        <div class="form-option">
                            <input asp-for="PollOptions[0].Id" type="hidden" />
                            <input asp-for="PollOptions[0].PollId" type="hidden" />
                            <input asp-for="PollOptions[0].Option" type="text" placeholder="Option 1" required>
                            <img src="~/images/icons/cross.svg" alt="remove-option" width="30px" class="remove-option" onclick="removeOption(this)">
                        </div>
                        <div class="form-option">
                            <input asp-for="PollOptions[1].Id" type="hidden" />
                            <input asp-for="PollOptions[1].PollId" type="hidden" />
                            <input asp-for="PollOptions[1].Option" type="text" placeholder="Option 2" required>
                            <img src="~/images/icons/cross.svg" alt="remove-option" width="30px" class="remove-option" onclick="removeOption(this)">
                        </div>
                    </div>
                    <button type="button" class="button side-button icon-button">Add Option <img src="~/images/icons/plus.svg" alt="add-option" width="25px"></button>
                </div>
                <button type="submit" class="button submit-button">Create!</button>
            </div>
        </form>
    </div>
</div>

@section Scripts {
    <script src="~/js/answer-options.js" asp-append-version="true"></script>
}

仅添加Poll时可正常执行，但需要同时添加PollOptions，求解决方法。

有两种可靠的解决思路：

方法一：分两次保存，先获取Poll的Id #

先将Poll保存到数据库，此时EF会自动生成并填充Poll的Id字段，之后给每个PollOption设置对应的PollId，再保存选项：

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(PollVM pollVM) 
{
    _unitOfWork.Poll.Add(pollVM.Poll);
    // 第一次保存，获取自动生成的Poll.Id
    _unitOfWork.Save();

    foreach (var option in pollVM.PollOptions)
    {
        option.PollId = pollVM.Poll.Id;
        _unitOfWork.PollOption.Add(option);
    }
    // 第二次保存所有关联选项
    _unitOfWork.Save();

    return RedirectToAction(nameof(Index)); // 建议重定向避免重复提交
}

方法二：利用EF导航属性自动关联（推荐） #

通过给Poll模型添加导航属性，让EF自动处理父子实体的关联和外键赋值，无需手动设置PollId：

1. 修改Poll模型 #

添加PollOptions导航属性，明确一对多关系：

public class Poll
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Question { get; set; } = "";

    // 导航属性：一个Poll对应多个PollOption
    public ICollection<PollOption> PollOptions { get; set; } = new List<PollOption>();
}

2. 修改Create动作 #

将PollOptions关联到Poll的导航属性中，EF会自动处理插入顺序和外键赋值：

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(PollVM pollVM) 
{
    // 将选项绑定到Poll的导航属性
    pollVM.Poll.PollOptions = pollVM.PollOptions;
    _unitOfWork.Poll.Add(pollVM.Poll);
    // 一次保存即可，EF会先插入Poll，再插入关联的PollOption并自动设置PollId
    _unitOfWork.Save();

    return RedirectToAction(nameof(Index));
}

3. 优化View（可选） #

由于导航属性会自动处理外键，View中无需再传递隐藏的PollId，可以移除所有选项的PollId隐藏输入框：

<!-- 移除这类输入框 -->
<input asp-for="PollOptions[0].PollId" type="hidden" />

内容的提问来源于stack exchange，提问作者Khaled Al Hariri