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?

Don't you have an entity for the join table entries? Also I think you are misusing the `Map` method in the last line. This version is only to map foreign key column names (look at the description in Intellisense). — Slauma, Jan 16 '12 at 18:06
Slauma, did not create an entity for the join table since I've never had to previously (for straight forward many-to-may relationships with two columns). Also, the "map" code was my last attempt at all the different premutations I could think of. I'll try creating the join table entity to see what else comes up. thx. — fuzzlog, Jan 16 '12 at 18:44
Yes, for many-to-many join tables you don't need an entity. But this table has to have precisely *two* columns which form a composite key and are FKs to the related tables. No other kind of join tables is allowed. — Slauma, Jan 16 '12 at 18:49
If I call EntityD the composite key entity, does this mean that I also have to add navigation propoerties for EntityD into EntityA, EntityB and EntityC, as well as adding a navigation properties for Entiteis A,B,C into D? Sorry if this sound clueless, but EF is not my forte. thx — fuzzlog, Jan 16 '12 at 19:05
You generally only need one navigation property for an association. The second is always optional. BTW: Why don't you simply map between A and B with a normal many-to-many relation and between B and C with a normal one-to-one relation? I don't understand the need to have a third key column in this join table. — Slauma, Jan 16 '12 at 22:28
Lack of experience on the matter. I'll try the AB BC route you mentined. thx. — fuzzlog, Jan 16 '12 at 22:39
Yor mapping between B and C is already fine imo if you just remove the `.Map(...)` at the end. — Slauma, Jan 16 '12 at 22:52

score 0 · Accepted Answer · answered Jan 17 '12 at 09:44

// 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.

So, based on the information you and Slauma have provided so far, it seems that if I create a Model for my tables (including junction/join tables), and then add a DbSet entry for each of those tables in my Context class, and then I add the appropriately-related navigation properties to their respective Models, then I will not have to use the fluent API .Map(...) method of ModelBuilder.Entity() at all? Is that a correct assertion? — fuzzlog, Jan 17 '12 at 21:51
I believe you will not need entity for junction table but just for A,B and C. Otherwise your understanding is correct. — Ladislav Mrnka, Jan 17 '12 at 23:29
Thanks to both for increasing my understanding. I was bent on trying to use .Map(...) since that's what I've seen in most examples. Creating the models for the join tables and adding the correct navigation properties in the the other models solved my problem. — fuzzlog, Jan 18 '12 at 00:48

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

1 Answers1