Many-to-many mapping table
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; }
List and the other was an IEnumerable. I changed them to both be lists and then Add-Migration started generating a XR table instead of just the FK properties.
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: 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)
);
Follow WeChat
Success story sharing
