CODEWITHSUNDEEP
c#entity-frameworkasp.net-core
si2030
si2030

Reputation: 4045

EF Core - may cause cycles or multiple cascade paths

I've set up what I thought was a pretty simple database.. However I am getting the following error.

Introducing FOREIGN KEY constraint 'FK_User_Suburb_SuburbId' on table 'User' 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 or index. See previous errors.

Here is my CATALOGCOntext:

using JobsLedger.CATALOG.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;

namespace JobsLedger.CATALOG
{
    public class CATALOGContext : DbContext
    {
        public DbSet<Tenant> Tenants { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Role> Roles { get; set; }
        public DbSet<State> States { get; set; }
        public DbSet<Suburb> Suburbs { get; set; }
        public DbSet<CATALOGCounter> Counters { get; set; }


        public CATALOGContext(DbContextOptions options) : base(options) { }



        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            foreach (var entity in modelBuilder.Model.GetEntityTypes())
            {
                entity.Relational().TableName = entity.DisplayName();
            }

            // User
            modelBuilder.Entity<User>().Property(u => u.UserName).IsRequired().HasMaxLength(50);
            modelBuilder.Entity<User>().Property(u => u.UserFirstName).IsRequired().HasMaxLength(100);
            modelBuilder.Entity<User>().Property(u => u.UserLastName).IsRequired().HasMaxLength(100);
            modelBuilder.Entity<User>().Property(u => u.Email).IsRequired().HasMaxLength(200);
            modelBuilder.Entity<User>().Property(u => u.HashedPassword).IsRequired().HasMaxLength(200);
            modelBuilder.Entity<User>().Property(u => u.Salt).IsRequired().HasMaxLength(200);

            modelBuilder.Entity<User>()
                .HasOne<Suburb>(s => s.Suburb)
                .WithMany(u => u.Users)
                .HasForeignKey(u => u.SuburbId)
                .IsRequired(false);

            // Role
            modelBuilder.Entity<Role>().Property(r => r.Name).IsRequired().HasMaxLength(50);

            modelBuilder.Entity<Role>()
                .HasOne<User>(u => u.User)
                .WithOne(r => r.Role)
                .HasForeignKey<User>(u => u.RoleId);

            // TenantAccount
            modelBuilder.Entity<Tenant>().Property(t => t.TenantNo).HasMaxLength(20);
            modelBuilder.Entity<Tenant>().Property(t => t.Company).HasMaxLength(100).IsRequired();
            modelBuilder.Entity<Tenant>().Property(t => t.ContactLastName).HasDefaultValue(false).IsRequired();
            modelBuilder.Entity<Tenant>().Property(t => t.Email).HasMaxLength(500).IsRequired();
            modelBuilder.Entity<Tenant>().Property(t => t.MobilePhone).HasMaxLength(20).IsRequired();
            modelBuilder.Entity<Tenant>().Property(t => t.OfficePhone).HasMaxLength(20);
            modelBuilder.Entity<Tenant>().Property(t => t.CompanyEmail).HasMaxLength(500);
            modelBuilder.Entity<Tenant>().Property(t => t.Address1).HasMaxLength(500);
            modelBuilder.Entity<Tenant>().Property(t => t.Address2).HasMaxLength(500);
            modelBuilder.Entity<Tenant>().Property(t => t.ABN).HasMaxLength(14);
            modelBuilder.Entity<Tenant>().Property(t => t.Database).HasMaxLength(100).IsRequired();
            modelBuilder.Entity<Tenant>().Property(t => t.IsLocked).HasDefaultValue(false);

            modelBuilder.Entity<Tenant>()
                .HasOne<User>(s => s.User)
                .WithMany(ta => ta.Tenants)
                .HasForeignKey(u => u.UserId);

            modelBuilder.Entity<Tenant>()
                .HasOne(s => s.Suburb)
                .WithMany(ta => ta.Tenants)
                .HasForeignKey(ta => ta.SuburbId);

            // State
            modelBuilder.Entity<State>().Property(s => s.StateShortName).HasMaxLength(3).IsRequired();
            modelBuilder.Entity<State>().Property(s => s.StateName).HasMaxLength(30).IsRequired();

            // Suburb
            modelBuilder.Entity<Suburb>().Property(s => s.SuburbName).HasMaxLength(3).IsRequired();
            modelBuilder.Entity<Suburb>().Property(s => s.PostCode).HasMaxLength(30).IsRequired();

            modelBuilder.Entity<Suburb>()
                .HasOne<State>(s => s.State)
                .WithMany(su => su.Suburbs)
                .HasForeignKey(st => st.StateId);
        }
    }
}

Here is my user:

...
        public int? SuburbId { get; set; }
        public Suburb Suburb { get; set; }

        public int RoleId { get; set; }
        public Role Role { get; set; }

        public virtual ICollection<Tenant> Tenants { get; set; }

Here is my Suburb which was also mentioned..

Wondering if someone might highlight why the migrations work but when I try and spin up a database it it errors with above error..

Simon

Upvotes: 16

Views: 19931

Answers (4)

Huynh Triet
Huynh Triet

Reputation: 181

The answers above are correct, but let me explain why you are running into this issue.

Basically, the User entity currently has relationship with both Suburb and Role, so you can not have CASCADING delete which would cause a conflict.

Let's say you delete a Role and it will also delete the user, but then how about the Suburb? You can see now the Suburb is not tied to anything, so that is why EF Core prevents this behavior. You have to specify NO ACTION so when a role or suburb is deleted the user won't be affected. This is a very common problem when an entity has relationship with multiple others.

In this case, it would not make sense either to remove the user just because role or suburb no longer exists.

Upvotes: 0

Ogglas
Ogglas

Reputation: 70028

For others that finds this question, this is enough:

modelBuilder.Entity<User>()
     .HasOne(u => u.Suburb)
     .WithMany(s => s.Users)
     .OnDelete(DeleteBehavior.Restrict);

If you don't have want a list property in Suburb with Users you can do it like this as well:

modelBuilder.Entity<User>()
     .HasOne(u => u.Suburb)
     .WithMany()
     .OnDelete(DeleteBehavior.Restrict);

Upvotes: 3

TanvirArjel
TanvirArjel

Reputation: 32069

Your User entity ForeignFey Fluent API configuration should be as follows:

modelBuilder.Entity<User>()
     .HasOne<Suburb>(s => s.Suburb)
     .WithMany(u => u.Users)
     .HasForeignKey(u => u.SuburbId)
     .IsRequired(false);
     .OnDelete(DeleteBehavior.Restrict); // <-- Here it is

Upvotes: 5

Ronald Haan
Ronald Haan

Reputation: 625

The error already says what you need to do. Specify what it must do when there is an action. You should add the .OnDelete() method to each foreign key definition.

modelBuilder.Entity<Tenant>()
                .HasOne<User>(s => s.User)
                .WithMany(ta => ta.Tenants)
                .HasForeignKey(u => u.UserId)
                .OnDelete(DeleteBehavior.Restrict);

For further information please read https://www.learnentityframeworkcore.com/configuration/fluent-api/ondelete-method

Upvotes: 20

Related Questions