Entity framework foreign key constraint

asp.net-mvc c# entity-framework

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; }
}
1
4
5/23/2013 1:50:04 PM

Accepted Answer

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);
}
5
5/23/2013 2:23:16 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow