Entity Framework mapping between multiple tables

Question

I am trying to get the correct mapping between 4 tables.

MainTables

Class(Id, ClassName)

Course(Id, CourseName)

Student(Id, StudentName)

Relationship tables

ClassCourse(Id, ClassId, CourseId)

ClassCourseStudent(ClassCourseId, StudentId)

Class to Course has Many to Many mapping. So we use a relationship table ClassCourse to store the relationship

Student has one to Many mapping with ClassCourse.

So my question is how can I do the mapping for Student and ClassCourse

My code is

public class Class
(
    public int Id {get;set;}
    public string ClassName {get;set;}
    public virtual ICollection<Course> Courses {get;set;}
)

public class Course
(
   public int Id {get;set;}
   public string CourseName {get;set;}
   public virtual ICollection<Student> Students {get;set;}
)

public class Student
(
  public int Id {get;set;}
  public string StudentName {get;set;}
)
modelBuilder.Entity<Class>().ToTable("Class");
modelBuilder.Entity<Course>().ToTable("Course");
modelBuilder.Entity<Student>().ToTable("Student");

modelBuilder.Entity<Class>().HasMany(c => c.Courses).WithMany().Map(m => m.ToTable("ClassCourse") 
m.MapLeftKey("ClassId")
m.MapRightKey("CourseId")
)

modelBuilder.Entity<Course>().HasMany(c => c.Students).WithMany().Map(m =>               
 m.ToTable("ClassCourseStudent") 
 m.MapLeftKey("ClassCourseId")
 m.MapRightKey("StudentId")

The last mapping is the one I am looking for.

Thanks in advance.

@JeroenVannevel In the last mapping. It is expecting the left key to be CourseId and right key to be StudentId. But because the ClassCourseId is a primary key of ClassCourse table, it doesn't match with the CourseId, hence no records are returned. :-( — Naveen Chakravarthy
– Naveen Chakravarthy, Commented Nov 13, 2013 at 22:05

Jeroen Vannevel · Accepted Answer · 2013-11-13 22:11:52Z

2

I think you have to revisit your design. Right now you're trying to assign a composite key as foreign key, which can't be done.

What I would do is create a separate model that simply stores the course-class combination and provides a single key to reference. This will result in an extra table, but allows you to do what you want.

class Student {
 public int StudentId {get; set;}
}

class Class {
 public int ClassId {get; set;}
}

class Course {
 public int CourseId {get; set;}
}

class ClassCourse {
 public int ClassCourseId {get; set;}
 public int ClassId {get; set;}
 public int CourseId {get; set;}
}

Now every class should have a list of ClassCourse objects instead of Course, and every Course should have a list of ClassCourse objects. Now they're not directly linked together but are still connected trough an intermediate object and you can connect your Student objects to the primary key of ClassCourse.

answered Nov 13, 2013 at 22:11

Jeroen Vannevel

44.6k25 gold badges113 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naveen Chakravarthy Over a year ago

Thank you Jeroen, I thought the same of changing the design to make it work. Thanks again for the suggestion that composite key as foreign key will not work. :-)

Collectives™ on Stack Overflow

Entity Framework mapping between multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related