EF 4.1: Mapping a many-to-many relationship with composite keys and a shared column?

Question

I'm using EF 4.1 Code First and trying to model the following.

public class Apple
{
    public int Id {get;set;}
}

public class Banana
{
    public int AppleId {get;set;}
    public int Id {get;set;}
    public virtual ICollection<Coconut> Coconuts {get;set;}
}

public class Coconuts
{
    public int AppleId {get;set;}
    public int Id {get;set;}
    public virtual ICollection<Banana> Bananas {get;set;}
}

The database looks like this.

This is an existing schema that doesn't follow the EF conventions, so I'm using the Fluent API to map the entities to the database. The mapping looks like this.

public class BananaMapping : EntityTypeConfiguration<Banana>
{
    public BananaMapping()
    {
        HasKey(e => new { e.AppleId, e.Id })
            .ToTable("Bananas", "fruit");
        Property(e => e.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        HasMany(e => e.Coconuts)
            .WithMany(s => s.Bananas)
            .Map(l =>
                     {
                         l.ToTable("BananaCoconuts", "fruit");
                         l.MapLeftKey("AppleId", "BananaId");
                         l.MapRightKey("AppleId", "CoconutId");
                     });
    }

(Apple and Coconut are mapped too, but omitted here for brevity) If I leave it like that, it will generate a MetadataException because of the shared column. "Schema specified is not valid. Errors: (110,6) : error 0019: Each property name in a type must be unique. Property name 'AppleId' was already defined."

To get past this, I created a computed column on BananaCoconuts that simply exposes a differently named copy of AppleId and called it BananaAppleId. I left the FKs alone (obviously) and changed the mapping to look like this...

HasMany(e => e.Coconuts)
    .WithMany(s => s.Bananas)
    .Map(l =>
        {
            l.ToTable("BananaCoconuts", "fruit");
            l.MapLeftKey("BananaAppleId", "BananaId");
            l.MapRightKey("AppleId", "CoconutId");
        }
    );

While somewhat smelly and definitely hacktastic, it did get me past the MetadataException, until I tried to add a new link from code.

var banana = dataContext.FindBanana(appleId, bananaId);
var coconut = dataContext.FindCoconut(appleId, coconutId);
banana.Coconuts.Add(coconut);
dataContext.SaveChanges();

Save changes throws a DbUpdateException

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

And the inner (couple exceptions down, actually)...

{"The column \"BananaAppleId\" cannot be modified because it is either a computed column or is the result of a UNION operator."}

And now I'm out of ideas. :) The database schema accurately models what we need (with the exception of that computed column). What's the best way to handle this? I'm not crazy about making BananaAppleId a "real" column, changing the foreign keys, and having duplicate stored data that shouldn't but could get out of sync.

Answer 1

The join table needs to have 4 columns as primary key and each one is a foreign key to the main table. A column can not be set as computed because EF will insert records by sending all 4 column values. So in your case

HasMany(e => e.Coconuts)
    .WithMany(s => s.Bananas)
    .Map(l =>
        {
            l.ToTable("BananaCoconuts", "fruit");
            l.MapLeftKey("BananaAppleId", "BananaId");
            l.MapRightKey("CoconutAppleId", "CoconutId");
        }
    );

Otherwise you have to map the join table as a separate entity.