EF Core, How to update a record in table which has both One To Many and Many to Many relationships with the same entity

Question

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int MainCourseId { get; set; }
    public Course MainCourse { get; set; }
    public IList<StudentCourse> StudentCourses { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; }
    public string Description { get; set; }
}

public class StudentCourse
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public int CourseId { get; set; }
    public Course Course { get; set; }
}

There are two issues. First issue is, the above migration is not creating the MainCourseId , gives this error. "Introducing FOREIGN KEY constraint 'FK_Students_Courses_MainCourseId' on table 'Students' 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."

Second is, in another system where structure of entities are same, updating an existing record with a modifed value in MainCourseId doesn't work.

Answer 1

You can't have cascade delete that creates a multiple cascade paths. Here deleting a Course would delete both all the StudentCourses for that Course and all the Students that have that as their MainCourse.

But EF won't configure CascadeDelete if your MainCourse is optional, as it probably should be to enable you to store a Student that has no MainCourse. So to fix both issues, just make Student.MainCourseId optional by changing the type to int?.:

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? MainCourseId { get; set; }
    public Course MainCourse { get; set; }
    public IList<StudentCourse> StudentCourses { get; set; }
}

Or you can leave it as required and configure the relationship not to cascade deletes.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
                .HasOne(s => s.MainCourse)
                .WithMany()
                .OnDelete(DeleteBehavior.Restrict);

    modelBuilder.Entity<StudentCourse>()
                .HasKey(e => new { e.StudentId, e.CourseId });

    base.OnModelCreating(modelBuilder);
}

I think it might be more convenient to use "skip navigation properties" so Student has Courses, and a Course has Students, which you can do in EF Core 5+ with Many-to-Many mapping, like this:

using Microsoft.EntityFrameworkCore;
using System.Linq;
using System;
using System.Collections.Generic;

namespace EfCore6Test
{ 


     public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int? MainCourseId { get; set; }
        public Course MainCourse { get; set; }
        public ICollection<Course> Courses { get; } = new HashSet<Course>();
    }

    public class Course
    {
        public int Id { get; set; }
        public string CourseName { get; set; }
        public string Description { get; set; }
        public ICollection<Student> Students { get; } = new HashSet<Student>();
    }

    public class StudentCourse
    {
        public int StudentId { get; set; }
        public Student Student { get; set; }
        public int CourseId { get; set; }
        public Course Course { get; set; }
    }

    public class Db: DbContext
    {
        public DbSet<Student> Students { get; set; }
        public DbSet<Course> Courses{ get; set; }
        public DbSet<StudentCourse> StudentCourses { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Student>().HasOne(s => s.MainCourse).WithMany().OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<Student>().HasMany(s => s.Courses).WithMany(c => c.Students).UsingEntity<StudentCourse>(
                sc => sc.HasOne(e => e.Course).WithMany(),
                sc => sc.HasOne(e => e.Student).WithMany()
                ); 

            base.OnModelCreating(modelBuilder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=localhost;database=efCore6Test;Integrated Security=true;TrustServerCertificate=true", o => o.UseRelationalNulls(true))
                .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
            base.OnConfiguring(optionsBuilder);
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            {
                
                using var db = new Db();
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();
                var joe = new Student() {Name="Joe" };
                var math = new Course() { CourseName = "Math" };
                var english = new Course() { CourseName = "English" };


                joe.Courses.Add(math);
                joe.Courses.Add(english);

                joe.MainCourse = english; 

                db.Students.Add(joe);

                db.SaveChanges();

            }

            {

                using var db = new Db();
                var joe = db.Students.Where(s => s.Name == "Joe").First();
                var math = db.Courses.Where(c => c.CourseName == "Math").First();
                joe.MainCourse = math;

                db.SaveChanges();
            }

        }
    }
}

Answer 2

Your db structure has a bug. You need to add List of StudentCourse to Course class too. IMHO it is not the best db structure I have seen. Much better way is to add Id to StudentCourse table and a flag if it is a main course.

public class StudentCourse
{
    public int Id { get; set; }
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public int CourseId { get; set; }
    public Course Course { get; set; }
    public bool IsMainCourse {get; set;}
}

and since you are using net5 you can add another lists to the classes

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Course> Courses { get; set; }
    public virtual List<StudentCourse> StudentCourses { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; }
    public string Description { get; set; }
    public virtual List<Student> Students{ get; set; }
    public virtual List<StudentCourse> StudentCourses { get; set; }
}

you need to make a new init migration to use this structure