Entity Framework Code First - Multiple FKs to different parent tables

Question

Looks like a "yet another EF multiple FK" question but please continue reading. I have the following requirements:

• A "Monitor" can have zero or more "Notifier"
• A "Monitor" can have zero or more "Setting"
• A "Notifier" can have zero or more "Setting"

So, deleting a "Monitor" will delete it's "Setting"s and "Notifiers". Also, since a "Notifier" may have zero or more Settings, related "Setting"s should be removed as well.

I have created the following model, but hit the famous "... may cause cycles or multiple cascade paths" error.

The closest answer was Entity Framework Cascading Delete but it uses DB first approach.

Can anyone suggest a workaround to enable cascaded deletes to above model?

And here is my model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Testbed
{
    public class Monitor
    {
        public int Id { get; set; }
        [Column(TypeName = "varchar")]
        [MaxLength(255)]
        public string Type { get; set; }
        [Column(TypeName = "varchar")]
        [MaxLength(20)]
        public string RunFrequency { get; set; }
        public List<Setting> Settings { get; set; }
        public List<Notifier> Notifiers { get; set; }
    }

    public class Setting
    {
        public int Id { get; set; }
        [MaxLength(255)]
        [Column(TypeName = "varchar")]
        public string Name { get; set; }
        [MaxLength(512)]
        public string Value { get; set; }
        public bool IsPassword { get; set; }
    }

    public class Notifier
    {
        public int Id { get; set; }
        [MaxLength(255)]
        [Column(TypeName = "varchar")]
        public string Name { get; set; }
        public List<Setting> Settings { get; set; }

    }
}

Answer 1

In your Setting and Notifier classes you will have to add some properties to allow the relationship.

public class Setting
{
    public int Id { get; set; }
    [MaxLength(255)]
    [Column(TypeName = "varchar")]
    public string Name { get; set; }
    [MaxLength(512)]
    public string Value { get; set; }
    public bool IsPassword { get; set; }

    //Add these properties
    public int ForeignId{ get; set; }
    public virtual Monitor Monitor { get; set; }
    public virtual Notifier Notifier { get; set; }
}

public class Notifier
{
    public int Id { get; set; }
    [MaxLength(255)]
    [Column(TypeName = "varchar")]
    public string Name { get; set; }
    public List<Setting> Settings { get; set; }

    //Add these properties
    public int MonitorId { get; set; }
    public virtual Monitor Monitor { get; set; }

}

Now that you have the relationships enabled you will need to add the mappings. This can be done either in your EntityModel or in your mapping class if you have them separated.

modelBuilder.Entity<Setting>()
    .HasOptional(e => e.Monitor) //Could be .HasRequired()
    .WithMany(e => e.Settings)
    .HasForeignKey(e => e.ForeignId)
    .WillCascadeOnDelete(true);

modelBuilder.Entity<Setting>()
    .HasOptional(e => e.Notifier) //Could be .HasRequired()
    .WithMany(e => e.Settings)
    .HasForeignKey(e => e.ForeignId)
    .WillCascadeOnDelete(true);

modelBuilder.Entity<Notifier>()
    .HasRequired(e => e.Monitor) //Could be .HasOptional()
    .WithMany(e => e.Notifiers)
    .HasForeignKey(e => e.MonitorId)
    .WillCascadeOnDelete(true);

The way this is setup is always going to cause problems. Your Settings table is being used by multiple tables. In my example I have made a single foreign key in Settings that is used by the other two tables. This can also be split if you wanted into MonitorId and NotifierId. It all depends on your data model.

EDIT: In the case that you need MonitorId and NotifierId.

modelBuilder.Entity<Setting>()
    .HasOptional(e => e.Monitor) //Could be .HasRequired()
    .WithMany(e => e.Settings)
    .HasForeignKey(e => e.MonitorId)
    .WillCascadeOnDelete(true);

modelBuilder.Entity<Setting>()
    .HasOptional(e => e.Notifier) //Could be .HasRequired()
    .WithMany(e => e.Settings)
    .HasForeignKey(e => e.NotifierId)
    .WillCascadeOnDelete(true);

You will also have to add those properties to the Setting class.

public class Setting
{
    public int MonitorId { get; set; }
    public int NotifierId { get; set; }
}