Many-To-Many relationship in EF with a datafield in the Association table
Good Afternoon all,
I have three tables arranged to make a many to many relationship (Contacts, PurchaseOrder, and PO_Contact). The PurchaseOrder table will eventually represent each purchaseorder (only one instance of each). Each Customer can be associated with any number of PurchaseOrders and each Purchase Order may be associated with any number of Contacts. However for each Contact, there can be a Purchase Order that is the Primary PO for that Contact.
I am trying to figure out how I can add this to my EntityModel and I am coming up short. Eventually I want to have a collection of PO objects with one depicted as the Primary, but I am not sure how to effect that. Anyone have experience with this? Please find below the database diagram for the affected tables.
Well the issue with junction tables is that Entity Framework does not have to map them if they contain only the FK's of the adjoining tables, but because you have that IsPrimary field, it needs to be represented on the model.
Eventually I want to have a collection of PO objects with one depicted as the Primary
Yes, as i said above, you're going to have to physically map the PO_Contact table, meaning your queries could end up like this:
var contact = db.Contacts.SingleOrDefault(x => x.ContactId == 1); var ordersForContact = contact.PO_Contacts.PurchaseOrders.ToList();
var order = db.PurchaseOrders.SingleOrDefault(x => x.POID == 1); var contactsForOrder = order.PO_Contacts.Contacts.ToList();
But to find out "which one is primary"? (and there could be more than one), you'll need another filter.
This could be an example query:
var primaryOrdersForConactOne = db.Contacts .Where(c => c.ContactID == 1) .Where(c => c.PO_Contacts.IsPrimary) .ToList();
That should work.
However, if your positive there could only be one primary order for any given customer, you could setup a form of inheritance (TPH, TPT) and make that field the discriminator, so you end up with seperate "PrimaryOrder" and "SecondaryOrder" entities, deriving from the base "PurchaseOrder" class.