.NET 8 EF Core 8 MySQL外键约束失败问题求助

在.NET 8、EF Core 8.0.2环境下，使用Pomelo.EntityFrameworkCore连接MySQL数据库，需实现：

• 建立Order与OrderItems的一对多关联：Order包含多个OrderItems，OrderItems通过idOrder外键关联Order的orderId
• 在OrderController中实现添加带关联订单项的新订单接口

配置关联关系后触发外键约束失败错误，调整数据插入顺序、修改导航属性均无效。注释以下关联配置代码及导航属性后代码可正常运行：

builder.HasOne(oi => oi.Order)
       .WithMany(o => o.OrderItems)
       .HasForeignKey(oi => oi.idOrder)
       .OnDelete(DeleteBehavior.Cascade); 

实体类 #

Order类 #

public class Order
{
    [Key]
    public int orderId { get; set; }
    [Required]
    public int idUser { get; set; }
    // public virtual User User { get; set; }
    
    [Required]
    public int idOrderStatus { get; set; }
    
    //public virtual OrderStatus OrderStatus { get; set; }
    
    public virtual ICollection<OrderItems> OrderItems { get; set; } = new List<OrderItems>();
    [Required]
    public DateTime orderDate { get; set; }
}

OrderItems类 #

public class OrderItems
{
    [Key]
    public int orderItemId { get; set; }
    [Required]
    public int idProduct { get; set; }
    //public virtual Products Products { get; set; }  
    public virtual Products Product { get; set; }
    [Required]
    public int idOrder { get; set; }
    public virtual Order Order { get; set; }  
    [Required]
    public int orderItemQuantity { get; set; }
    [Required]
    public int orderPrice { get; set; }
}

配置类 #

OrderItemsConfiguration #

public class OrderItemsConfiguration : IEntityTypeConfiguration<OrderItems>
{
    public void Configure(EntityTypeBuilder<OrderItems> builder)
    {
        builder.HasKey(oi => oi.orderItemId);

        builder.HasOne(oi => oi.Order)
            .WithMany(o => o.OrderItems)
            .HasForeignKey(oi => oi.idOrder)
            .OnDelete(DeleteBehavior.Cascade); 

        builder.HasOne(oi => oi.Product)
            .WithMany(p => p.OrderItems)  
            .HasForeignKey(oi => oi.idProduct)
            .OnDelete(DeleteBehavior.Restrict); 

        builder.Property(oi => oi.idProduct).IsRequired();
        builder.Property(oi => oi.idOrder).IsRequired();
        builder.Property(oi => oi.orderItemQuantity).IsRequired();
        builder.Property(oi => oi.orderPrice).IsRequired();

        builder.ToTable("OrderItems");
    }
}

OrderConfiguration #

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(o => o.orderId);
        builder.Property(o => o.idUser).IsRequired();
        builder.Property(o => o.idOrderStatus).IsRequired();
        builder.Property(o => o.orderDate).IsRequired();
        builder.ToTable("Order");
    }
}

初始化种子数据 #

private IEnumerable<Order> BuildOrderList()
{
    DateTime localDate = DateTime.Now;
    var orderList = new List<Order>();
    var orders = new Order()
    {
        orderId = 1,
        idUser = 1,
        idOrderStatus = 1,
        orderDate = localDate,
    };
    orderList.Add(orders);
    
    localDate = DateTime.Now;
    orders = new Order()
    {
        orderId = 2,
        idUser = 2,
        idOrderStatus = 2,
        orderDate = localDate,
    };
    orderList.Add(orders);
    
    localDate = DateTime.Now;
    orders = new Order()
    {
        orderId = 3,
        idUser = 3,
        idOrderStatus = 3,
        orderDate = localDate,
    };
    orderList.Add(orders);
    return orderList;
}

private IEnumerable<OrderItems> BuildOrderItemsList()
{
    var orderItemsList = new List<OrderItems>();
    var orderItems = new OrderItems()
    {
        orderItemId = 1,
        idProduct = 2,
        idOrder = 1,
        orderItemQuantity = 3,
        orderPrice = 123,
    };
    orderItemsList.Add(orderItems);
    
    orderItems = new OrderItems()
    {
        orderItemId = 2,
        idProduct = 1,
        idOrder = 2,
        orderItemQuantity = 5,
        orderPrice = 1123,
    };
    orderItemsList.Add(orderItems);
    
    orderItems = new OrderItems()
    {
        orderItemId = 3,
        idProduct = 3,
        idOrder = 3,
        orderItemQuantity = 12,
        orderPrice = 1523,
    };
    orderItemsList.Add(orderItems);
    return orderItemsList;
}

fail: Microsoft.EntityFrameworkCore.Update[10000]
数据库中保存上下文类型为'ReOrderlyWeb.SQL.Data.ReOrderlyWebDbContext'的更改时发生异常。
Microsoft.EntityFrameworkCore.DbUpdateException: 保存实体更改时发生错误。有关详细信息，请参阅内部异常。
MySqlConnector.MySqlException (0x80004005): 无法添加或更新子行：外键约束失败 (`reorderlywebdb`.`orderitems`, CONSTRAINT `FK_OrderItems_Order_orderId` FOREIGN KEY (`orderId`) REFERENCES `order` (`orderId`) ON DELETE CASCADE)
在 MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior,
.......
.......
在 Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Microsoft.EntityFrameworkCore.DbUpdateException: 保存实体更改时发生错误。有关详细信息，请参阅内部异常。
MySqlConnector.MySqlException (0x80004005): 无法添加或更新子行：外键约束失败 (`reorderlywebdb`.`orderitems`, CONSTRAINT `FK_OrderItems_Order_orderId` FOREIGN KEY (`orderId`) REFERENCES `order` (`orderId`) ON DELETE CASCADE)

1. 修正种子数据插入顺序 #

EF Core不会自动处理种子数据的依赖顺序，需确保先插入Order数据，再插入OrderItems数据。在OnModelCreating中明确指定顺序：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 先应用所有实体配置
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(ReOrderlyWebDbContext).Assembly);
    
    // 先添加Order种子数据
    modelBuilder.Entity<Order>().HasData(BuildOrderList());
    // 再添加OrderItems种子数据
    modelBuilder.Entity<OrderItems>().HasData(BuildOrderItemsList());
}

2. 新增订单时正确关联导航属性 #

在OrderController中创建新订单时，无需手动设置idOrder，通过导航属性关联订单项，让EF Core自动处理外键：

[HttpPost]
public async Task<IActionResult> CreateOrder(OrderCreateDto dto)
{
    var order = new Order
    {
        idUser = dto.IdUser,
        idOrderStatus = dto.IdOrderStatus,
        orderDate = DateTime.Now,
        OrderItems = dto.OrderItems.Select(item => new OrderItems
        {
            idProduct = item.IdProduct,
            orderItemQuantity = item.Quantity,
            orderPrice = item.Price
        }).ToList()
    };

    _context.Orders.Add(order);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(GetOrder), new { id = order.orderId }, order);
}

3. 验证数据库表结构与配置一致性 #

• 确认Order表的orderId是自增主键（若使用自动生成主键），或种子数据中的orderId与OrderItems的idOrder完全匹配
• 检查MySQL表名大小写：由于MySQL默认区分表名大小写，确保Order表的实际名称与OrderConfiguration中指定的"Order"一致

内容的提问来源于stack exchange，提问作者Michał Krawiec