static void

I wanted to use a many-to-many relationship using Entity Framework Code First (v4.1/4.2).

Using pure code first such as this:

using (var context = new MyContext())
{
    var employee = new Employee { FirstName = "Homer", LastName = "Simpson" };
    var territory = new Territory { TerritoryDescription = "Springfield" };
    employee.Territories.Add(territory);
    context.Employees.Add(employee);

    context.SaveChanges();
}

results in a nice association table

How do you map existing database tables? Like Northwind's customer to customer demographic table relationship:

This is the code I want to write:

using (var context = new MyContext("name=Northwind"))
{

    var demo = new CustomerDemographic();
    demo.CustomerTypeID = "BERLIN";
    demo.CustomerDesc = "Berliner";
    context.CustomerDemographics.Add(demo);
    //link it to a customer by either end
    var alfki = context.Customers.Find("ALFKI");
    alfki.CustomerDemographics.Add(demo);

    context.SaveChanges();
}

We have to override DbContext's OnModelCreating and add some mapping. For CodeFirst, you map both sides of the relationship, so you can either put the mapping on Customer or CustomerDemographic - or even both if the mappings agree. A normal foreign key relationship is mapped with ".HasMany|HasOptional|HasRequired" followed by a ".WithMany|WithOptional|WithRequired".

So, from the CustomerDemographic entity, a many to many is just .HasMany(x=>x.Customers).WithMany(z=>z.CustomerDemographics).

In addition, we don't have standard names for our association table so we add a .Map element to specify the table and the left and right key columns.

Note the primary key of CustomerDemographics isn't the 'tableName'+"Id" convention that Code First will expect. So I have to define the key for that. As we have that end of the configuration, we'll define the mapping there.

Here's the code.

modelBuilder.Entity<CustomerDemographic>()
    //the key isn't standard so specify it
    .HasKey(x => x.CustomerTypeID)
    //define both sides of the relationship - HasMany.WithMany
    .HasMany(x => x.Customers)
    .WithMany(z => z.CustomerDemographics)
    //specify mapping information
    .Map(map =>
    {
        //the association table name
        map.ToTable("CustomerCustomerDemo");
        //the left side (fk to CustomerDemographic, the entity we're defining)
        map.MapLeftKey("CustomerTypeID");
        //the right side (fk to Customers, the other side)
        map.MapRightKey("CustomerID");
    }
);

If we mapped from the Customer entity, the HasMany and WithMany properties are different, and the mapped left and right keys swap round.

Here's the full DbContext for my mini-Northwind mapping:

class MyContext : DbContext
{
    public MyContext(string connectionName)
        : base(connectionName)
    {
    }

    public DbSet<Employee> Employees { get; set; }
    public DbSet<Territory> Territories { get; set; }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<CustomerDemographic> CustomerDemographics { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());
        Database.SetInitializer<MyContext>(null);
        modelBuilder.Conventions.Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();

        modelBuilder.Entity<CustomerDemographic>()
            //the key isn't standard so specify it
            .HasKey(x => x.CustomerTypeID)
            //define both sides of the relationship - HasMany.WithMany
            .HasMany(x => x.Customers)
            .WithMany(z => z.CustomerDemographics)
            //specify mapping information
            .Map(map =>
            {
                //the association table name
                map.ToTable("CustomerCustomerDemo");
                //the left side (fk to CustomerDemographic, the entity we're defining)
                map.MapLeftKey("CustomerTypeID");
                //the right side (fk to Customers, the other side)
                map.MapRightKey("CustomerID");
            }
        );
    }
}