Fluent API foreign key could not be created due to index issues-CodePudding

I am trying to create a Foreign Key with fluent API but got the following error, and honestly can not figure out how to reduced the bytes for the cluster or why it is even that high.

Introducing FOREIGN KEY constraint 'FK_CustomerOrderLine_CustomerOrder_OrderCode' on table 'CustomerOrderLine' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.
Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_CustomerOrderLine' has maximum length of 904 bytes. For some combination of large values, the insert/update operation will fail.

CustomerOrder.cs:

public class CustomerOrder
{
    public string OrderCode { get; set; }
    public string Table { get; set; }
    public OrderStatus Status { get; set; }
    public decimal Total { get; set; }
    public bool Paid { get; set; }

    public List<CustomerOrderLine> CustomerOrderLines { get; set; }
}

CustomerOrderLine:

public class CustomerOrderLine
{
    public string OrderCode { get; set; }
    public int OrderLine { get; set; }
    public string Description { get; set; }
    public int Quantity { get; set; }
}

Fluent API:

 modelBuilder.Entity<CustomerOrder>(e =>
 {
    e.HasKey(p => p.OrderCode);
    e.HasMany<CustomerOrderLine>()
    .WithOne()
    .OnDelete(DeleteBehavior.Cascade);
    e.Property(p => p.Total)
    .HasPrecision(2);
 });

 /* Customer Order Line */
 modelBuilder.Entity<CustomerOrderLine>(e =>
 {
    e.HasKey(p => new { p.OrderCode, p.OrderLine });
    e.HasOne<CustomerOrder>()
    .WithMany();
 });

CodePudding user response：

The error you are receiving is because you are configuring the relationship between CustomerOrder and CustomerOrderLine twice.

 modelBuilder.Entity<CustomerOrder>(e =>
 {
    e.HasKey(p => p.OrderCode);
    e.HasMany<CustomerOrderLine>() // here the relationship is configured for the first time
     .WithOne()
     .OnDelete(DeleteBehavior.Cascade);
    e.Property(p => p.Total)
     .HasPrecision(2);
 });

 /* Customer Order Line */
 modelBuilder.Entity<CustomerOrderLine>(e =>
 {
    e.HasKey(p => new { p.OrderCode, p.OrderLine });
    e.HasOne<CustomerOrder>() // here it is configured a second time
     .WithMany();
 });

Therefore EF assumes that there a two different relationships between CustomerOrder and CustomerOrderLine and you want both of them to cascade (which is not possible). Just remove the second configuration of the relationship and the error will go away.

And the warning you are receiving is propably because OrderCode is a string without a maximum length being configured. Adding an approprate maximum length (e.g. 100 characters) will propably solve the issue.

CodePudding user response：

a workaround would be to introduce a primary key "Id" to "CustomerOrderLine" generated by the database instead of composite key "OrderCode" & "OrderLine".