I am trying to Update-Database after Add-Migration Initial using Entity Framework. These are the Models that I want to create using Many-To-Many relationship:

Trainer Model

public class Trainer
    {
        public int ID { get; set; }
 
        public string LastName { get; set; }


        public string FirstMidName { get; set; }

        public DateTime HireDate { get; set; }

        public string FullName
        {
            get { return LastName   ", "   FirstMidName; }
        }

        public int LocationId { get; set; }
        public OfficeLocation? OfficeLocation { get; set; }

        public ICollection<Course>? Courses { get; set; }
    }

Course Model

    public class Course
    {
        public int CourseId { get; set; }
        public string Title { get; set; }
        public int Cost { get; set; }


        public int DepartmentID { get; set; }
        public Department? Department { get; set;  }

        public ICollection<Enrollment>? Enrollments { get; set; }
        public ICollection<Trainer>? Trainers { get; set; }
}

EDIT: Added the rest of the models.

Department model: 
    public class Department
    {
        public int ID { get; set; }

        [Required]
        [MinLength(2)]
        public string Name { get; set; }

        [Required]
        //[DataType(DataType.Date)]
        //[DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}",
        //               ApplyFormatInEditMode = true)]
        public DateTime StartDate { get; set; }

        public int TrainerID { get; set; }


        public Trainer? Trainer { get; set; } // = new Trainer();
        public ICollection<Course>? Courses { get; set; }
    }

Student Model:

public class Student
{
    public int ID { get; set; }
    public string LastName { get; set; }
    public string FirstMidName { get; set; }
    public DateTime EnrollmentDate { get; set; }
    public string FullName
    {
        get
        {
            return LastName   ", "   FirstMidName;
        }
    }


    public ICollection<Enrollment>? Enrollments { get; set; }
}

Enrollment Model:

public class Enrollment
{
    public int EnrollmentID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }

    public Course Course { get; set; }
    public Student Student { get; set; }
}

OfficeLocation Model:

public class OfficeLocation
{
    public int Id { get; set; }
    public string LocationAddress { get; set; }
}

My issue is that when I try to Update-Database, right after Add-Migration Initial, the database is also creating the CourseTrainer join table and it gives me this error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [CourseTrainer] (
          [CoursesId] int NOT NULL,
          [TrainersId] int NOT NULL,
          CONSTRAINT [PK_CourseTrainer] PRIMARY KEY ([CoursesId], [TrainersId]),
          CONSTRAINT [FK_CourseTrainer_Course_CoursesId] FOREIGN KEY ([CoursesId]) REFERENCES [Course] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_CourseTrainer_Trainer_TrainersId] FOREIGN KEY ([TrainersId]) REFERENCES [Trainer] ([Id]) ON DELETE CASCADE
      );
        Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
        CREATE TABLE [CourseTrainer] (
            [CoursesCourseId] int NOT NULL,
            [TrainersID] int NOT NULL,
            CONSTRAINT [PK_CourseTrainer] PRIMARY KEY ([CoursesCourseId], [TrainersID]),
            CONSTRAINT [FK_CourseTrainer_Course_CoursesCourseId] FOREIGN KEY ([CoursesCourseId]) REFERENCES [Course] ([CourseId]) ON DELETE CASCADE,
            CONSTRAINT [FK_CourseTrainer_Trainer_TrainersID] FOREIGN KEY ([TrainersID]) REFERENCES [Trainer] ([ID]) ON DELETE CASCADE
        );
    Introducing FOREIGN KEY constraint 'FK_CourseTrainer_Trainer_TrainersID' on table 'CourseTrainer' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint or index. See previous errors.

I have looked on the internet for some solutions, but I could not find one that helps me understand. Please note that I am not that advanced and I am learning about table relations. Thank you very much for understanding.

I have made a test in which I created another blazer server app where I added these only two models in it, identical like in here and there I could update the database successfully.

CodePudding user response：

In your Department class change your foreign key to int? this will resolve your problem. Trainer? should be int?

    public int? TrainerID { get; set; }
    public Trainer Trainer { get; set; } // = new Trainer();

The optional foreign relationships should use a nullable on the key not the entity.

After this small change I was able to create and run the migration.

Side Note:

  public string FullName => LastName   ", "   FirstMidName;