How do I create a Many-to-Many relationship with only one entity?

Question

Courses have many prerequisites, and simultaneously a particular course can be a prerequisite for many courses. I've tried to establish the many-to-many relationship (in OnModelBCreating) using EF code-first with the following:

modelBuilder.Entity<Course>()
                .HasMany(e => e.Prerequisites)
                .WithMany(e => e.Postrequisites)
                .Map(m => m.ToTable("CourseRequisiteMappings")
                .MapLeftKey("CourseId").MapRightKey("CourseId")); // EDIT: THIS LINE IS THE PROBLEM. SEE MARKED ANSWER AND MY COMMENT ON IT.

Also, here is the Course class:

public class Course
{
    public int CourseId { get; set; }
    public string Name { get; set; }
    public string InstitutionCode { get; set; }     
    public string Description { get; set; }
    public bool IsElective { get; set; }
    public virtual ICollection<Instructor> Instructors { get; set; }
    public virtual ICollection<Student> Students { get; set; }
    public virtual ICollection<Module> Modules { get; set; }
    public virtual ICollection<Course> Prerequisites { get; set; }
    public virtual ICollection<Course> Postrequisites { get; set; }
}

When I implemented this and went to update the database, it gave me the following errors:

CourseId: Name: Each property name in a type must be unique. Property name 'CourseId' is already defined.

ModuleId: Name: Each property name in a type must be unique. Property name 'ModuleId' is already defined.

CourseCourse: EntityType: EntitySet 'CourseCourse' is based on type 'CourseCourse' that has no keys defined.

ModuleModule: EntityType: EntitySet 'ModuleModule' is based on type 'ModuleModule' that has no keys defined.

I could not find an example of doing this which leads me to believe one of the following three are true:

1. There's a different way of accomplishing this that I don't see
2. I'm on the right track but overlooking something due to my lack of knowledge with EF
3. I'm the first one to try and EF doesn't support this (very unlikely)

First, does anyone know how I can set up this relationship, i.e., what do these errors mean (responding to #2)? For bonus points, is there another way of doing this that might be better or worse (kinda #1)? Thanks in advance.

Answer 1

Your mapping is nearly correct. But you have to understand that under the hood the Entity Framework wil create a so callled junction table that stores the many to many relationship.

This junction table wil just have two fields, containing the foreign keys which together make up the primary key. Obviously these foreign keys cannot have the same name.EF is smart enough to all figure it out by itself and no maping is necessary. Below a working example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;

namespace ManyToManyUnderTheHoodSpike
{
class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<CourseContext>());
        using (CourseContext context=new CourseContext())
        {
            context.Courses.Add(new Course("Top of the bill")
            {
                PrerequisiteCourses = new List<Course>() 
                    {
                        new Course("My two cents"),
                        new Course("Counting to two")
                    }
            });
            context.SaveChanges();
        }
    }
}

public class CourseContext : DbContext
{

    public DbSet<Course> Courses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

public class Course
{

    public Course() { }

    public  Course(string name)
    {
        Name = name;
    }

    public string Name {get;set;}
    public int CourseId{get;set;}

    public ICollection<Course> PrerequisiteCourses{get;set;}
    public ICollection<Course> FollowUpCourses{get;set;}
}
}

If you run this code you get a database with two tables: Courses and CourseCourses with as the only fields Course_Id and Course_Id1.

But that is not very readable, so let's make the mapping to make it more readable:

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Course>().HasMany(course => course.PrerequisiteCourses)
            .WithMany(course => course.FollowUpCourses)
            .Map(data => data.ToTable("Prerequisites")
                .MapLeftKey("FollowUpId")
                .MapRightKey("PrerequisiteId"));
    }

Presto!

Answer 2

I would model like this. I know you wanted only 1 table. But Ef will create the many to many table if you dont. Not sure what you didnt get right without testing. So anyway, here is another option.

public class Course
{
public int CourseId { get; set; }
public string Name { get; set; }
public string InstitutionCode { get; set; }     
public string Description { get; set; }
public bool IsElective { get; set; }
//nav elements
public virtual ICollection<Instructor> Instructors { get; set; }
public virtual ICollection<Student> Students { get; set; }
public virtual ICollection<Module> Modules { get; set; }
public virtual ICollection<PreReqCourse> Prerequisites { get; set; }
// You can Find follow on courses, by accessing PreReqCourse table, but if you felt this navigation offered enough value, create a post req table too. Using same approach.
// public virtual ICollection<Course> Postrequisites { get; set; } 
}

public class PreReqCourse
{
public virtual int Id {get; set;}
public virtual int CourseId { get; set; }
public virtual Course  PreReqForCourse { get; set; } //Nav prop
}


modelBuilder.Entity<Course>()
            .HasMany(e => e.Prerequisites)
            .WithMany();   
// Leave WithMany empty. You can define in PreReqCourse Table model, you dont need to model from both directions. 


modelBuilder.Entity<PreReqCourse>()
            .HasRequired(e => e.PreReqForCourse)
            .HasForeignKey(f => f.CourseId)
            .WithMany(p=>p.PreRequisites);