Adding columns to joint tables with payload in MVC

Question

Evening, I have recently gotten help making my database in MVC on SO (I'm very thankful for that). I have another question, as I'm not in graduate school yet so I don't know the best-practice, I thought someone might in my case.

I am making joint tables between two classes, a User table and a Course table. Put simply, a User can have a Course, and this is being made in my OnModelCreating method of my DbContext class

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new UserMap());

    base.OnModelCreating(modelBuilder);
}

<-- snip --> Below is my class for the mappings <-- snip -->

public class UserMap : EntityTypeConfiguration<ApplicationUser>
{
    public UserMap()
    {

        // Primary Key
        this.HasKey(m => m.Id);

        // UserHasCourse
        this.HasMany(m => m.Courses)
            .WithMany()
            .Map(n =>
                {
                    n.MapLeftKey("UserId");
                    n.MapRightKey("CourseId");
                    n.ToTable("UserHasCourse");
                });
    }
}

What I want to do is be able to add more columns to the UserHasCourse. The question is, how do I do this if the table is being generated here? This will not be the only case where I need to add a column/s that isn't necessarily related to the model (ie. I want to add the columns Credits and Grade, corresponding to the number of credits a user has earned in the course with a grade). I don't want to have Credits and Grade saved in the Courses table (as they only need to be in this joint table).

I can always add in two columns in the Server Explorer, but is there a way I should do this in code (if it is better that way)?

I've found a solution to the question, outlined in a blog post you can find here. http://programmerscheatbook.blogspot.com/2014/08/mvc-making-join-table-with-payload.html — reZach, Aug 16 '14 at 04:16

score 1 · Accepted Answer · answered Jul 24 '14 at 01:54

1

Entity Framework can do LOTS of stuff under the covers. However, this does not necessarily mean it's a good idea to let EF "figure stuff out" for you. I use EF all the time and find the CodeFirst approach a great way to get what you want done. What I would suggest is more of a declarative approach. Tell EF exactly what you want to do.

//Models

public class User
 {
   public int UserId {get;set;}
   public int CourseId {get;set;}

   public virtual Course Course {get;set;}
 }

public class Course
 {
   private ICollection<User> _users;

   public int CourseId {get;set;}
   public string CustomPropOne {get;set;}
   public string CustomPropTwo {get;set;}

   // mark this as virtual if you want to enable lazy loading
   public ICollection<User> Users
   {
     get{ return _users ?? ( _users = new List<User>()); }
     protected set{ _users = value;}
   }
}

//Mapping Classes

public class UserMap : EntityTypeConfiguration<User>
 {
   public UserMap()
    {
      HasKey(u => u.UserId);
      HasRequired(u => u.Course)
      .WithMany(c => c.Users)
      .HasForeignKey(u => u.CourseId);
    }
 }

//etc.

By declaring EXACTLY what you want to happen, you ensure your db is setup correctly and it becomes trivial to change/update your db model.

--

answered Jul 24 '14 at 01:54

trevorc

3,023
4
31
49

You are skirting around my question, I want to avoid putting these properties in my Course model because it doesn't relate to it, but want to have the properties in my joint table. I want to normalize my tables as best I can and I feel by putting the properties in my class, this doesn't help that case. – reZach Jul 24 '14 at 01:59
Then just add a Grade table and link it the same way to your course table. That way your course table can have 'n' courses and your grade table can have 'n' grades to 'n' courses. This is why the CodeFirst approach should not be much different than if you were to create your db schema using SQL. – trevorc Jul 24 '14 at 02:05
I need the Grade information linked to the (already joined Users and Courses table), how is that done? – reZach Jul 24 '14 at 02:38
Your question states one user one course. Is that what you want or do you want multiple courses per user (more real world example). – trevorc Jul 24 '14 at 02:49
Courses and Users maintain a many to many relationship; I want multiple courses to many users – reZach Jul 24 '14 at 19:34
I've found information on here, if it helps your answer: http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table – reZach Jul 24 '14 at 20:57

Adding columns to joint tables with payload in MVC

1 Answers1