Sunday, November 30, 2014

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

By default, Entity Framework will be automatically creating a join table when he sees a many to many relationship. For example let’s take the following example. Let’s say I have two entities named Person and Hobby. Person can have many hobbies. Single Hobby can be used by many persons. If I 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 me the following table structure.

image
Table Structure
Now let’s say, since a person can have many hobbies, I need to track the primary hobby of a person. So for that I 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 I can modify the join table.

But there is. Let’s create the join table manually. For that I am creating 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 I am changing 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 I am using the Fluent API in the overriding OnModelCreating event in my 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 me the following table structure as expected.

image
Table Structure
So that’s it. I am uploading the sample to my OneDrive.


Happy Coding.

Regards,
Jaliya

No comments:

Post a Comment