EntityFramewok: How to configure Cascade-Delete to

Question

EntityFramework's documentation states that the following behavior is possible:

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.

(from http://msdn.microsoft.com/en-us/jj591620)

However, I cannot achieve such a behavior.

I have the following Entities defined with code-first:

public class TestMaster
{
    public int Id { get; set; }
    public string Name { get; set; }        
    public virtual ICollection<TestChild> Children { get; set; }       
}

public class TestChild
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual TestMaster Master { get; set; }
    public int? MasterId { get; set; }
}

Here is the Fluent API mapping configuration:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestMaster>()
                    .HasMany(e => e.Children)
                    .WithOptional(p => p.Master).WillCascadeOnDelete(false);

        modelBuilder.Entity<TestChild>()
                    .HasOptional(e => e.Master)
                    .WithMany(e => e.Children)
                    .HasForeignKey(e => e.MasterId).WillCascadeOnDelete(false);
    }

Foreign Key is nullable, navigation property is mapped as Optional, so I expect the cascade delete to work as described as MSDN - i.e. to nullify MasterID's of all children and then delete the Master object.

But when I actually try to delete, I get the FK violation error:

 using (var dbContext = new TestContext())
        {
            var master = dbContext.Set<TestMaster>().Find(1);
            dbContext.Set<TestMaster>().Remove(master);
            dbContext.SaveChanges();
        }

On SaveChanges() it throws the following:

System.Data.Entity.Infrastructure.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
----> System.Data.UpdateException : An error occurred while updating the entries. See the inner exception for details.
----> System.Data.SqlClient.SqlException : The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.TestChilds_dbo.TestMasters_MasterId". The conflict occurred in database "SCM_Test", table "dbo.TestChilds", column 'MasterId'.
The statement has been terminated.

Am I doing something wrong or did I misunderstood what the MSDN says?

Answer 1

After following @Slauma's great answer I was still getting same error as OP.

So don't be as naive as me and think that the examples below will end up with same result.

dbCtx.Entry(principal).State = EntityState.Deleted;
dbCtx.Dependant.Where(d => d.PrincipalId == principalId).Load();

// code above will give error and code below will work on dbCtx.SaveChanges()

dbCtx.Dependant.Where(d => d.PrincipalId == principalId).Load();
dbCtx.Entry(principal).State = EntityState.Deleted;

First load the children into context before setting entity state to deleted (if you are doing it that way).

Answer 2

It works indeed as described but the article on MSDN misses to emphasize that it only works if the children are loaded into the context as well, not only the parent entity. So, instead of using Find (which only loads the parent) you must use eager loading with Include (or any other way to load the children into the context):

using (var dbContext = new TestContext())
{
    var master = dbContext.Set<TestMaster>().Include(m => m.Children)
        .SingleOrDefault(m => m.Id == 1);
    dbContext.Set<TestMaster>().Remove(master);
    dbContext.SaveChanges();
}

This will delete the master from the database, set all foreign keys in the Child entities to null and write UPDATE statements for the children to the database.