One to many relationships with Join table using EF First code

Question

I have a question on how to set up a one-to-many relationship with a join table using the Code First fluent API. I have a company, the contact object has a common address object, as shown below.

Class Address { public int AddressId ..... } Class Company { public int CompanyId ...... public virtual ICollection<Address> Address } Class Contact { public int ContactID ....... public virtual ICollection<Address> Address } 

My expected database structure will be

 Company Table CompanyId PK NOT NULL ..... Contact Table ContactId PK NOT NULL ..... Address Table AddressId PK NOT NULL ..... CompanyAddress Table CompanyId NOT NULL AddressId NOT NULL ContactAddress Table ContactId NOT NULL AddressId NOT NULL 

I was able to achieve this using the API below

 modelBuilder.Entity<Company>() .HasMany(c => c.Address) .WithMany() .Map(m => { m => m.MapLeftKey("CompanyId") .MapRightKey("AddressId") .ToTable("CompanyAddress")}); modelBuilder.Entity<Contact>() .HasMany(c => c.Address) .WithMany() .Map(m => { m => m.MapLeftKey("ContactId") .MapRightKey("AddressId") .ToTable("ContactAddress")}); 

but EF starts looking at the company and associates with the many-to-many relationship, and when I try to delete the company or contacts, it does not delete the corresponding address record (since EF considers them as many for many), how can I determine this type of relationship using Cascaded EF. I searched for more than three days and was surprised that no one spoke or raised scripts of this type, so I wonder if my approach is wrong or the answer is very trivial.

Answer 1

You cannot have this with many for many (this is what you create - and what you describe).

When you delete a company / contact, the entries in the join table are deleted.

You can simplify this and just do it in your config (delete everything you have):

 modelBuilder.Entity<Company>() .HasMany(c => c.Address) .WithOptional() .WillCascadeOnDelete(true); modelBuilder.Entity<Contact>() .HasMany(c => c.Address) .WithOptional() .WillCascadeOnDelete(true);