CODEWITHSUNDEEP
entity-frameworkef-code-first
John
John

Reputation: 217

Many to Many Relationship EF Code First

I'm starting to learn MVC and I'm trying to create a database using the entity framework code first approach. I have a table for students and a table of subjects, each student can have a number of subjects and a a subject can have a number of students. The linker table has a column for grade, because each student will have a different grade for every subject. I'm getting an error when trying to create the dtabase, telling me that one or more validation errors were detected during model generation. Here are my models. 

 public class Course
{
    [Key]
    public int CourseId { get; set; }
    public string CourseName { get; set; }
    public List<Student> Students { get; set; }

}

public class Student
{
     [Key]
    public int StudentId { get; set; }
    public string Name { get; set; }
    public List<Course> Courses { get; set; }
}

public class StudentCourses
{
     [Key]
    public Course Course { get; set; }
     [Key]
    public Student Student { get; set; }
    public double Grade { get; set; }
}

public class CourseDb: DbContext
{

    public DbSet<Course> Courses { get; set; }
    public DbSet<Student> Students { get; set; }
    public DbSet<StudentCourses> StudentCourses { get; set; }


}

I've googled around a bit and have seen some approaches for a many to many relationship, but none that have an extra property in the linker table( the Grade property).

Upvotes: 1

Views: 179

Answers (1)

Chris Pratt
Chris Pratt

Reputation: 239430

Unfortunately, if you use a intermediary table with additional properties (or really, just if you define a specific class to manage that relationship instead of letting EF handle it automatically), you lose the ability to have a direct navigation property.

You'll have to change your models to:

public class Course
{
    ...

    public List<StudentCourses> Students { get; set; }
}

And

public class Student
{
    ...

    public List<StudentCourses> Courses { get; set; }
}

Then, for example, if you're iterating through your list of courses for a particular student:

@foreach (var course in student.Courses)
{
    @course.Course.CourseName, @course.Grade
}

Also, worth mentioning that this might lead to 1+N queries, so if you're going to do that you should eagerly load the course when querying the student:

var student = db.Students.Include('Courses.Course').SingleOrDefault(m => m.StudentId == id);

EDIT

You actually had multiple problems here. I caught the first one, and just stopped there without paying attention to what else might be wrong. You're still getting errors because you're using a composite key (combination of student and course foreign keys) on your intermediary table. That's fine, but when you specify multiple keys, you also have to specify a column order:

[Key, Column(Order = 0)]
public Course Course { get; set; }

[Key, Column(Order = 1)]
public Student Student { get; set; }

I'm also not sure if you can use Key with a navigation property. Never tried to do it myself, but it may work. If you still get errors, I would try explicit foreign key properties:

[Key, Column(Order = 0)]
public int CourseId { get; set; }
public Course Course { get; set; }

[Key, Column(Order = 1)]
public int StudentId { get; set; }
public Student Student { get; set; }

Upvotes: 2

Related Questions