Many-to-many mapping table

C#Entity FrameworkEf Code-FirstMany to-Many

C# Problem Overview


From examples that I have seen online and in a Programming Entity Framework CodeFirst book, when you have a collection on both classes EF would create a mapping table such as MembersRecipes and the primary key from each class would link to this table.

However when I do the below, I instead get a new field in the Recipes table called Member_Id and a Recipe_Id in the Members table.

Which only creates two one-to-many relationships, but not a many-to-many so I could have Member 3 linked to Recipes (4,5,6) and Recipe 4 linked to Members (1,2,3) etc.

Is there a way to create this mapping table? and if so how do you name it something else such as "cookbooks" ?

Thanks

    public abstract class Entity {
		[Required]
		public int Id { get; set; }
	}	

    public class Member : Entity {
		[Required]
		public string Name { get; set; }

		public virtual IList<Recipe> Recipes { get; set; }
	}

	public class Recipe : Entity {	
		[Required]
		public string Name { get; set; }

		[ForeignKey("Author")]
		public int AuthorId { get; set; }
		public virtual Member Author { get; set; }
            
            ....

		public virtual IList<Member> Members { get; set; }
	}

UPDATE: Below is another approach I have tried which doesn't use the Fluent API and replaces the AuthorId & Author on Recipe with an owner flag, I have also renamed the below example from Cookbooks to MembersRecipes, this also fixes my issue similar to the answer but as mentioned has further implications.

public class MembersRecipes {

	[Key, Column(Order = 0)]
	[ForeignKey("Recipe")]
	public int RecipeId { get; set; }
	public virtual Recipe Recipe { get; set; }

	[Key, Column(Order = 1)]
	[ForeignKey("Member")]
	public int MemberId { get; set; }
	public virtual Member Member { get; set; }

	public bool Owner { get; set; }
}

and in Recipe & Member classes I changed the collections to

public virtual IList<MembersRecipes> MembersRecipes { get; set; }

C# Solutions


Solution 1 - C#

Do this on your DbContext OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{    
    modelBuilder.Entity<Recipe>()
        .HasMany(x => x.Members)
        .WithMany(x => x.Recipes)
    .Map(x =>
    {
        x.ToTable("Cookbooks"); // third table is named Cookbooks
        x.MapLeftKey("RecipeId");
        x.MapRightKey("MemberId");
    });
}

You can do it the other way around too, it's the same, just another side of the same coin:

modelBuilder.Entity<Member>()
    .HasMany(x => x.Recipes)
    .WithMany(x => x.Members)
.Map(x =>
{
  x.ToTable("Cookbooks"); // third table is named Cookbooks
  x.MapLeftKey("MemberId");
  x.MapRightKey("RecipeId");
});


Further examples:

http://www.ienablemuch.com/2011/07/using-checkbox-list-on-aspnet-mvc-with_16.html

http://www.ienablemuch.com/2011/07/nhibernate-equivalent-of-entity.html


UPDATE

To prevent cyclical reference on your Author property, aside from above, you need to add this:

modelBuilder.Entity<Recipe>()
    .HasRequired(x => x.Author)
    .WithMany()
    .WillCascadeOnDelete(false);

Idea sourced here: https://stackoverflow.com/questions/5273569/ef-code-first-with-many-to-many-self-referencing-relationship

The core thing is, you need to inform EF that the Author property(which is a Member instance) has no Recipe collections(denoted by WithMany()); that way, cyclical reference could be stopped on Author property.

These are the created tables from the Code First mappings above:

CREATE TABLE Members(
	Id int IDENTITY(1,1) NOT NULL primary key,
	Name nvarchar(128) NOT NULL
);


CREATE TABLE Recipes(
	Id int IDENTITY(1,1) NOT NULL primary key,
	Name nvarchar(128) NOT NULL,
	AuthorId int NOT NULL references Members(Id)
);


CREATE TABLE Cookbooks(
	RecipeId int NOT NULL,
	MemberId int NOT NULL,
	constraint pk_Cookbooks primary key(RecipeId,MemberId)
);

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPriceyView Question on Stackoverflow
Solution 1 - C#Michael BuenView Answer on Stackoverflow