EF 4.1 Fluent API. How to map two entities while using an existing join table which has three id columns?

Question

I have three entities (EntityA, EntityB, EntityC) in code and their respective tables (TableA, TableB, TableC) in the database. I also have an existing join table that has three ID columns(TableA_ID, TableB_ID, TableC_ID).

In code, the entities are related as follows:

MODELS:
public class EntityA
{
   public Guid EntityA_ID { get; set }
   .....
   // Each EntityA can be associated with 0 or Many EntityB
   public virtual ICollection<EntityB> EntityBCollection { get; set; }
}

public class EntityB
{
   public Guid EntityB_ID { get; set; }
   .....
   // Each EntityB can be associated with 0 or Many EntityA
   public virtual ICollection<EntityA> EntityACollection { get; set; }

   // Each EntityB can be assocated with 0 or Many EntityC,
   // but it becomes 0 or 1 when EntityB is associated with an EntityA
   public virtual EntityC EntityC { get; set; }
}

public class EntityC
{
   public Guid EntityC_ID { get; set; }
   ......
   // Each EntityC an only be associated with a EntityB
   // an EntityC does not exist on its own
   public virtual EntityB EntityB { get; set; }
}

DATA CONTEXT:
modelBuilder.Entity<EntityB>()
                .HasOptional(entityb => entityb.EntityC)
                .WithRequired(entityc => entityc.EntityB)
                .Map(map =>
                {
                    map.ToTable("ThreeIDColumnJoinTable").MapKey(new string[]{"EntityA_ID", "EntityB_ID", "EntityC_ID"});

                });

I keep on getting the following error:

Unable to determine the principal end of an association between the types 'EntityC' and 'EntityB'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations. 

Any ideas on how I can reconfigure the mapping in the DATA CONTEXT so it will not produce an error and it will also include the relationship of EntityA that is specified in the ThreeIDColumnJoinTable?

Answer 1

// Each EntityB can be assocated with 0 or Many EntityC, but it becomes 0 or 1 when EntityB is associated with an EntityA

In such case your EntityB has wrong navigation property. It should be:

public class EntityB
{
   public Guid EntityB_ID { get; set; }
   .....
   // Each EntityB can be associated with 0 or Many EntityA
   public virtual ICollection<EntityA> EntityACollection { get; set; }

   public virtual ICollection<EntityC> EntityCCollection { get; set; }
}

You need collection of EntityC to support "Many" part. The second part of the rule cannot be enforced by database / model. It must be enforced by your application logic.

Rest of your model can be used as is. Remove that fluent mapping and you should get many-to-many relation between A and B and one-to-many relation between B and C. That is exactly what your rules states.

There is nothing like automatic many-to-many for three tables. If you need that (not your current case) you must map junction table as fourth entity and point navigation properties from other three entities to this new entity providing relational bridge.