EF Many-to-many- custom intermediary table but migration creates 2

Question

I was trying to create an EF code-first model with 2 tables "Students" and "Classes". Normally EF creates a mapping table automaticly, but I prefer to have my own so it's queryable. When I create my model in MSQL it looks how it should be, but for some reason in my migrations I see that EF is trying to create the table twice.

my Student class

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace test.Models
{
    public class Student
    {

        public Student()
        {
            Classes = new HashSet<Class>();
        }


        [Key]
        public int Id { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        [Required]
        public DateTime DateOfBirth { get; set; }

        [Required]
        public Gender Gender { get; set; }

        [Required]
        public int TotalHours { get; set; }

        [Required]
        public int HoursStudied { get; set; }

        public DateTime DateJoined { get; set; }

        public ICollection<Class> Classes { get; set; }
        public string FirstName { get; set; }


        public int HoursLeft
        {
            get
            {
                return this.TotalHours - this.HoursStudied;
            }
        }
    }
}

Class class

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace test.Models
{
    public class Class
    {

        public Class()
        {
            Students = new HashSet<Student>();
        }

        [Key]
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string ClassName { get; set; }

        [Required]
        [Range(1, 100)]
        public int MaxStudents { get; set; }

        public ICollection<Student> Students { get; set; }

    }
}

ClassStudents class

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace test.Models
{
    public class ClassStudents
    {
        public Class Class { get; set; }

        public Student Student { get; set; }

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

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

SchoolContext Class

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

namespace test.Models
{
    public class SchoolContext : DbContext
    {
        public DbSet<Class> Classes { get; set; }
        public DbSet<Student> Students { get; set; }
        public DbSet<ClassStudents> ClassStudents { get; set; }

        public SchoolContext() : base("SchoolManagementPortalEntities")
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Class>()
                .HasMany(c => c.Students)
                .WithMany(c => c.Classes);
        }
    }
}

Now here is the weird Migration that it creates, note the ClassStudents1 table

namespace test.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class InitialModel : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Classes",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        ClassName = c.String(nullable: false, maxLength: 50),
                        MaxStudents = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.Students",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Name = c.String(nullable: false, maxLength: 100),
                        DateOfBirth = c.DateTime(nullable: false),
                        Gender = c.Int(nullable: false),
                        TotalHours = c.Int(nullable: false),
                        HoursStudied = c.Int(nullable: false),
                        DateJoined = c.DateTime(nullable: false),
                        FirstName = c.String(),
                    })
                .PrimaryKey(t => t.Id);

            CreateTable(
                "dbo.ClassStudents",
                c => new
                    {
                        ClassId = c.Int(nullable: false),
                        StudentId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => new { t.ClassId, t.StudentId })
                .ForeignKey("dbo.Classes", t => t.ClassId, cascadeDelete: true)
                .ForeignKey("dbo.Students", t => t.StudentId, cascadeDelete: true)
                .Index(t => t.ClassId)
                .Index(t => t.StudentId);

            CreateTable(
                "dbo.ClassStudents1",
                c => new
                    {
                        Class_Id = c.Int(nullable: false),
                        Student_Id = c.Int(nullable: false),
                    })
                .PrimaryKey(t => new { t.Class_Id, t.Student_Id })
                .ForeignKey("dbo.Classes", t => t.Class_Id, cascadeDelete: true)
                .ForeignKey("dbo.Students", t => t.Student_Id, cascadeDelete: true)
                .Index(t => t.Class_Id)
                .Index(t => t.Student_Id);

        }

        public override void Down()
        {
            DropForeignKey("dbo.ClassStudents", "StudentId", "dbo.Students");
            DropForeignKey("dbo.ClassStudents", "ClassId", "dbo.Classes");
            DropForeignKey("dbo.ClassStudents1", "Student_Id", "dbo.Students");
            DropForeignKey("dbo.ClassStudents1", "Class_Id", "dbo.Classes");
            DropIndex("dbo.ClassStudents1", new[] { "Student_Id" });
            DropIndex("dbo.ClassStudents1", new[] { "Class_Id" });
            DropIndex("dbo.ClassStudents", new[] { "StudentId" });
            DropIndex("dbo.ClassStudents", new[] { "ClassId" });
            DropTable("dbo.ClassStudents1");
            DropTable("dbo.ClassStudents");
            DropTable("dbo.Students");
            DropTable("dbo.Classes");
        }
    }
}

Now I don't know why it happens but for some reason there are 2 classStudents tables which mess up with my code.

any help would be appreciated. Cheers!

I like [this method](https://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table) for many to many. — Steve Greene, Jul 31 '17 at 17:52

score 0 · Answer 1 · answered Jul 31 '17 at 15:03

My answer is based off of this tutorial: http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx

This is what you might be missing:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Class>()
                .HasMany<Student>(s => s.Students)
                .WithMany(c => c.Classes)
                .Map(cs =>
                    {
                        cs.MapLeftKey("ClassId");
                        cs.MapRightKey("StudentId");
                        cs.ToTable("ClassStudents");
                    });
}

Thanks for the advice, I added it but it didnt seem to work. I found the answer in removing my intermediary table from the DbSet<>. — Rodney Wormsbecher, Jul 31 '17 at 19:55

score 0 · Answer 2 · answered Jul 31 '17 at 19:56

0

If I remove the DbSet from the DbContext it seems to be working fine.

answered Jul 31 '17 at 19:56

Rodney Wormsbecher

897
2
17
39

EF Many-to-many- custom intermediary table but migration creates 2

2 Answers2