Many to Many relationship - Entity Framework Core

Question

I have two tables in my database:

• Contact
• ContactRoles

The join table is Contact_ContactRole.

I have defined them in code as

• Contact.cs
• ContactRoles.cs
• Contact_ContactRole.cs

I have defined a virtual property as follows:

public class Contact
{
    public Contact()
    {
        
    }
    public int ID { get; set; }
    public int ClientID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Mobile { get; set; }
    public int IsDefault { get; set; }
    public bool IsActive { get; set; }
    public bool IsInvoiceEmail { get; set; }
    public DateTime CreationDate { get; set; }
    public bool IsSubConsultant { get; set; }
    public string Notes { get; set; }
    //[NotMapped]
    public virtual ICollection<ContactRoles> ContactRoles { get; set; }
}

public class ContactRoles
{
    public int ID { get; set; }
    public string Name { get; set; }
    
    public virtual ICollection<Contact> Contacts { get; set; }
}

public class Contact_ContactRole
{
    public int ContactID { get; set; }
    public int ContactRoleID { get; set; }

    //public Contact Contact { get; set; }
    //public ContactRoles ContactRole { get; set; }
}

AuthContext.cs

protected override void OnModelCreating(ModelBuilder builder)
{
    builder
     .Entity<Contact>()
     .ToTable("Contact");

    builder
     .Entity<Contact_ContactRole>()
     .ToTable("Contact_ContactRole");

    builder
      .Entity<ContactRoles>()
      .ToTable("ContactRoles");

    builder.Entity<Contact>()
      .HasMany(p => p.ContactRoles)
      .WithMany(p => p.Contacts)
      .UsingEntity(j => j.ToTable("Contact_ContactRole"));

   builder
      .Entity<Contact_ContactRole>()
      .HasKey(ccr => new { ccr.ContactID, ccr.ContactRoleID });
}

I am getting an error:

Cannot use table 'Contact_ContactRole' for entity type 'Contact_ContactRole' since it is being used for entity type 'ContactContactRoles (Dictionary<string, object>)' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'Contact_ContactRole' on the primary key properties and pointing to the primary key on another entity typed mapped to 'Contact_ContactRole'.'

If I comment the code:

 builder.Entity<Contact>()
            .HasMany(p => p.ContactRoles)
            .WithMany(p => p.Contacts)
            .UsingEntity(j => j.ToTable("Contact_ContactRole"));

I get the following error:

Invalid object name 'ContactContactRoles'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Answer 1

Try this

   public class Contact
    {
        public Contact()
        {
            
        }
      
        public int ContactId { get; set; }  //PK
        public int ClientID { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Mobile { get; set; }
        public int IsDefault { get; set; }
        public bool IsActive { get; set; }
        public bool IsInvoiceEmail { get; set; }
        public DateTime CreationDate { get; set; }
        public bool IsSubConsultant { get; set; }
        public string Notes { get; set; }
        [ForeignKey("ContactId")]
         public virtual ICollection<Contact_ContactRole> ContactContactRoles { get; set; }
    }
    
    public class ContactRoles
    {
       
        public int ContactRoleId { get; set; }  //PK
        public string Name { get; set; }
        
        [ForeignKey("ContactRoleId")]
         public virtual ICollection<Contact_ContactRole> ContactContactRoles { get; set; }
    }
    
    public class Contact_ContactRole
    {
        public int ContactId { get; set; }
        public Contact Contact { get; set; }
    
        public int ContactRoleId { get; set; }
        public ContactRoles ContactRole { get; set; }
    }

also I believe that Contact_ContactRole needs a Primary Key. Currently the data annotations in EF Core does not have the option of creating Composite Primary Key.

Hence, we may have to fall back to Fluent API to create the Composite Key.

   modelBuilder.Entity<Contact_ContactRole>()
                 .HasKey(e => new { e.ContactId, e.ContactRoleId });