Is it possible to link a table FK to 2 other PK in entity framework?

Question

I have a shopping cart that is to receive items from two separate tables.

One table contains single items and the other contains Boxes of several items.

I am using an ApiController to insert the item into the cart, problem being that when I insert a Box with ID 1, the FK in Cart updates the ID to 1 but there is no indication to if it is an Item or Box.

I have tried to have multiple FK in cart table for each the Item and Box ID but Code first is giving errors about nulls in the FK. I have tried making them nullable but this causes errors when trying to join the tables for data retrieval.

What is the best practice for a relationship shown below?

Cart Model:

  public class Cart
  {
    [Key]
    public int RecordID { get; set; }
    public string CartID { get; set; }
    public int ItemID { get; set; }
    public int BoxID { get; set; }
    public int Qty { get; set; }
    public System.DateTime DateCreated { get; set; }

    public Item Item{ get; set; }
    public Box Box { get; set; }

    public string UserID { get; set; }
    public ApplicationUser User { get; set; }
}

Muhammad Waqas · Answer 1 · 2019-03-07T14:13:03.723

1

Why are you not treating box as a separate item and as a result you will have one table instead of two tables.

For example.

public class Cart
{
    [Key]
    public int RecordID { get; set; }
    public string CartID { get; set; }
    public int ItemID { get; set; }
    public int Qty { get; set; }
    public System.DateTime DateCreated { get; set; }

    public Item Item{ get; set; }

    public string UserID { get; set; }
    public ApplicationUser User { get; set; }
}
public class Item
{
    [Key]
    public int ItemID { get; set; }
    .
    .
    .
}

In this case you will be able to assign different Id to individual items and box items.

You can also use the Qty property in case of same items in a box.

edited Mar 07 '19 at 14:13

answered Mar 07 '19 at 14:05

Muhammad Waqas

26
2

I am trying to keep Box and Items in separate tables to make future analysis easier. Because 1 box could contain multiple different items. For example BoxID1 contains: ItemID1 x5, ItemID2 x1, ItemID3 x1 etc – DarrenB Mar 07 '19 at 14:18
then add a `ICollection` to your Item entity. – tschmit007 Mar 07 '19 at 14:24
In this case I think you will need to use Table per Type (TPT) inheritance approach. e.g. Add a Parent class which will abstract and both Item and Box class will inherit from the Parent class. Using TPT approach you can solve this problem. For more detailed information, see this article. https://entityframework.net/tpt – Muhammad Waqas Mar 07 '19 at 14:35

Is it possible to link a table FK to 2 other PK in entity framework?

1 Answers1