Entity Framework - table needs two foreign keys pointing at same table

Question

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

Answer 1

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; }