Entity Framework 6: Code First Cascade delete

c# entity-framework entity-framework-6 sql

Question

So there are several similar questions on here to this, but I'm still having issues determing what exactly I'm missing in my simplified scenario.

Let's say I have the following tables, cleverly named after myself:

'JohnsParentTable' (Id, Description) 
'JohnsChildTable' (Id, JohnsParentTableId, Description)

With the resulting classes looking like so

public class JohnsParentTable
{
    public int Id { get; set; }
    public string Description { get; set; }
    public virtual ICollection<JohnsChildTable> JohnsChildTable { get; set; }

    public JohnsParentTable()
    {
        JohnsChildTable = new List<JohnsChildTable>();
    }
}

internal class JohnsParentTableConfiguration : EntityTypeConfiguration<JohnsParentTable>
{
    public JohnsParentTableConfiguration()
    {
        ToTable("dbo.JohnsParentTable");
        HasKey(x => x.Id);
        Property(x => x.Id).HasColumnName("Id").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.Description).HasColumnName("Description").IsRequired().HasMaxLength(50);
    }
}

public class JohnsChildTable
{
    public int Id { get; set; }
    public string Description { get; set; }
    public int JohnsParentTableId { get; set; }
    public JohnsParentTable JohnsParentTable { get; set; }
}

internal class JohnsChildTableConfiguration : EntityTypeConfiguration<JohnsChildTable>
{
    public JohnsChildTableConfiguration()
    {
        ToTable("dbo.JohnsChildTable");
        HasKey(x => x.Id);
        Property(x => x.Id).HasColumnName("Id").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.Description).HasColumnName("Description").IsRequired().HasMaxLength(50);
        HasRequired(a => a.JohnsParentTable).WithMany(c => c.JohnsChildTable).HasForeignKey(a => a.JohnsParentTableId);
    }
}

In the database I have a row in the parent table with an Id of 1 along with two rows in the child table tied to this parent. If I do this:

var parent = db.JohnsParentTable.FirstOrDefault(a => a.Id == 1)

The object is correctly populated. However, if I try to delete this row:

var parent = new Data.Models.JohnsParentTable() { Id = 1 };
db.JohnsParentTable.Attach(parent);
db.JohnsParentTable.Remove(parent);

db.SaveChanges();

Entity framework tries to execute the following:

DELETE [dbo].[JohnsParentTable]
WHERE ([Id] = @0)
-- @0: '1' (Type = Int32)
-- Executing at 1/23/2014 10:34:01 AM -06:00
-- Failed in 103 ms with error: The DELETE statement conflicted with the REFERENCE constraint "FK_JohnsChildTable_JohnsParentTable". The conflict occurred in database "mydatabase", table "dbo.JohnsChildTable", column 'JohnsParentTableId'.
The statement has been terminated.

My question then is what exactly am I missing to ensure Entity Framework knows that it must delete the 'JohnsChildTable' rows before deleting the parent?

Accepted Answer

It depends on whether you want Entity Framework to delete the children, or you want the database to take care of it.

If you want EF to generate a delete statement for all the children and execute those before deleting the parent, you have to load all the children into memory first.

So you can't simply create a "dummy" entity with just the key populated, and expect the children to be deleted.

For that to work, you have to let the database handle the deletion.

The foreign key on the child table would have to have cascade deletes enabled however. If that's the case, Entity Framework just creates a delete statement for the parent, and the database knows to delete the children as well.

Entity Framework creates a foreign key with cascade deletes enabled by default, if the relationship is required, like in your case. (Foreign key can't be null).

If you have created the database yourself, you have to remember to enable it.


Popular Answer

I think it's better to override the OnModelCreating method and add this code.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<JohnsChildTable>()
               .HasRequired(t=>t.JohnsParentTable)
               .WithMany(t=>t.JohnsChildTables)
               .HasForeignKey(d=>d.JohnsParentTableId)
               .WillCascadeOnDelete(true);

            base.OnModelCreating(modelBuilder);
}

I've set to true WillCascadeOnDelete(true)




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why