Entity related to another many-to-many relationship RRS feed

  • Question

  • User306743125 posted


    Using EF Core 5, which allows for many-to-many relationships. EF 5 basically creates the join tables for you.

    public class Buyer
    public int Id {get;set;
    public string BuyerName {get;set;}
    public ICollection<Supplier>Suppliers {get;set;}
    public class Supplier
    public int Id {get;set;}
    public string SupplierName {get;set;}
    public ICollection<Buyer>Buyers {get;set;}
    public class Invoice
    public int Id {get;set;}
    public string InvoiceNumber {get;set;}
    public decimal InvoiceAmount {get;set;}
    public ICollection<BuyerSupplier>BuyerSupplier {get;set;}

    I have 3 entities: Buyer, Supplier and Invoice.
    Buyers and Suppliers have a many-to-many relationship. 
    Each Invoice will have 1 Buyer and 1 Supplier...But only if the selected Buyer and Supplier is related.
    It is easy enough to have a cascading dropdown on the Invoice Create View to only display Buyer of the selected Supplier, thus forcing the user to select a Buyer which is related to a Supplier.

    For example: Supplier 1 delivers to Buyer 1 and 2
    But Supplier 2 only delivers to Buyer 1
    An Invoice with Buyer 2 selected should not allow a Supplier of 2

    But I am worried that, that there is no FK in the database to enforce the requirement. If, for example, I import Invoices from file nothing will force the given Suppliers and Buyers to be related.

    I was thinking that the Invoice entity should not be related to the Buyer and Supplier entities, but should rather be related to BuyerSupplier entity (the Join table of the many-to-many relationship). But that seems to bring a whole lot of other complications.

    Anyone advice?

    Sunday, February 21, 2021 11:49 AM

All replies

  • User1120430333 posted

    My advise to you is work it out with a  DBA tool like SSMS using tsql for CRUD with the tables. If you can't work it out with SSMS and using  tsql, its not going to work with EF.

    Sunday, February 21, 2021 4:02 PM