Entity Framework Core cascade delete one to many relationship

Question

public class Station : IEntitie
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<RegulatorySchedule> RegulatoryScheduleDispatchStations { get; set; }    

    public virtual ICollection<RegulatorySchedule> RegulatoryScheduleDestinationStations { get; set; }   
}

public class RegulatorySchedule : IEntitie
{
    [Key]
    public int Id { get; set; }

    public virtual Station DispatchStation { get; set; }      

    public virtual Station DestinationStation { get; set; }     
}


protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<RegulatorySchedule>()
            .HasOne(s => s.DestinationStation)
            .WithMany(s => s.RegulatoryScheduleDestinationStations)
            .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);

        modelBuilder.Entity<RegulatorySchedule>()
            .HasOne(s => s.DispatchStation)
            .WithMany(s => s.RegulatoryScheduleDispatchStations)
            .OnDelete(Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict);
}

The database is created during migration only when I clearly expose the behavior when deleting Restrict OnDelete (Microsoft.EntityFrameworkCore.Metadata.DeleteBehavior.Restrict). Otherwise, it throws an exception:

"Introducing FOREIGN KEY constraint 'FK_RegulatorySchedules_Stations_DispatchStationId' on table 'RegulatorySchedules' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

I need the removal of the Station Stations of the table and the table-related properties RegulatorySchedules DispatchStation and DestinationStation exposed to NULL. But Restrict option there is an exception when you delete a SetNull I can not put. Tell me how to be?

Answer 1

Described "problem" is not related to Entity Framework - this is restriction of MS SQL Server itself. Table with several FKs may have only one of them with cascade delete.

So, as soon as you need both FKs to have cascade - you should implement such "cleanup" in your code. Set one (or both) FKs to DeleteBehavior.Restrict, and in your controller/service prior to removing Station manually find and delete all related RegulatorySchedule

Answer 2

Dmitry's answer worked perfectly. For any future traveler a working sample of a mapping table down below.

The code is located in the OnModelCreating(ModelBuilder modelBuilder) method in your DbContext class:

modelBuilder.Entity<AB>()
            .HasKey(e => new { e.AId, e.BId});

modelBuilder.Entity<AB>()
            .HasOne(e => e.A)
            .WithMany(e => e.ABs)
            .HasForeignKey(e => e.AId)
            .OnDelete(DeleteBehavior.Cascade); // <= This entity has cascading behaviour on deletion

modelBuilder.Entity<AB>()
            .HasOne(e => e.B)
            .WithMany(e => e.ABs)
            .HasForeignKey(e => e.BId)
            .OnDelete(DeleteBehavior.Restrict); // <= This entity has restricted behaviour on deletion