Entity Framework Code First - Defining Relationships/Keys

Question

I am designing my database using code first and I need a little help I think.

I am getting this error:

Introducing FOREIGN KEY constraint 'SalesOrder_Invoices' on table 'Invoices' 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.

I am trying to have the following relationships/keys:

--> = 1 to Many Relationship

Customer --> CustomerLocation
CustomerLocation --> SalesOrder
SalesOrder --> Invoice
SalesRep --> SalesOrder
PaymentTerm --> Customer
PaymentTerm --> SalesOrder
PaymentTerm --> Invoice

I am trying to define them by the standard of:

<ClassName><PrimaryKeyID>

Example: Customer has ID property, so in CustomerLocation i define the foreign key like so:

Public Property CustomerID AS Integer

All I have to do is define the foreign key correct? Do I also have to have navigation properties for each key I define?

And, can I not have multiple foreign keys on the same primary key of an object?

Updated

So to define a relationship, do you use the ClassName.PrimaryKeyProperty? or do you use navigation properties? Or both? Confused!!

Update 2

So to make a relationship work you have to define both sides... I think.

Public Class Customer
    Public Property ID AS Integer
    Public Overrideable Property Locations AS ICollection(OF CustomerLocation)

End Class

Public Class CustomerLocation
    Public Property ID AS Integer
    Public Property CustomerID AS Integer

End Class

score 97 · Accepted Answer · edited Aug 19 '12 at 18:34

97

This is exception caused by SQL server when you have multiple paths of cascade deletes. If you delete your PaymentTerm it will trigger cascade delete on all three relations. This will blow up when creating either SalesOrder or Invoice. EF creates by default all one-to-many relations with ON DELETE CASCADE you can remap your specific relation to not use it by:

modelBuilder.Entity<...>()
            .HasRequired(...)
            .WithMany(...)
            .HasForeignKey(...)
            .WillCascadeOnDelete(false);

Or you can turn it off globaly by removing the convention:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

You can get around this error on a particular migration by editing the generated Up() method with a line something like this:

AddForeignKey("dbo.Payments", "EventID", "dbo.Events", "EventID", cascadeDelete: true)

and change that cascadeDelete: value to false on the offending relationship(s).

edited Aug 19 '12 at 18:34

Andrej Kyselica

846
9
8

answered Apr 03 '11 at 22:22

Ladislav Mrnka

360,892
59
660
670

So to make a relationship work, you have to define both sides? Please see my update above. – Sam Apr 04 '11 at 00:12
@Sam: No you don't need navigation property on both sides. – Ladislav Mrnka Apr 04 '11 at 06:16
13

I love this answer for the `OneToManyCascadeDeleteConvention` tip. – batwad Jan 31 '12 at 16:54
1

No need to call `HasForeignKey` in the fluent API unless the mapping does not follow standard conventions. – Edward Brey Mar 05 '13 at 03:08

score 4 · Answer 2 · edited May 23 '17 at 11:46

Read this, I am sure this will help you find the answer.

Also, according to ScottGu's blogpost, I think in general it should be that you just create the classes as follows (I didn't read it carefully enough, so you should check it out for further details):

public class Customer
{
    public int CustomerID { get; set; }
    public int CustomerLocationID { get; set; }
    public virtual CustomerLocation Location { get; set; }
}

public class CustomerLocation
{
    public int CustomerLocationID { get; set; }
    public virtual ICollection<Customer> Customers { get; set; }
}

Entity Framework Code First - Defining Relationships/Keys

2 Answers2

Linked