Entity Framework : Customized Join Table in a Many to Many Relationship

By default, Entity Framework will create a join table when it sees a many-to-many relationship. For example, let’s take the following example. Let’s say we have two entities named Person and Hobby. A person can have many hobbies. A single hobby can be used by many persons. If we model that scenario, this is how it will look like.

public class Person

{

    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Hobby> Hobbies { get; set; }

}

public class Hobby

{

    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Person> Persons { get; set; }

}

So the above code will create the following table structure.

http://lh6.ggpht.com/-IBoKkWowLJI/VGW-NHlA5DI/AAAAAAAAC14/xxZVtut0ajs/image_thumb3.png?imgmax=800

Table Structure

Now let’s say, since a person can have many hobbies, we need to track the primary hobby of a person. So for that we will need to modify the join table (PersonHobbies) to add a new column to state whether that particular hobby is the primary hobby. But since Entity Framework is automatically creating the join table, initially it seems there is no way that we can modify the join table.

But there is. Let’s create the join table manually. For that let's create a new class named “PersonHobbies”.

public class PersonHobbies

{

    public int Person_Id { get; set; }

    public int Hobby_Id { get; set; }

    public virtual Person Person { get; set; }

    public virtual Hobby Hobby { get; set; }

    public bool IsPrimary { get; set; }

}

Then let's change the Person and Hobby classes as follows.

public class Person

{

    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<PersonHobbies> Hobbies { get; set; }

}

public class Hobby

{

    public int Id { get; set; }

    public string Name { get; set; }

    public virtual ICollection<PersonHobbies> Persons { get; set; }

}

Next what is left to be done is adding primary key and foreign keys in the PersonHobbies entity. For that let's use the Fluent API in the overriding OnModelCreating event in our DbContext.

public class MyDbContext : DbContext

{

    public DbSet<Person> Persons { get; set; }

    public DbSet<Hobby> Hobbies { get; set; }

    protected override void OnModelCreating(DbModelBuilder builder)

    {

        builder.Entity<PersonHobbies>()

            .HasKey(ph => new { ph.Person_Id, ph.Hobby_Id });

        builder.Entity<PersonHobbies>()

            .HasRequired(ph => ph.Person)

            .WithMany(ph => ph.Hobbies)

            .HasForeignKey(ph => ph.Person_Id);

        builder.Entity<PersonHobbies>()

            .HasRequired(ph => ph.Hobby)

            .WithMany(ph => ph.Persons)

            .HasForeignKey(ph => ph.Hobby_Id);

    }

}

So now the above will create the following table structure as expected.

http://lh4.ggpht.com/-ex6fI_OBqcg/VGXATQdtj8I/AAAAAAAAC2c/kl4KgIKFxYE/image_thumb%25255B2%25255D.png?imgmax=800

Table Structure

So that’s it. You can find the full sample code in MSDN Code Gallery.
Download Sample

Happy Coding.