Entity Framework 4.3 code first multiple many to many using the same tables

Question

I have a model like

public class User
{
    [Key]
    public long UserId { get; set; }

    [Required]
    public String Nickname { get; set; }

    public virtual ICollection<Town> Residencies { get; set; }

    public virtual ICollection<Town> Mayorships { get; set; }
}

and

public class Town
{
    [Key]
    public long TownId { get; set; }

    [Required]
    public String Name { get; set; }

    public virtual ICollection<User> Residents { get; set; }
    public virtual ICollection<User> Mayors { get; set; }
}

I was hoping EF would create two many to many relationships using automatically created TownResidents and TownMayors table. I can't seem to find the necessary convention or explicit annotation to get this result.

Instead I am getting two FK UserIds in the Town table and two FK TownIds in the User table.

Any ideas how to get EF to see these at two many to many relationships?

Thanks

Answer 1

Well, EF doesn't have some kind of word and grammar recognition algorithm which would be required to identify that you (probably) want that User.Residencies and Town.Residents form a pair of navigation properties and User.Mayorships and Town.Mayors form a second pair. Therefore it assumes that you have four one-to-many relationships and that each of the four navigation properties belongs to one of the relationships. (This is the reason for the four foreign keys you have seen in the database tables.)

This standard assumption is not what you want, hence you must define the relationships explicitly to override this standard convention:

Either with data annotations:

public class User
{
    [Key]
    public long UserId { get; set; }

    [Required]
    public String Nickname { get; set; }

    [InverseProperty("Residents")]
    public virtual ICollection<Town> Residencies { get; set; }

    [InverseProperty("Mayors")]
    public virtual ICollection<Town> Mayorships { get; set; }
}

Or with Fluent API:

modelBuilder.Entity<User>()
    .HasMany(u => u.Residencies)
    .WithMany(t => t.Residents)
    .Map(x =>
    {
        x.MapLeftKey("UserId");
        x.MapRightKey("TownId");
        x.ToTable("TownResidents");
    });

modelBuilder.Entity<User>()
    .HasMany(u => u.Mayorships)
    .WithMany(t => t.Mayors)
    .Map(x =>
    {
        x.MapLeftKey("UserId");
        x.MapRightKey("TownId");
        x.ToTable("TownMayors");
    });

Fluent API has the advantage that you can control the name of the link table (and the key column names as well). You cannot define those names with data annotations.