30

I would like to enable CASCADE DELETE on a table using code-first. When the model is re-created from scratch, there is no CASCADE DELETE set even though the relationships are set-up automatically. The strange thing is that it DOES enable this for some tables with a many to many relationship though, which you would think it might have problems with.

Setup: Table A <- Table B.

Table B's FK points to Table A's PK.

Why would this not work?

user2771704
  • 5,994
  • 6
  • 37
  • 38
jaffa
  • 26,770
  • 50
  • 178
  • 289
  • 1
    Is FK required (non-nullable)? In such case cascade deletes should be generated by `OneToManyCascadeDeleteConvention`. – Ladislav Mrnka Mar 29 '11 at 11:12
  • 1
    @Ladislav: Is this good to remove the general `entity-framework` tag? I was just wondering why this question suddenly disappeared from the questions under my favorite tags. I've added now `entity-framework-4.1` and `entity-framework-4` and...and...and... but it's getting a bit convoluted now to check all those tags. I am not sure if it wouldn't be better to have a hierarchy of gradually specializing tags on a question. I see a risk that less readers will see questions with only very specialized tags which is not in the interest of the questioner. – Slauma Mar 29 '11 at 11:59
  • 1
    @Slauma: I agree, but either we will insist on closing both EF 4 and EF 4.1 tag and we will use only EF tag (which I thing is correct way) or we will tag questions depending on their content. I will open the question on meta about this topic soon, because there is more problems with EF tags. – Ladislav Mrnka Mar 29 '11 at 12:03
  • @Ladislav: Could you put a link here when you have asked on Meta? Personally I find a hierarchical approach is OK in my opinion. For instance I had probably tagged this question here with `.net`, `entity-framework`, `entity-framework-4.1`, `ef-code-first` (or only `code-first` since I saw that Mortezza proposed this retagging under tag synonyms). But I admit that this might be too complicated, especially for newcomers and the risk is high that tagging can become chaotic if we have so many specialized tags. – Slauma Mar 29 '11 at 12:17
  • @Slauma: I already requested some tag merging without any feedback yet: http://meta.stackexchange.com/questions/82432/request-for-tags-merging – Ladislav Mrnka Mar 29 '11 at 12:23

2 Answers2

60

Possible reason why you don't get cascading delete is that your relationship is optional. Example:

public class Category
{
    public int CategoryId { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public Category Category { get; set; }
}

In this model you would get a Product table which has a foreign key to the Category table but this key is nullable and there is no cascading delete setup in the database by default.

If you want to have the relationship required then you have two options:

Annotations:

public class Product
{
    public int ProductId { get; set; }
    [Required]
    public Category Category { get; set; }
}

Fluent API:

modelBuilder.Entity<Product>()
            .HasRequired(p => p.Category)
            .WithMany();

In both cases cascading delete will be configured automatically.

If you want to have the relationship optional but WITH cascading delete you need to configure this explicitely:

modelBuilder.Entity<Product>()
            .HasOptional(p => p.Category)
            .WithMany()
            .WillCascadeOnDelete(true);

Edit: In the last code snippet you can also simply write .WillCascadeOnDelete(). This parameterless overload defaults to true for setting up cascading delete.

See more on this in the documentation

Carrie Kendall
  • 11,124
  • 5
  • 61
  • 81
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • Does this mean that if you don't specify the convention, WillCascadeOnDelete() is off by default? – jaffa Mar 29 '11 at 12:57
  • 3
    @Jon: No, CascadeOnDelete is not generally off. The convention says: It's on if your relationship/navigation property is "required" (=foreign key/reference are forbidden to be null). But it's off if your relationship/navigation is "optional" (=foreign key/reference are allowed to be null). You can overwrite the convention in both cases: You can switch off cascading delete for a required navigation property and you can switch it on for an optional navigation property. You can also switch off the "OneToManyCascadeDeleteConvention". Then you have always to specify what you want in the Fluent API. – Slauma Mar 29 '11 at 14:39
  • @Slauma I have read that relationships are assumed to be required if the foreign key is not nullable which is the case with your first example. Read "Relationship Convention" here: http://msdn.microsoft.com/en-us/data/jj679962.aspx – ajbeaven Oct 21 '13 at 08:09
  • 1
    @ajbeaven: No, the first example doesn't have a foreign key property at all (there is no `Product.CategoryId` property). The relationship would be optional by convention. – Slauma Oct 21 '13 at 10:39
  • Whoops, didn't see the missing CategoryId. Cool, I've never seen implicit foreign keys like that. Can you still reference `Product.CategoryId` using this convention? – ajbeaven Oct 21 '13 at 22:09
  • 1
    Also, cascading deletes are not turned on when using inheritance with EF, even if the relationship is marked as required. – ajbeaven Oct 22 '13 at 06:01
  • 2
    +1 this is more informative than the doc. Thanks for the specifics regarding optional relationships – Carrie Kendall Oct 03 '14 at 19:23
  • Bizarrely enough in EF6 it still doesn't generate the FK constraint as ON CASCADE DELETE for my optional relationship even though I'm using `modelBuilder.Entity().HasOptional(f => f.Bar).WithMany().HasForeignKey(f => f.BarId).WillCascadeOnDelete(true);` Anyone else running into this? – Joren Jun 03 '15 at 13:04
0
modelBuilder
.Entity<Product>()
.HasRequired(p => p.Category)
.WithMany(x => x.Products)
.WillCascadeOnDelete(true);
Peter Barbanyaga
  • 496
  • 7
  • 16