EF: Multiple column foreign keys

Question

I have 4 entities that are needed to store details about a multi-warehouse order.

Order - The header of the order.

Warehouse - The details of the warehouse.

OrderLines - The detail about the product against the order (including which warehouse its belongs to).

OrderWarehouse - The detail about the order relating only to a single warehouse.

public class Order
{
    public int Id { get; set; }

    public virtual ICollection<OrderWarehouse> OrderWarehouses { get; set; }

    public virtual ICollection<OrderLine> OrderLines { get; set; }
}

public class Warehouse
{
    public int Id { get; set; }

    public virtual ICollection<OrderWarehouse> OrderWarehouses { get; set; }

    public virtual ICollection<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
    public int Id { get; set; }

    public int OrderId { get; set; }

    public int ProductId { get; set; }

    public int WarehouseId { get; set; }

    [ForeignKey("OrderId")]
    public virtual Order Order { get; set; }

    [ForeignKey("WarehouseId")]
    public virtual Warehouse Warehouse { get; set; }

    public virtual OrderWarehouse OrderWarehouse { get; set; } // THIS IS WHERE I AM STRUGGLING
}

public class OrderWarehouse
{
    [Key, Column(Order = 0)]
    public int OrderId { get; set; }

    [Key, Column(Order = 1)]
    public int WarehouseId { get; set; }

    [ForeignKey("OrderId")]
    public virtual Order Order { get; set; }

    [ForeignKey("WarehouseId")]
    public virtual Warehouse Warehouse { get; set; }

    public virtual ICollection<OrderLine> OrderLines { get; set; } // THIS IS WHERE I AM STRUGGLING
}

I can't seem to get the relationship defined correctly between the OrderLines and OrderWarehouse.

The key is made up of OrderId, WarehouseId.

I know this needs to be defined in the context but all my attempts throw errors.

Can someone please assist me?

Edit:

If I try this:

modelBuilder.Entity<OrderLine>()
.HasOptional(u => u.OrderWarehouse)
.WithMany()
.HasForeignKey(u => new { u.OrderId, u.WarehouseId });

I get:

OrderLine_OrderWarehouse: : Multiplicity conflicts with the referential constraint in Role 'OrderLine_OrderWarehouse_Target' in relationship 'OrderLine_OrderWarehouse'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

Ben Robinson Ben Robinson · Accepted Answer · 2014-11-04T16:24:28

The fluent mapping you have is almost correct, but you can't specify an optional relationship on non nullable properties, if you try the following it should work.

modelBuilder.Entity<OrderLine>()
.HasRequired(u => u.OrderWarehouse)
.WithMany()
.HasForeignKey(u => new { u.OrderId, u.WarehouseId });

EF: Multiple column foreign keys

1 Answers