0

When setting up a many-to-many relationship with Code First approach, by default the join table only includes 2 columns (FK to PK of the 2 tables).

1) Is it possible/How can I setup a join table that will include some additional fields, and 2) how do I fill these values via code?

For example:

class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Course> Courses { get; set; }
}

class Course
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Student> Students { get; set; }
}

class MyDbContext : DbContext
{
    public DbSet<Student> Students { get; set; }
    public DbSet<Course> Courses { get; set; }
}

A simple db with Students and Courses with a many-to-many relationship. However for each join Student-Course, I also want a field, for example: PercentComplete, that tracks how far a student is in each course.

How can this be achieved?

Thanks

RaelB
  • 3,301
  • 5
  • 35
  • 55

1 Answers1

4

This is a case for many-to-many relationship with additional information. You will need to create a new entity called, let's say Enrollments. Then, you will need to set 1-to-Many relationships between Student - Enrollment and Course - Enrollment entities.

public class Student
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Enrollment> Enrollments { get; set; }//each student can have many enrollments
}

public class Course
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Enrollment> Enrollments { get; set; }//each course can have many enrollments
}


public class Enrollment
{
    public int EnrollmentId { get; set; }
    public int PercentComplete{ get; set; }
    public Course Course { get; set; } //each single enrollment involves one course
    public Student Student { get; set; } //each single enrollment involves one student
}

You can find a detailed example here:

https://practiceaspnet.wordpress.com/2015/11/09/many-to-many-relationships-with-additional-fields/

https://practiceaspnet.wordpress.com/2015/11/13/displaying-related-entity-in-gridview-entity-framework-code-first/

renakre
  • 8,001
  • 5
  • 46
  • 99
  • Thanks, very nice articles. – RaelB Apr 30 '15 at 18:26
  • @renakre I have a more complex scenario that seems to not work well. It defers from this one in two things: the entity has many to many relation with itself. And it has 2 concrete classes, each mapped to its own table. I'd be glad if you can assist. Full info is [here](https://stackoverflow.com/questions/52912942/entity-framework-non-existant-columns-are-added-to-query-on-a-many-to-many-rela) – Bob Oct 22 '18 at 13:06