0

I am working on an existing project with a database containing a table of "Contacts" : Id, FirstName, LastName, MobileNumber etc.

I am now wanting to implement a table whereby I can link pairs of Contacts together. I have built a class called contactMatches: Id, contact1, contact2 where contact1 and contact2 should link to the contactId column as a FK. I'm looking at creating the data relationships for the table representing the class using entity frameworks Modelbuilder.Entity functionality.

I'm relatively new to database design and entity framework. Can this be done within one linking table or do I need to go down a different route?

The other route I have considered is creating the table just storing the contact1 and contact2 as integers as it'll only be used when cross-referencing the table other tables within the db. This seems bad because the contactMatches table could potentially contain contactId's that didn't exist and so would need safeguarding against that....

What is the best way to do what I want, any ideas?

Thanks, JK

elixenide
  • 44,308
  • 16
  • 74
  • 100
JonnyKnottsvill
  • 1,123
  • 2
  • 16
  • 39
  • can you provide more information regarding contact1, contact2, contactmatches, etc. classes. I am not clear about their purpose, and their all proerties. I see the possibility of inheritance, but I want to make sure first. You can directly post their class definitions here. – renakre Apr 08 '15 at 21:25
  • There is no contact1 or contact2 class sorry if that was confusing. There is one Contact class: `int Id, string first, string last, string mobileNumber, string email, deliverymethods delivery method`. Delivery method refers to the preference of email or Mobile. then there is contactMatch: `int Id, Contact contact1, Contact contact2, Message message`.......Contact table already exists in DB, Messages are just text and a target location and have a table within the DB also. – JonnyKnottsvill Apr 09 '15 at 09:06

1 Answers1

0

You can do that in overriden OnModelCreating method

modelBuilder.Entity<Contacts>()
            .HasMany(e => e.ContactMatches1)
            .WithRequired(e => e.Contact1)
            .HasForeignKey(e => e.Id);

        modelBuilder.Entity<Contacts>()
            .HasMany(e => e.ContactMatches2)
            .WithRequired(e => e.Contact2)
            .HasForeignKey(e => e.Id);

Provided that ContactMatches1 + 2 are your navigation properties in the Contacts class

public virtual ICollection<ContactMatches> ContactMatches1 { get; set; }
Martin Vich
  • 1,062
  • 1
  • 7
  • 22
  • Okay....does it matter that not every contact would have a ContactMatch? I will try this out. – JonnyKnottsvill Apr 08 '15 at 16:23
  • It doesn't since the navigation property in Contact is collection so every contact can have 0..N relation with ContactMatch.Contact1 – Martin Vich Apr 08 '15 at 16:29
  • What are the implications with regards to us already having a live populated contacts table? Will the structural changes to the contact model have any effect? – JonnyKnottsvill Apr 08 '15 at 16:45
  • If you're using code first then it might create an extra migration depending on how the DbContext looked before this change – Martin Vich Apr 08 '15 at 16:54
  • I understand that a migration would need to be added, but what about the data which exists in the contact table? What would the contactMatches Collection look like in the db? I'm just confused as to how this will physically look in the db. Will EF create a ContactMatches table? – JonnyKnottsvill Apr 09 '15 at 09:02
  • It should generaly look like standard M:N relation table. Obviously it depends on how the class would look like but I don't think there is much room to be creative – Martin Vich Apr 11 '15 at 11:33
  • Thankyou very much for the replies. How would I define a relationship if the contact matches model also included a message object? `Message: int Id, string Text, Contact Recipient`. Would it be like `modelBuilder.Entity().HasMany(e => e.contactMatch).WithRequired(e => e.message).HasForeignKey(e => e.Id)` ? where contactMatch is a collection in the message class again? – JonnyKnottsvill Apr 14 '15 at 10:55
  • I have tested this and get an error when adding the migration. `Contact_ContactMatches1_Target: : Multiplicity is not valid in Role 'Contact_ContactMatches1_Target' in relationship 'Contact_ContactMatches1'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.` – JonnyKnottsvill Apr 14 '15 at 13:29
  • Worked great. thanks. I just had to remove the .HasForeignKey() on the model building statement. The FK's were still created, not sure what the issue was – JonnyKnottsvill Apr 17 '15 at 10:23