Many-to-many relationship between entities of same type in MVC3

Question

I have an ASP.NET MVC3 application, where I use Entity Framework 4.3 Code-First and Migrations.

I've been trying to create a many-to-many relationship between entities of the same type, but when I scaffold the migration with Migrations, it generates a one-to-one relationship.

The idea is that one user should be able to follow multiple other users (think Twitter).

My User model look something like this:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public DateTime Registered { get; set; }
    ...
    public virtual ICollection<User> Follows { get; set; }
}

When I scaffold the the added Follows-property, I get a migration like this:

public partial class FollowUser : DbMigration
{
    public override void Up()
    {
        AddColumn("User", "User_UserId", c => c.Int());
        AddForeignKey("User", "User_UserId", "User", "UserId");
        CreateIndex("User", "User_UserId");
    }

    ...
}

So Entity Framework interprets my model as a one-to-one relationship between two users.

How can I create a many-to-many relationship between entities of the same type?

@Blast_dan Thanks for your response! Not sure how that question relates to this though? — Christofer Eliasson, May 02 '12 at 20:49
Woops wrong link http://stackoverflow.com/questions/9683716/mapping-for-self-referencing-entity-in-ef-code-first — Blast_dan, May 02 '12 at 21:02

score 14 · Accepted Answer · answered May 02 '12 at 21:45

Since It is a Many to Many Self Joined relationship, User entity should have a Followers and Following properties both of Type User. So Let us alter your class to include those

public class User
{
    public int ID { get; set; }
    public string Name { get; set; }       
    public virtual ICollection<User> Followers { get; set; }
    public virtual ICollection<User> Following { get; set; }
}

Now update the DbContext class to have a Property which returns the Users

 public DbSet<User> Users { set; get; }

We need to tell EntityFramework that we need to have a many to many relations ship between these two. So Let us do that with Fluent API by overriding OnModelCreating method

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasMany(m => m.Followers).WithMany(p => p.Following).Map(w => w.ToTable("User_Follow").MapLeftKey("UserId").MapRightKey("FollowerID"));
    base.OnModelCreating(modelBuilder);
}

Now Entity Framework will create the tables like this.

enter image description here

Note that we explicitly told Entity Framework to use UserId and FollowerId as the Forign Key column name. If we havent mentioned that, EF will use User_ID and User_ID1 as the column names.

Thanks a lot! This is a really good answer, very clear. I'd +2 if I could. — Christofer Eliasson, May 03 '12 at 06:26
Thanks a lot. I would like to add some additional columns to User_Follower table. Is it possible to have this table as entity ? Also i have noticed followers and followees are getting loaded eagerly, would it cause performance issues because i would prefer loading them lazily — Deliganli, Nov 22 '15 at 01:26

Many-to-many relationship between entities of same type in MVC3

1 Answers1

Linked