I have complicated relationships between these entities:

• Country
• Airport
• Airline
• Flight

Country has many Airlines and many Airports. Airline has one Countries, the same about Airport. Airline has many Flights, Airport has many DepartureFlights and ArrivalFlights (both are Flight type). Flight has one Airline and one DepartureAirport and one ArrivalAirport (both are Airport type).

Country can have no airlines and airports, Airline can have no Flights, Airport can have neither DepartureFlights nor ArrivalFlights.

What I am trying to do is when Country is deleted, then all related Airlines and Airports are deleted, also when Airline or DepartureAirport or ArrivalAirport are deleted, all related Flights are deleted also, but when updating my db after the migration is created I'm getting an error: Introducing FOREIGN KEY constraint "FK_Flights_Airports_DepartureAirport" on table "Flights" may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

How to implement this behavior and prevent an error?

Here are my models:

1. Country:

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

        /* other properties */

        public virtual ICollection<Airport>? Airports { get; set; }

        public virtual ICollection<Airline>? Airlines { get; set; }
    }

2. Airline:

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

        /* other properties */

        public int CountryId { get; set; }

        public virtual Country Country { get; set; }

        public virtual ICollection<Flight>? Flights { get; set; }
    }

3. Airport:

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

        public string Name { get; set; }

        /* other properties */

        public int CountryId { get; set; }

        public virtual Country Country { get; set; }

        public virtual ICollection<Flight>? DepartureFlights { get; set; }

        public virtual ICollection<Flight>? ArrivalFlights { get; set; }
    }

4. Flight:

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

        /* other properties */

        public int AirlineId { get; set; }

        public int DepartureAirportId { get; set; }

        public int ArrivalAirportId { get; set; }

        public virtual Airline Airline { get; set; }

        public virtual Airport DepartureAirport { get; set; }

        public virtual Airport ArrivalAirport { get; set; }
    }

After all the DBContext file:

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

    public DbSet<Airline> Airlines { get; set; }
    public DbSet<Airport> Airports { get; set; }
    public DbSet<Country> Countries { get; set; }
    public DbSet<Flight> Flights { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Airline>(x =>
        {
            x.HasKey(a => a.Id);

            x.HasOne(c => c.Country)
            .WithMany(a => a.Airlines)
            .HasForeignKey(a => a.CountryId)
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
        });

        modelBuilder.Entity<Airport>(x =>
        {
            x.HasKey(a => a.Id);

            x.HasOne(c => c.Country)
            .WithMany(a => a.Airports)
            .HasForeignKey(a => a.CountryId)
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
        });

        modelBuilder.Entity<Country>(x =>
        {
            x.HasKey(c => c.Id);
        });

        modelBuilder.Entity<Flight>(x =>
        {
            x.HasKey(f => f.Id);

            x.HasOne(a => a.Airline)
            .WithMany(f => f.Flights)
            .HasForeignKey(f => f.AirlineId)
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();

            x.HasOne(a => a.DepartureAirport)
            .WithMany(f => f.DepartureFlights)
            .HasForeignKey(f => f.DepartureAirportId)
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();

            x.HasOne(a => a.ArrivalAirport)
            .WithMany(f => f.ArrivalFlights)
            .HasForeignKey(f => f.ArrivalAirportId)
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
        });
    }
}

CodePudding user response：

How to implement this behavior and prevent an error?

This is officially known issue. Therefore, We have two ways to handle this scenario thus the error:

1. Change one or more of the relationships to not cascade delete.

In this scenario, we could make the Country relationship with Airport, Flight and Airlines optional by giving it a nullable foreign key property: for instance we can do something like:

.IsRequired(false);

Note: You can check our official document for more details.

2. Configure the database without one or more of these cascade deletes, then ensure all dependent entities are loaded so that EF Core can perform the cascading behavior.

Considering this appreach we can keep the Airport, Flight and Airlines relationship required and configured for cascade delete, but make this configuration only apply to tracked entities, not the database: So we can do somethng like below:

modelBuilder.Entity<Airline>(x =>
        {
            x.HasKey(a => a.Id);

            x.HasOne(c => c.Country)
            .WithMany(a => a.Airlines)
            .HasForeignKey(a => a.CountryId)
            .OnDelete(DeleteBehavior.ClientCascade);
            .IsRequired(false);
        });

        modelBuilder.Entity<Airport>(x =>
        {
            x.HasKey(a => a.Id);

            x.HasOne(c => c.Country)
            .WithMany(a => a.Airports)
            .HasForeignKey(a => a.CountryId)
            .OnDelete(DeleteBehavior.ClientCascade);
            .IsRequired(false);
        });

Note: You can apply same for Flight as well. In addition, As you may know OnDelete(DeleteBehavior.ClientCascade); or ClientCascade allows the DBContext to delete entities even if there is a cyclic ref or LOCK on it. Please read the official guideline for more details here

CodePudding user response：

add these lines in "OnModelCreating"

 var cascades = modelBuilder.Model.GetEntityTypes()
                    .SelectMany(t => t.GetForeignKeys())
                    .Where(fk => !fk.IsOwnership && fk.DeleteBehavior == DeleteBehavior.Cascade);
    
    
    foreach (var fk in cascades)
         fk.DeleteBehavior = DeleteBehavior.Restrict;