Entity Framework Core 5 - 在表上引入 FOREIGN KEY 约束可能会导致循环或多个级联路径

以下是所示模型中的级联删除路径

1. 诊所 -> 咨询
2. 医生 -> 咨询
3. 患者 -> 咨询
4. 医生 -> 患者 -> 咨询

问题（多个级联路径）是最后两个。如您所见，删除 a 时Doctor，Consultation可以直接删除链接Patient记录或查看链接记录。由于这种可能性，一些数据库（主要是 SqlServer）拒绝级联删除选项，并要求您至少为形成循环的一个关系关闭它，并手动或通过触发器处理删除。

所以通常这就是当这种循环存在时你应该做的。

但这里看起来模型有问题。要么Patient不应该链接到单个Doctor，而是通过链接表和删除Patient.Doctor导航属性（因此关联的 FK 关系）链接到多个，从而自然地打破多个级联路径，即Doctor删除只删除诊所和患者的链接，而不是诊所和患者的链接他们自己。

或者，如果你想保持Patient单身Doctor关系，那么Consultation.Doctor（以及相关的Consultation.DoctorIdFK 和关系）是多余的 - 咨询的医生可以通过 Consulting.Patient.Doctor 获得）。所以删除它，这也将解决多个级联路径问题，因为将不再有Doctor -> Consultation级联删除链接。

为清楚起见，第一个建议选项需要以下模型更改：

[Table("Clinics")]
public class Clinic // Unchanged
{
    public int ClinicID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string Address { get; set; }
    public List<Doctor> DoctorsAvailable { get; set; } = new List<Doctor>();
}

[Table("Doctors")]
public class Doctor
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public List<Clinic> ClinicsAvailableAt { get; set; } = new List<Clinic>();
    public ICollection<Patient> Patients { get; set; } // <-- added
}

[Table("Patients")]
public class Patient
{
    public int PatientID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    //public int DoctorID { get; set; } <-- removed
    //public Doctor Doctor { get; set; } <-- removed
    public ICollection<Doctor> Doctors { get; set; } // <-- added
}


[Table("Consultations")]
public class Consultation // Unchanged
{
    public int ID { get; set; }
    public int ClinicID { get; set; }
    public int DoctorID { get; set; }
    public int PatientID { get; set; }
    public Clinic Clinic { get; set; }
    [ForeignKey("DoctorID")]
    public Doctor Doctor { get; set; }
    [ForeignKey("PatientID")]
    public Patient Patient { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

和选项2：

[Table("Clinics")]
public class Clinic // Unchanged
{
    public int ClinicID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string Address { get; set; }
    public List<Doctor> DoctorsAvailable { get; set; } = new List<Doctor>();
}

[Table("Doctors")]
public class Doctor // Unchanged
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public List<Clinic> ClinicsAvailableAt { get; set; } = new List<Clinic>();
}

[Table("Patients")]
public class Patient // Unchanged
{
    public int PatientID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public int DoctorID { get; set; }
    public Doctor Doctor { get; set; }
}


[Table("Consultations")]
public class Consultation
{
    public int ID { get; set; }
    public int ClinicID { get; set; }
    //public int DoctorID { get; set; } <-- removed
    public int PatientID { get; set; }
    public Clinic Clinic { get; set; }
    //[ForeignKey("DoctorID")]
    //public Doctor Doctor { get; set; } <-- removed
    [ForeignKey("PatientID")]
    public Patient Patient { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}