Failed to Migration while FK

Question

I Create new fresh database and i was adding tables using EFcore Migrations following are my classes but when i migrate i run add script they run successful but when i run update they gave me error

Failed executing DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

CREATE TABLE [Clients] (
    [Id] int NOT NULL IDENTITY,
    [FirstName] nvarchar(max) NOT NULL,
    [LastName] nvarchar(max) NOT NULL,
    [PhoneNo] nvarchar(max) NOT NULL,
    [Address] nvarchar(max) NOT NULL,
    [Budget] decimal(18,2) NOT NULL,
    [PropertyType] nvarchar(max) NOT NULL,
    [PreferredLocation] nvarchar(max) NOT NULL,
    [Description] nvarchar(max) NOT NULL,
    [CityID] int NOT NULL,
    [StateID] int NOT NULL,
    CONSTRAINT [PK_Clients] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Clients_Cities_CityID] FOREIGN KEY ([CityID]) REFERENCES [Cities] ([CityID]) ON DELETE CASCADE,
    CONSTRAINT [FK_Clients_States_StateID] FOREIGN KEY ([StateID]) REFERENCES [States] ([StateID]) ON DELETE CASCADE
);

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

Classes

City

 public class City
 {
     public int CityID { get; set; }
     public string CityName { get; set; }

     //Foreign key property
     public int StateID { get; set; }

     // Navigation property for related State
     public State State { get; set; }

     // Navigation property for related Clients
     public ICollection<Clients> Clients { get; set; }
 }

State

public class State
{
    public int StateID { get; set; }
    public string StateName { get; set; }

    // Navigation property for related Cities
    public ICollection<City> Cities { get; set; }

    // Navigation property for related Clients
    public ICollection<Clients> Clients { get; set; }

}

Clients

  public class Clients
  {
      public int Id { get; set; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public string PhoneNo { get; set; }
      public string Address { get; set; }
      public decimal Budget { get; set; }
      public string PropertyType { get; set; }
      public string PreferredLocation { get; set; }
      public string Description { get; set; }

      // Foreign key properties
      public int CityID { get; set; }
      public int StateID { get; set; }

      // Navigation properties
      public City City { get; set; }
      public State State { get; set; }
  }

Answer 1

This error is clearly that you have Cascade Delete Conflicts.

You have added multiple foreign key relationships with the City , STATE and Clients. They all have relationship for each other which caused this issue.

So if you update the model ,it will caused the Cascade Delete Conflicts, you should re-design the relationship to avoid cycles or multiple cascade paths.

If you don't want to re-design the model , you need modify the EF configuration to set the ON DELETE NO ACTION or ON UPDATE NO ACTION.

More details, you could refer to below codes to modify the dbcontext to add the .OnDelete(DeleteBehavior.Restrict); property:

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<Clients> Clients { get; set; }
    public DbSet<City> Cities { get; set; }
    public DbSet<State> States { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configuring the Client-City relationship
        modelBuilder.Entity<Clients>()
            .HasOne(c => c.City)
            .WithMany(city => city.Clients)
            .HasForeignKey(c => c.CityID)
            .OnDelete(DeleteBehavior.Restrict); // No action on delete

        // Configuring the Client-State relationship
        modelBuilder.Entity<Clients>()
            .HasOne(c => c.State)
            .WithMany(state => state.Clients)
            .HasForeignKey(c => c.StateID)
            .OnDelete(DeleteBehavior.Restrict); // No action on delete

        // Configuring the City-State relationship
        modelBuilder.Entity<City>()
            .HasOne(c => c.State)
            .WithMany(state => state.Cities)
            .HasForeignKey(c => c.StateID)
            .OnDelete(DeleteBehavior.Restrict);// No action on delete
    }


}