I'm stuck with foreign key constraint fails error. .NET 8, EF Core 8.0.2

Question

I'm trying to link Order table with OrderItems table. The order holds id of OrderItems, which has a list of items assigned to the order. In OrderController, I want to have an endpoint that adds a new order with its items assigned, but to do this I need to link the relations of both tables.

I've tried to diagnose the problem by changing the order of adding items to database, with no result because I think that is the correct order of inserting, I've tried changing navigation but it doesn't work. Code builds and runs if I comment out these blocks

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

and navigation. I'm stuck on this problem and because it's really my first .NET Core project, I don't know how to progress from there, and I don't want to just comment out the relations and navigation and skip it with just more code in the OrderController class.

Additional info: database is created in mysql, connection is established using pomelo.entityframework

Parts of the code below - Order class:

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");
    }
}

InitialSeed to seed the database:

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;
}

Part of the error thrown :

fail: Microsoft.EntityFrameworkCore.Update[10000]

An exception occurred in the database while saving changes for context type 'ReOrderlyWeb.SQL.Data.ReOrderlyWebDbContext'.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

MySqlConnector.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (reorderlywebdb.orderitems, CONSTRAINT FK_OrderItems_Order_orderId FOREIGN KEY (orderId) REFERENCES order (orderId) ON DELETE CASCADE)

at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior,
.......
.......
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

MySqlConnector.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (reorderlywebdb.orderitems, CONSTRAINT FK_OrderItems_Order_orderId FOREIGN KEY (orderId) REFERENCES order (orderId) ON DELETE CASCADE)

Answer 1

The exception hints at the problem:

MySqlConnector.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (reorderlywebdb.orderitems, CONSTRAINT FK_OrderItems_Order_orderId FOREIGN KEY (orderId) REFERENCES order (orderId) ON DELETE CASCADE)

Here it is trying to associate the Order using a FK named "orderId" on the OrderItem. What I suspect is happening is that your EntityTypeConfigurations are not actually being included. These need to be associated to the DbContext in OnModelCreating, typically using modelBuilder.ApplyConfigurationsFromAssembly(). In the DbContext.OnModelCreating add:

modelBuilder.ApplyConfigurationsFromAssembly(typeof(OrderConfiguration).Assembly);

This will add all configurations deriving from the IEntityTypeConfiguration<T> in that assembly so you just need to point it at whatever assembly or assemblies that contain the configurations.

If these classes are not being loaded then EF will try to construct your model using just the attributes and filling in gaps using convention which would be using OrderId rather than your idOrder for the FK.