Entity framework foreign key constraint

Question

I'm still getting my head around using EF. I using a code first approach in my project and stumbled upon the following issue.

I have the following objects:

public class Employee
{
    public int EmployeeId { get; set; }
    public int BusinessUnitId { get; set; }

    public virtual BusinessUnit BusinessUnit { get; set; }
}

public class Quote
{
    public int QuoteId { get; set; }

    [DisplayName("Business Unit")]
    public int BusinessUnitId { get; set; }

    [DisplayName("Responsible Employee")]
    public int EmployeeId { get; set; }

    [DisplayName("Date Issued")]
    [DataType(DataType.Date)]
    public DateTime DateIssued { get; set; }

    [DataType(DataType.MultilineText)]
    public string Description { get; set; }

    public virtual Employee Employee { get; set; }
    public virtual BusinessUnit BusinessUnit { get; set; }
}

Both include a BusinessUnit property, and it seems that EF doesn't want to allow this. Seeing that I get the following error below on the Index() method when a Linq query with a bunch of includes are executed.

Introducing FOREIGN KEY constraint 'FK_dbo.Quotes_dbo.BusinessUnits_BusinessUnitId' on table 'Quotes' 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.

Can someone please explain to me why I get this error and how I might go about fixing it. Thanks.

EDIT:

This is definitly caused by including the BusinessUnit property in both the Quote object and the Employee object. I just dont understand why.

EDIT 2:

The code for the BusinessUnit class:

public class BusinessUnit
{
    public int BusinessUnitId { get; set; }
    public string Name { get; set; }
}

http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths — Oskar, May 23 '13 at 13:46
What DbSets do you have defined, and what does your overridden OnModelCreate contain? — nullforce, May 23 '13 at 13:57
Why do you include the int BusinessUnitId in the Quote and Employee classes? My guess is that it's a conflict between the int properties and the BusinessUnit properties — Daniel, May 23 '13 at 14:08
I think it might be because if you were to have the same business unit referenced by employee and quote and there is no way to delete the quote. Try making one of the relationships optional — undefined, May 23 '13 at 14:15

score 5 · Accepted Answer · answered May 23 '13 at 14:23

Right now, if you try to delete an Employee, it will attempt to delete the Quote, the BusinessUnit for that Quote, and then the Employees for that BusinessUnit, etc.

Either make some relationships optional, or turn off cascading conventions as below:

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

    // or for one model object
    modelBuilder.Entity<Person>()
       .HasRequired(p => p.Department)
       .WithMany()
       .HasForeignKey(p => p.DepartmentId)
       .WillCascadeOnDelete(false);
}

score 0 · Answer 2 · answered May 23 '13 at 14:15

0

Adding comment as answer: It's a conflict between the BusinessUnitId properties and the BusinessUnit properties

Remove the BusinessUnitId property from Quote and Employee classes

answered May 23 '13 at 14:15

Daniel

622
4
6

score 0 · Answer 3 · answered May 23 '13 at 14:40

At the core of this issue is SQL Server's refusal to allow circular referenced cascading deletes (as the SO link from @Oskar discusses). EF is relaying an exception from SQL Server to you. It is being triggered by code first's default behavior: "If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null."

You should be able to overcome this issue by making at least one of your BusinessUnitId properties nullable, either on Quote or Employee. Alternatively, you can use EF's fluent api to specify cascade delete rules.

Entity framework foreign key constraint

3 Answers3