Entity Framework 6, cascade delete of mapped join table

Question

I have simple "graph" representation stored in database. There is Node entity which has it's ID, Label and list of adjacent nodes.

public class Node
{
    [Key]
    public int Id { get; set; }

    [StringLength(128)]
    public string Label { get; set; }

    public virtual ICollection<Node> AdjacentNodes { get; set; }
}

Then in OnModelCreating on database context class, I have:

modelBuilder.Entity<Node>()
    .HasMany(n => n.AdjacentNodes)
    .WithMany()
    .Map(n => n.MapLeftKey("From").MapRightKey("To").ToTable("NodeEdge"));

Now, when I want to delete any node which has already any relation, I get constraint error:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.NodeEdge_dbo.Nodes_From". The conflict occurred in database "master", table "dbo.NodeEdge", column 'From'. The statement has been terminated.

Join table looks like this:

CREATE TABLE [dbo].[NodeEdge] (
    [From] INT NOT NULL,
    [To]   INT NOT NULL,
    CONSTRAINT [PK_dbo.NodeEdge] PRIMARY KEY CLUSTERED ([From] ASC, [To] ASC),
    CONSTRAINT [FK_dbo.NodeEdge_dbo.Nodes_From] FOREIGN KEY ([From]) REFERENCES [dbo].[Nodes] ([Id]),
    CONSTRAINT [FK_dbo.NodeEdge_dbo.Nodes_To] FOREIGN KEY ([To]) REFERENCES [dbo].[Nodes] ([Id])
);

Is there any way how to add ON DELETE CASCADE on join table constraints - so it will clear up all references when I'm deleting nodes? (... and keeping model simple without having join table entity)

Seems that `RemoveRange` helps to avoid constraint error, but still, join table is not cleared despite referenced items are already deleted (... sometimes I have really no idea what EF is doing) — Zdeněk, Nov 21 '15 at 10:26
`WillCascadeOnDelete` is not available for many-to-many relation unfortunately. — Zdeněk, Nov 21 '15 at 16:10
see this link maybe helped you http://stackoverflow.com/questions/29831729/asp-net-mvc-many-to-many-relationship-using-my-own-table/29832776#29832776 — Iraj, Nov 24 '15 at 06:25

score 0 · Accepted Answer · answered Nov 21 '15 at 21:44

When the many-to-many association is a self reference, EF chooses to create the foreign keys as not cascading. If it would make both keys cascading there would be a circular cascade, which Sql Server doesn't allow. Apparently, EF doesn't want to choose for you which of both keys should be cascading, so it chooses none.

You can however add the ON DELETE CASCADE clause to the FK_dbo.NodeEdge_dbo.Nodes_From foreign key afterwards, or add it in the migration Up method.

Entity Framework 6, cascade delete of mapped join table

1 Answers1