插入ExpRegistry时如何更新InHandOrder匹配OrderNo的行而非新增

问题背景 #

我有两张数据表InHandOrder和ExpRegistry，二者共享OrderNo字段。需求是向ExpRegistry插入数据时，检查InHandOrder中是否存在OrderNo匹配的行，若存在则更新该行的InvoiceNo、ShipQty、InvoiceValue字段。但当前代码没有更新匹配行，而是新增了一行。

原控制器代码 #

[HttpPost]
[ProducesResponseType(StatusCodes.Status201Created)]
[ProducesResponseType(StatusCodes.Status202Accepted)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public IActionResult Create(ExpRegistry obj)
{
    if (ModelState.IsValid)
    {
        _unitOfWork.ExpRegistry.Add(obj);
        _unitOfWork.Save();

        IEnumerable<InHandOrder> objInHandOrderList = _unitOfWork.InHandOrder.GetAll().ToList();

        var checkOrderNo = objInHandOrderList.FirstOrDefault(i => i.OrderNo == obj.OrderNo);
        _unitOfWork.InHandOrder.Add(new InHandOrder()
            {
                InvoiceNo = obj.InvoiceNo,
                ShipQty = obj.ShipQty,
                InvoiceValue = obj.InvoiceValue
            });

        _unitOfWork.Save();

        return CreatedAtAction("GetDetails", new { id = obj.Id }, obj);
    }

    _logger.LogError($"Something went wrong in the {nameof(Create)}");
    return StatusCode(500, "Internal Server Error, Please Try Again Later!");
}

InHandOrder实体类 #

public class InHandOrder
{
    [Key()]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    // Purchase Order
    public int? ContractListId { get; set; }

    [ForeignKey("ContractListId")]
    [ValidateNever]
    public ContractList? ContractList { get; set; }

    public string? OrderNo { get; set; }

    public int? StyleListId { get; set; }

    [ForeignKey("StyleListId")]
    [ValidateNever]
    public StyleList? StyleList { get; set; }

    public int? SeasonListId { get; set; }

    [ForeignKey("SeasonListId")]
    [ValidateNever]
    public SeasonList? SeasonList { get; set; }

    public DateTime? Shipment { get; set; }
    public int? TotalQuantity { get; set; }
    public decimal? UnitPrice { get; set; }
    public decimal? PoValue { get; set; }

    public int? CountryListId { get; set; }

    [ForeignKey("CountryListId")]
    [ValidateNever]
    public CountryList? CountryList { get; set; }

    // Export Register
    public string? InvoiceNo { get; set; }
    public int? ShipQty { get; set; }

    public decimal? InvoiceValue { get; set; }
    public decimal? ShortValue { get; set; }
    public int? ShortQty { get; set; }
}

ExpRegistry实体类 #

public class ExpRegistry
{
    [Key]
    public int Id { get; set; }
    public int? PoId { get; set; }

    [DisplayName("Exp No")]
    public string? ExpNo { get; set; }
    [DisplayName("UNIT")]
    public int? UnitListId { get; set; }
    [ForeignKey("UnitListId")]
    [ValidateNever]
    public UnitList? UnitList { get; set; }

    [Display(Name = "DATE")] //EXP ISSUE DATE
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    public DateTime? ExpIssueDate { get; set; }

    [DisplayName("ORDER NO")]
    [ValidateNever]
    public string? OrderNo { get; set; }

    [DisplayName("INVOICE NO")]
    [ValidateNever]
    public string? InvoiceNo { get; set; }

    [DisplayName("SHIPPED QTY")]
    [ValidateNever]
    public int? ShipQty { get; set; }

    [DisplayName("VALUE")]
    [DisplayFormat(DataFormatString = "{0:C}", ApplyFormatInEditMode = false)]
    [ValidateNever]
    public decimal? InvoiceValue { get; set; }
}

错误原因 #

1. 原代码获取到匹配的checkOrderNo后，未对该实体进行修改，反而直接调用Add方法创建新的InHandOrder实例，EF Core会将其识别为新实体并执行插入操作。
2. 查询逻辑低效：加载全部InHandOrder数据再过滤，浪费数据库资源。

修正后的控制器代码 #

[HttpPost]
[ProducesResponseType(StatusCodes.Status201Created)]
[ProducesResponseType(StatusCodes.Status202Accepted)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public IActionResult Create(ExpRegistry obj)
{
    if (ModelState.IsValid)
    {
        // 插入ExpRegistry数据
        _unitOfWork.ExpRegistry.Add(obj);
        _unitOfWork.Save();

        // 直接通过OrderNo查询匹配的InHandOrder，避免加载全部数据
        var matchedOrder = _unitOfWork.InHandOrder.GetAll()
            .FirstOrDefault(i => i.OrderNo == obj.OrderNo);

        if (matchedOrder != null)
        {
            // 更新匹配行的指定字段
            matchedOrder.InvoiceNo = obj.InvoiceNo;
            matchedOrder.ShipQty = obj.ShipQty;
            matchedOrder.InvoiceValue = obj.InvoiceValue;
            
            // EF Core自动跟踪实体变更，无需调用Add，直接保存即可
            _unitOfWork.Save();
        }

        return CreatedAtAction("GetDetails", new { id = obj.Id }, obj);
    }

    _logger.LogError($"Something went wrong in the {nameof(Create)}");
    return StatusCode(500, "Internal Server Error, Please Try Again Later!");
}

关键说明 #

• 利用EF Core的变更跟踪机制：从数据库查询出的matchedOrder会被上下文跟踪，修改其属性后，调用Save()时EF Core会自动生成UPDATE语句更新数据库，而非插入新行。
• 优化查询逻辑：直接通过OrderNo过滤，减少数据库IO开销。
• 若需在OrderNo不存在时执行额外逻辑（如提示用户或新增行），可在else分支添加对应代码。

内容的提问来源于stack exchange，提问作者Masum Rayhan