SQL Server Problem foreign key between tables in ASP.NET Core 6 MVC & EF Core

xt0899hw  于 2023-02-28  发布在  .NET
关注(0)|答案(1)|浏览(110)

I have a problem in my new project, I work in ASP.NET Core 6 and Entity Framework Core. In my project I have 10 tables, the problem comes from the relation between all the tables. Last time I posted a part of my project, you proposed to me some changes and advice, I make the changes but I found another type of problem, I tried very hard to solve it but I cannot.

I decided to share with you all my project and I need some helps please.

The project is about a school management system

Models:

public class Classe
{
    [Key]
    public int Id { get; set; }
    public string  ClassName { get; set; }
    public ICollection<Examen> Examens { get; set; }
    public ICollection<Subject> Subjects { get; set; }
    public ICollection<Student> Students { get; set; }
    public ICollection<TeacherSubject> TeacherSubjects { get; set; }
    public ICollection<Expense> Expenses { get; set; }
    public ICollection<Fee> Fees { get; set; }
    public ICollection<StudentAttendence> StudentAttendence { get; set; }
}

public class Examen
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public int Marks { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }

    public virtual Subject Subject { get; set; }
    [ForeignKey(nameof(Subject))]
    public int SubjectId { get; set; }
}

public class Expense
{
    public int Id { get; set; }
    public int ChargeAmount { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }

    public virtual Subject Subject { get; set; }
    [ForeignKey(nameof(Subject))]
    public int SubjectId { get; set; }
}

public class Fee
{
    public int Id { get; set; }
    public int FeeAmount { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DOB { get; set; }
    public string Gender { get; set; }
    public string Mobile { get; set; }
    public string RollNo { get; set; }
    public string Adresse { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }
}

public class StudentAttendence
{
    public int Id { get; set; }
    public string RollNo { get; set; }
    public byte Status { get; set; }
    public DateTime date { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }

    public virtual Subject Subject { get; set; }
    [ForeignKey(nameof(Subject))]
    public int SubjectId { get; set; }
}

public class Subject
{
    public int Id { get; set; }
    public string SubjectName { get; set; }
    public ICollection<Examen> Examens { get; set; }
    public ICollection<TeacherSubject> TeacherSubjects { get; set; }
    public ICollection<Expense> Expenses { get; set; }
    public ICollection<StudentAttendence> StudentAttendence { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }
}

public class Teacher
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DOB { get; set; }
    public string Gender { get; set; }
    public string Mobile { get; set; }
    public string Email { get; set; }
    public string Adresse { get; set; }
    public string PassWord { get; set; }
    public ICollection<TeacherSubject> TeacherSubjects { get; set; }
    public ICollection<TeacherAttendence> TeacherAttendence { get; set; }
}

public class TeacherAttendence
{
    public int Id { get; set; }
    public byte Status { get; set; }
    public DateTime date { get; set; }

    public virtual Teacher Teacher { get; set; }
    [ForeignKey(nameof(Teacher))]
    public int TeacherId { get; set; }
}

public class TeacherSubject
{
    public int Id { get; set; }

    public virtual Classe Classe { get; set; }
    [ForeignKey(nameof(Classe))]
    public int ClasseId { get; set; }

    public virtual Subject Subject { get; set; }
    [ForeignKey(nameof(Subject))]
    public int SubjectId { get; set; }

    public virtual Teacher Teacher { get; set; }
    [ForeignKey(nameof(Teacher))]
    public int TeacherId { get; set; }
}

My DbContext :

public class SchoolContext : DbContext
{
    public SchoolContext(DbContextOptions<SchoolContext> options):base(options)
    {
    }

    public DbSet<Classe> Classes { get; set; }
    public DbSet<Examen> Examens { get; set; }
    public DbSet<Subject> Subjects { get; set; }
    public DbSet<Student> Students { get; set; }
    public DbSet<Teacher> Teachers { get; set; }
    public DbSet<TeacherSubject> TeacherSubjects { get; set; }
    public DbSet<Expense> Expenses { get; set; }

    public DbSet<Fee> Fees { get; set; }
    public DbSet<StudentAttendence> StudentAttendences { get; set; }
    public DbSet<TeacherAttendence> TeacherAttendences { get; set; }
}

When I add a migration, I always get this problem :

Introducing a FOREIGN KEY constraint 'FK_Examens_Subjects_SubjectId' on the 'Exams' table may cause cycles or multiple cascad accesses. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Unable to create constraint or index.

I represented my database in Excel :

Can someone please help me to find how can I fix this ?

Thanks in advance

7cwmlq89

7cwmlq891#

There is a problem with your multi-cascade relationship. The easiest way is to cancel the relationship between Classe and Subject :

public class Classe
{
    [Key]
    public int Id { get; set; }
    public string ClassName { get; set; }
    public ICollection<Examen> Examens { get; set; }
    //public ICollection<Subject> Subjects { get; set; }
    public ICollection<Student> Students { get; set; }
    public ICollection<TeacherSubject> TeacherSubjects { get; set; }
    public ICollection<Expense> Expenses { get; set; }
    public ICollection<Fee> Fees { get; set; }
    public ICollection<StudentAttendence> StudentAttendence { get; set; }
}
public class Subject
{
    public int Id { get; set; }
    public string SubjectName { get; set; }
    public ICollection<Examen> Examens { get; set; }
    public ICollection<TeacherSubject> TeacherSubjects { get; set; }
    public ICollection<Expense> Expenses { get; set; }
    public ICollection<StudentAttendence> StudentAttendence { get; set; }

    //public virtual Classe Classe { get; set; }
    //[ForeignKey(nameof(Classe))]
    //public int ClasseId { get; set; }
}

For details, you can refer to this link .

相关问题