Entity Framework Code First "join table"

Question

I am working with Entity Framework Code First.

I have an Entity Called Notes

I also have other Entities such as BusinessPartners Opportunities WorkOrders

All of these entities may have notes.

What is the best way to model this

1.) in the notes table have optional foreign keys to Business partners, Opportunities, and workorders. Then just set the optional key to which the note is related

2.) have intermediate tables such as BusinessPartnerNotes, with two field BusinessPartnerId and NoteId

It should be mentioned that a note is never going to be related to two entities at the same time.

Any help or suggestions would be appreciated.

Why do not just have a foreign key in Business partners, Opportunities and workorders to Notes ? — Maxim Fleitling, Jan 09 '16 at 21:32
"Why do not just have a foreign key in Business partners, Opportunities and workorders to Notes ? " This would only allow one note per entity — user1287453, Jan 09 '16 at 21:49
i guess the thing is that this is a one-to-many relationship however the "one" side may be a Businesspartners, opportunities, or workorders. My #2 would basically model on the database side as a many to many but model in entityframework as one to many. option #1 would be consistent one to many in both EF and DB however multiple "optional" FK would be needed in the notes table — user1287453, Jan 09 '16 at 21:51
Possible duplicate: http://stackoverflow.com/q/8895806/861716 — Gert Arnold, Jan 09 '16 at 22:55

score 1 · Accepted Answer · answered Jan 09 '16 at 21:57

Given your description of the cardinalities, and assuming Notes for BusinessPartners have the same format of Notes for Opportunities, I'd go with the simplest approach (option 1. in your list).

class Note
{
    public int Id { get; set; }
    public string Content { get; set; }
}
class BusinessPartner
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Note> Notes { get; set; }
}
class Opportunity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Note> Notes { get; set; }
}

Which should generate the following tables:

Notes
  Id
  Content
  BusinessPartner_Id
  Opportunity_Id
BusinessPartners
  Id
  Name
Opportunities
  Id
  Name

That is what i was thinking as well. Well one more catch. This is currently a production application and the DB has the intermediate table. How can i model a one to many relationship using a intermediate table? Do i just use the above classes with the modelbuilder.hasMany.Withmany...? — user1287453, Jan 09 '16 at 22:41

Entity Framework Code First "join table"

1 Answers1