Entity Framework Error on Many to Many Relationship

Question

I have the following class for appointments:

public class Appointment
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int appointmentId { get; set; }

    public int Mins { get; set; }
    public DateTime StartDateTime { get; set; }
    public string Note { get; set; }

    //Navagation Properties
    public CompanyInformation Company { get; set; }

    public virtual ICollection<Service> Services { get; set; }

    public UserProfile Customer { get; set; }
    public UserProfile Staff { get; set; }

    public int? ParentID { get; set; }
    public Appointment ParentAppointment { get; set; }
}

and the following Service Class:

public class Service
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int serviceId { get; set; }
    public string name { get; set; }
    public string description { get; set; }
    public decimal price { get; set; }

    public bool isPublished { get; set; }
    public bool isActive { get; set; }

    public virtual CompanyInformation Company { get; set; }

    public UserProfile defaultStaff { get; set; }

    public virtual ICollection<Appointment> Appointments { get; set; }
}

I'm trying to create a many-to-many relationship between these two by:

 modelBuilder.Entity<Service>().HasMany(e => e.Appointments).WithMany(e => e.Services);

in the OnModelCreating of the DbContext. when I try to update the db I get the following error.

 Introducing FOREIGN KEY constraint 'FK_dbo.ServiceAppointments_dbo.Appointments_Appointment_appointmentId' on table 'ServiceAppointments' 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. See previous errors.

I've researched it a bit and found that the problem is with the Cascade delete and that I should disable it. The problem is that I don't know how and the options that I've found doesn't seem to work for me (they are apparently for one-to-many relationships). any help solving this issue would be greatly appreciated. Thanks in advance....

Answer 1

That should work fine as it is (you don't need the mapping for many-to-many, that works by convention). Which version EF/CF do you have ? But anyway, since it doesn't...

a) Try removing the convention as @Lajos mentioned - that should do the trick,

b) If that doesn't work, you can 'rewrite' the relationships manually - and turn off cascade, something like this...

modelBuilder.Entity<Appointment>()
    .HasOptional(x => x.ParentAppointment)
    .WithOptionalDependent()
    .WillCascadeOnDelete(false);

modelBuilder.Entity<ServiceAppointment>()
    .HasKey(x => new { x.ServiceId, x.AppointmentId });

modelBuilder.Entity<ServiceAppointment>()
    .HasRequired(x => x.Service)
    .WithMany(x => x.Appointments)
    .HasForeignKey(at => at.ServiceId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<ServiceAppointment>()
    .HasRequired(x => x.Appointment)
    .WithMany(x => x.Services)
    .HasForeignKey(at => at.AppointmentId)
    .WillCascadeOnDelete(false);

Just change collections in both classes to...

// public virtual ICollection<Appointment> Appointments { get; set; }
public virtual ICollection<ServiceAppointment> Appointments { get; set; }

// public virtual ICollection<Service> Services { get; set; }
public virtual ICollection<ServiceAppointment> Services { get; set; }

public class ServiceAppointment
{
    public int ServiceId { get; set; }
    public int AppointmentId { get; set; }
    public Service Service { get; set; }
    public Appointment Appointment { get; set; }
}

...that should definitely resolve the issue. Though you lose the 'Services' 'Appointments' direct navigation (all goes via ServiceAppointments)

Answer 2

protected override void OnModelCreating( DbModelBuilder modelBuilder )
      {

         modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

     modelBuilder.Entity<Appointment>()
        .WithRequiredDependent()
        .WillCascadeOnDelete( false );

     modelBuilder.Entity<Service>()
        .WithRequiredDependent()
        .WillCascadeOnDelete( false );

      }