Entity Framework: how to solve "FOREIGN KEY constraint may cause cycles or multiple cascade paths"?

Question

there are lots of questions about this problem, but I couldn't solve my case. can some one please take a look at this:

I have an Office table which has one-many relationship with Doctor and Secretary tables. Both of the last tables, are derived from Employee table which has a shared-primary-key relationship with predefined Users table created by sqlmembershipprovider. It seems there is a many-many relationship between Users table and Roles table which I haven't any hand in it.

My problem was in creating a (zero,one) - (one) relationship between my Employee table and that Users table which I ended with a shared primary key relationship between them and the error raised, then. (Is there a better solution for that problem?)

here is the error:

Introducing FOREIGN KEY constraint 'FK_dbo.aspnet_UsersInRoles_dbo.aspnet_Users_UserId' on table 'aspnet_UsersInRoles' 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. See previous errors.

here are my codes and membership codes after reverse engineering:

public class Office
{
    public Office()
    {
        this.Doctors = new HashSet<Doctor>();
        this.Secretaries = new HashSet<Secretary>();
    }

    [Key]
    public System.Guid OfficeId { get; set; }
    public virtual ICollection<Doctor> Doctors { get; set; }
    public virtual ICollection<Secretary> Secretaries { get; set; }
}

public class Employee
{
    [Key, ForeignKey("User")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public System.Guid Id { get; set; }
    public string Name { get; set; }

    [ForeignKey("Office")]
    public System.Guid OfficeId { get; set; }

    // shared primary key 
    public virtual aspnet_Users User { get; set; }

    public virtual Office Office { get; set; }
}

public class Doctor :Employee
{
    public Doctor()
    {
        this.Expertises = new HashSet<Expertise>();
    }
    //the rest..    
    public virtual ICollection<Expertise> Expertises { get; set; }
}

public class Secretary : Employee
{
    // blah blah
}

public class aspnet_Users
{
    public aspnet_Users()
    {
        this.aspnet_Roles = new List<aspnet_Roles>();
    }

    public System.Guid ApplicationId { get; set; }
    public System.Guid UserId { get; set; }
    //the rest..
    public virtual aspnet_Applications aspnet_Applications { get; set; }
    public virtual ICollection<aspnet_Roles> aspnet_Roles { get; set; }
}

public class aspnet_Roles
{
    public aspnet_Roles()
    {
        this.aspnet_Users = new List<aspnet_Users>();
    }

    public System.Guid ApplicationId { get; set; }
    public System.Guid RoleId { get; set; }
    //the rest..
    public virtual aspnet_Applications aspnet_Applications { get; set; }
    public virtual ICollection<aspnet_Users> aspnet_Users { get; set; }
}

EDIT: and the relationships go deeper, there is a many-one relationship between Users table and Applications table, also between Roles and Applications too.

Adverbial

Let me get this right, are you using the same value for the PK in the Employee and Users Table? – Karlenekarlens

yes, as I expect there should be a user value first then I use it in Employee. the error raises just in initialization. – Adverbial

Answer 1

You can use the fluent api to specify the actions the error message suggests.

In your Context:

protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<aspnet_UsersInRoles>().HasMany(i => i.Users).WithRequired().WillCascadeOnDelete(false);
}

Note that you have not included the definition for the table aspnet_UsersInRoles so this code may not work.

Another option is to remove all CASCADE DELETES by adding this

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

If you need more info about configuring relationships with the fluent api I suggest http://msdn.microsoft.com/en-US/data/jj591620

Kristie

Hi Charles. I can't manipulate Users or Roles or UsersInRoles because then membership provider will stop working. Can I change the circle somewhere in my tables? – Adverbial

Well EF is trying to modify those tables. Your error comes from EF trying to add a foreign key constraint. Try adding [NotMapped] to each of the aspnet_* class definitions. This will allow you to use the classes but EF won't try to create tables for them. – Kristie

NOTE: if using this override method, remember to call base.OnModelCreating(modelBuilder); too, otherwise other convention defaults won't be set. For me this caused new errors, such as EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType. – Herbst

What if you're using database-first design? Does this answer still apply? – Arsenault

Answer 2

Also you can modify your migration class. In my case in the migration class was:

CreateTable(
    "dbo.Spendings",
    c => new
        {
          SpendingId = c.Int(nullable: false, identity: true),
          CategoryGroupId = c.Int(nullable: false),
          CategoryId = c.Int(nullable: false),
          Sum = c.Single(nullable: false),
          Date = c.DateTime(nullable: false),
          Comment = c.String(),
          UserId = c.String(),
          LastUpdate = c.String(),
        })
    .PrimaryKey(t => t.SpendingId)
    .ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true)
    .ForeignKey("dbo.CategoryGroups", t => t.CategoryGroupId, cascadeDelete: true)
    .Index(t => t.CategoryGroupId)
    .Index(t => t.CategoryId);

After removing "cascadeDelete: true" Update-Database works perfectly.

OR as false

 .ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: false)

Spacial

Answer 3

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    }

Add this code in context

Reiko

Do I want to use this? What kind of negative effects can it have? – Gondola

This is a solution that I sometimes use but this is because I rarely want anything to cascade delete - I find it can be dangerous. However, some people may want to cascade delete certain things (it can be useful in the right situation) so the negative effect is that you will have to delete every item associated with/ using a particular foreign key before you can delete the item itself. It may be necessary to do something more like the "best answer" if you still want some things to cascade delete. – Jemison

Answer 4

Just an Update:

As other answers suggest you need to NOT do any action on delete. Updated way of doing this is as follows for Entityframework Core 1.0

table.ForeignKey(name: "FK_Cities_Regions_RegionId",
                 column: x => x.RegionId,
                 principalTable: "Regions",
                 principalColumn: "RegionId",
                 onDelete: ReferentialAction.NoAction);

NOTE: Do ReferentialAction.NoAction on onDelete

Cowlick

Answer 5

Based on your comments, the problem is that you're using the same PK in both tables. I'd change that right away, have Users PK as an extra field in Employees with a FK relationship to Users (one to one i guess). And add an independent PK to users (Another guid to respect the rest of your tables). If you want to guarantee that the new foreign key in Employees (the one that points to Users) is unique, you can always add a Unique Index.

That way you keep your structure all the same and you get rid of the cycle.

Karlenekarlens