ASP.NET MVC主从结构中OrderID无法自动赋值致数据插入失败

SqlException: 违反 PRIMARY KEY 约束 'PK__SaleOrde__C3905BAFABA6B181'。无法在对象 'dbo.SaleOrder' 中插入重复键。重复键值为 (0)。

当前添加新订单数据时触发上述错误，核心原因是OrderID主键字段未自动生成唯一值，每次插入都使用默认值0，导致主键重复约束冲突。

以下是现有代码：

控制器代码 #

using DoAnLTHDT.Models;
using DoAnLTHDT.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Services.Description;

namespace DoAnLTHDT.Controllers
{
    public class MasterDetailController : Controller
    {
        private UserManageEntities objManageEntities;

        public MasterDetailController()
        {
            objManageEntities = new UserManageEntities();
        }

        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public JsonResult Index(SaleOrderViewModel objSaleOrderViewModel)
        {
            SaleOrder objSalesOrder = new SaleOrder();
            objSalesOrder.AddressCus = objSaleOrderViewModel.AddressCus;
            objSalesOrder.CustomerName = objSaleOrderViewModel.CustomerName;
            objSalesOrder.OrderNumber = objSaleOrderViewModel.OrderNumber;
            objManageEntities.SaleOrders.Add(objSalesOrder);
            objManageEntities.SaveChanges();

            objSalesOrder = 
                objManageEntities.SaleOrders.Single(model =>
                model.OrderNumber == objSaleOrderViewModel.OrderNumber
            );

            foreach (var item in objSaleOrderViewModel.ListOfSaleOrderDetail)
            {
                SaleOrderDetail objSaleOrderDetail = new SaleOrderDetail();
                objSaleOrderDetail.Discount = item.Discount;
                objSaleOrderDetail.ItemName = item.ItemName;
                objSaleOrderDetail.OrderID = objSalesOrder.OrderID;
                objSaleOrderDetail.Quantity = item.Quantity;
                objSaleOrderDetail.Total = item.SubTotal;
                objSaleOrderDetail.UnitPrice = item.UnitPrice;
                objManageEntities.SaleOrderDetails.Add(objSaleOrderDetail);
                objManageEntities.SaveChanges();
            }

            return Json(data: new {Success = true, Message ="Data Add"}, JsonRequestBehavior.AllowGet);
        }
    }
}

Ajax和jQuery脚本 #

<script type="text/javascript">
    $(document).ready(function () {
        $("#btnDelete").hide();
    });
    function AddSaleOrderRow() {
        $("#btnDelete").show();
        var tblBody = $("#tblSaleOrderDetails").find("tbody");
        var tblLastRow = tblBody.find("tr:last");
        var tblClone = tblLastRow.clone().find("input[type=text]").val('').end();
        tblLastRow.after(tblClone);
        $("#btnDelete").hide();
    };
    function DeleteSaleOrderRow(btnDelete) {
        $(btnDelete).closest("tr").remove();
        CalculateSubTotal();
    }
    function ResetCustomerDetails() {
        $("#txtCustomerName").val('');
        $("#txtAddessCus").val('');
        $("#txtOrderNumber").val('');
        $("#txtTotal").val('');
        var tblBody = $("#tblSaleOrderDetails").find("tbody");
        tblBody.find('tr:gt(0)').remove();

        tblBody.find("tr").each(function () {
            $(this).find("input[type=text]").val('');
        });
    }
    function CalculateSubTotal() {
        var Total = 0.0;
        var tblBody = $("#tblSaleOrderDetails").find("tbody");
        tblBody.find("tr").each(function () {
            var Quantity = $(this).find("td:eq(1) input[type=text]").val(); 
            var UnitPrice = $(this).find("td:eq(2) input[type=text]").val(); 
            var Discount = $(this).find("td:eq(3) input[type=text]").val();
            var SubTotal = (Quantity * UnitPrice) - Discount;
            Total = Total + SubTotal;
            $(this).find("td:eq(4) input[type=text]").val(SubTotal)
            $("#txtTotal").val(Total);
        });
    }
    function SaveOrderDetails() {
        ListOfSaleOrderDetail = [];
        var tblBody = $("#tblSaleOrderDetails").find("tbody");
        tblBody.find("tr").each(function () {
            var ItemName = $(this).find("td:eq(1) input[type=text]").val();
            var Quantity = $(this).find("td:eq(2) input[type=text]").val();
            var UnitPrice = $(this).find("td:eq(3) input[type=text]").val();
            var Discount = $(this).find("td:eq(4) input[type=text]").val();
            var SubTotal = (Quantity * UnitPrice) - Discount;
            ListOfSaleOrderDetail.push({
                "ItemName": ItemName,
                "Quantity": Quantity,
                "UnitPrice": UnitPrice,
                "Discount": Discount,
                "SubTotal": SubTotal,
            });

        });
        SaleOrderViewModel = {
            "OrderNumber": $("#txtOrderNumber").val(),
            "CustomerName": $("#txtCustomerName").val(),
            "AddressCus": $("#txtAddressCus").val(),
            "ListOfSaleOrderDetail": ListOfSaleOrderDetail
        };
        $.ajax({
            async: true,
            type: 'POST',
            dataType: 'JSON',
            url: '/MasterDetail/Index',
            data: JSON.stringify(SaleOrderViewModel),
            contentType: 'application/json; charset=utf-8',
            success: function (data) {
                if (data.success) {
                    alert(data.Message);
                    ResetCustomerDetails()
                }
                else {
                    alert(data.Message);
                }
            },
            error: function (data) {
                alert("loi");
            }
        });
    }
</script>

解决方案 #

1. 检查数据库表配置 #

确保dbo.SaleOrder表的OrderID字段设置为标识列（自动递增）：

• 打开SQL Server Management Studio，找到SaleOrder表
• 右键进入设计模式，选中OrderID字段
• 在列属性中，将“标识规范”设为“是”，标识增量和标识种子均设为1（默认值）

2. 确认EF模型配置 #

• 若使用Code First，在SaleOrder实体类的OrderID属性上添加标识特性：

  public class SaleOrder
  {
      [Key]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public int OrderID { get; set; }
      // 其他属性...
  }
  

• 若使用Database First，更新EDMX模型，将OrderID的StoreGeneratedPattern属性设置为Identity

3. 优化代码逻辑（可选） #

调用SaveChanges()后，EF会自动将数据库生成的OrderID赋值给objSalesOrder对象，无需再次查询，同时建议批量保存明细以提升性能：

[HttpPost]
public JsonResult Index(SaleOrderViewModel objSaleOrderViewModel)
{
    SaleOrder objSalesOrder = new SaleOrder();
    objSalesOrder.AddressCus = objSaleOrderViewModel.AddressCus;
    objSalesOrder.CustomerName = objSaleOrderViewModel.CustomerName;
    objSalesOrder.OrderNumber = objSaleOrderViewModel.OrderNumber;
    objManageEntities.SaleOrders.Add(objSalesOrder);
    objManageEntities.SaveChanges();

    foreach (var item in objSaleOrderViewModel.ListOfSaleOrderDetail)
    {
        SaleOrderDetail objSaleOrderDetail = new SaleOrderDetail();
        objSaleOrderDetail.Discount = item.Discount;
        objSaleOrderDetail.ItemName = item.ItemName;
        objSaleOrderDetail.OrderID = objSalesOrder.OrderID; // 直接使用自动生成的ID
        objSaleOrderDetail.Quantity = item.Quantity;
        objSaleOrderDetail.Total = item.SubTotal;
        objSaleOrderDetail.UnitPrice = item.UnitPrice;
        objManageEntities.SaleOrderDetails.Add(objSaleOrderDetail);
    }
    // 批量提交明细数据
    objManageEntities.SaveChanges();

    return Json(data: new {Success = true, Message ="数据添加成功"}, JsonRequestBehavior.AllowGet);
}

内容的提问来源于stack exchange，提问作者Văn Toàn 37-Nguyễn