ASP.NET MVC Many to Many relationship, using "My Own" table

Question

I am fairly new to using Code First approach with entity framework and I know that I you have a many to many relationship like the entities below, the EF will create the intermediary table automatically:

class Post {
  ...
  public virtual ICollection<Category> Categories {get; set;}
  ... 
}

class Category {
   ...
   public virtual ICollection<Post> Posts {get; set;}
   ...
}

However, if in the intermediary table I need to have extra data fields, one possible way (which I currently like, maybe because I am unaware of better ways) would be defining a new Entity of my own, like:

class Posts_Categories {
   public int Id {get; set;}
   public int CategoryId {get; set;}
   public int PostId {get; set;}
   public string Exrtafield1 {get; set;}
   public int ex extraField2 {get; set;}
   ...
   public virtual Post Post {get; set;}
   public virtual Category Category {get; set;}
}

Using this approach, EF does create my custom intermediary table, but it also creates another one of its own called "PostsCategories" which only contains a foreign key to Post_Id and another to Category_Id.

How do I make it not create that extra one and use the one I have defined? Is this a good way to manage Many to Many relationships with extra data fields??

possible duplicate of [Create code first, many to many, with additional fields in association table](http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table) — Gert Arnold, Apr 23 '15 at 20:14

score 5 · Accepted Answer · answered Apr 23 '15 at 19:35

5

you should use one to many relation like this :

public class Post
{
    public System.Int32 PostId { get; set; }

    [InverseProperty("Post")]
    public virtual ICollection<Posts_Category> PostCategories { get; set; }
}

public class Category
{
    public System.Int32 CategoryId { get; set; }

    [InverseProperty("Category")]
    public virtual ICollection<Posts_Category> PostCategories { get; set; }
}

public class Posts_Category
{
    public System.Int32 PostId { get; set; }

    public System.Int32 CategoryId { get; set; }

    [ForeignKey("PostId")]
    [InverseProperty("PostCategories")]
    public virtual Post Post { get; set; }

    [ForeignKey("CategoryId")]
    [InverseProperty("PostCategories")]
    public virtual Category Category { get; set; }
}

answered Apr 23 '15 at 19:35

Iraj

1,492
5
18
42

wow, @Iraj, I was about to post similar solution, you beat me to it, I agree , this is the way to go , this way it would not create another table, and you can still access all posts from Category and all categories from Post. – pjobs Apr 23 '15 at 20:20
Thanks @Iraj, I will look into it a bit later as first I have to learn the couple new keywords you have used here like the InverseProperty I was unaware of. I may hit you some more questions! Thanks so much Bro! – Behrooz Apr 23 '15 at 21:16
@behroozdalvandi: Please see this link may be help you : https://msdn.microsoft.com/en-us/data/jj591583.aspx – Iraj Apr 24 '15 at 04:45

score 1 · Answer 2 · answered Dec 07 '15 at 17:40

I needed to expand a bit on Iraj's answer to make it work. Another modification is that I'm including the default ApplicationUser as one of my tables.

So the relation is ApplicationUser 1-∞ IdeaVote ∞-1 Idea (i.e. there are users and ideas, users can vote on ideas, and each vote is represented with a connection between an ApplicationUser and an Idea.

public class Idea
{
    [Key]
    public int Id { get; set; }

    // This is an ordinary data field
    public string Text { get; set; }

    [InverseProperty("Idea")]
    public virtual ICollection<IdeaVote> Votes { get; set; }
}

public class IdeaVote
{
    [Key]
    public int Id { get; set; }

    public int IdeaId { get; set; }
    [ForeignKey("IdeaId")]
    [InverseProperty("Votes")]
    public virtual Idea Idea { get; set; }

    public string UserId { get; set; }
    [ForeignKey("UserId")]
    [InverseProperty("Votes")]
    public virtual ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    [InverseProperty("User")]
    public virtual ICollection<IdeaVote> Votes { get; set; }

    // Default stuff
}

score 0 · Answer 3 · answered Apr 23 '15 at 19:03

0

It is normal for it to create that PostsCategories table for the relation between the two, and you are going to want that. If c is a Category, you'll be able to do things like c.Posts

Normally you would not create your own table manually for that. What kinds of data would you be keeping in the "extra" fields? I would probably move the fields to one of the other tables and drop that one. Most many to many relationship tables do not contain extra fields.

answered Apr 23 '15 at 19:03

Christopher

134
7

Thanks Chris. I know most of m2m relations don't have extra fields but some do. Just consider a course table, a student tabel, and a StudentCourse table that keep what student has what courses and what is the grade for each course. That grade has to be in an extra field. I would appreciate if you can tell me how I can achieve what I have in mind. – Behrooz Apr 23 '15 at 19:27
2

There is an answer here with 187 upvotes that I believe is exactly what you are trying to do. It's not the easiest thing to accomplish but I hope this helps. http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table – Christopher Apr 23 '15 at 19:38
Thanks Chris, for the useful link :) – Behrooz Apr 23 '15 at 21:16

ASP.NET MVC Many to Many relationship, using "My Own" table

3 Answers3

Linked