EF Optional navigation cascade not deleting

Question

I have read this useful post Entity Framework .Remove() vs. .DeleteObject() that says this basically:

If the relationship is optional, i.e. the foreign key that refers from the child to the parent in the database allows NULL values, this foreign will be set to null and if you call SaveChanges this NULL value for the childEntity will be written to the database (i.e. the relationship between the two is removed). This happens with a SQL UPDATE statement. No DELETE statement occurs.

Does this mean that even with the option of CascadeOnDelete that optional relationships will not be cascaded? I want the ImageFile child to be deleted whenever the EditPendingApprovalSong is deleted, at the moment it just sets the foreign Id to null.

public class EditPendingApprovalSong
{
    public int Id { get; set; }
    ...
    public virtual ImageFile Image { get; set; }
}

public class ImageFile
{
    public int Id { get; set; }
    public string Name { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<EditPendingApprovalSong>().HasOptional(x => x.Image).WithOptionalPrincipal().WillCascadeOnDelete(true);

     base.OnModelCreating(modelBuilder);
}

[HttpPost]
[ValidateAntiForgeryToken]
[Authorize(Roles = "Admin")]
public ActionResult RejectEdit(ApproveEditDto approveEditDto)
{
    ...
    var editPendingApprovalSong = _editPendingApprovalSongService.GetEditPendingApprovalSong(approveEditDto.Id);

    _editPendingApprovalSongService.Delete(editPendingApprovalSong);

    return RedirectToAction("Index");
}

//Delete method that service calls
public virtual void Delete(T entity)
{
    _context.Set<T>().Remove(entity);
    Save();
}

With my other relationships e.g:

modelBuilder.Entity<EditPendingApprovalSong>().HasMany(x => x.BuyLinks).WithOptional().WillCascadeOnDelete(true);

these work fine and delete the child objects like they should. My question is, do I need to change one of the navigation's to required so that it cascades properly or am I doing something else wrong?

The SQL statements EF is generating, you can see it's doing the Update statement which isn't what I want:

exec sp_executesql N'UPDATE [dbo].[ImageFiles]
SET [EditPendingApprovalSong_Id] = NULL
WHERE (([Id] = @0) AND ([EditPendingApprovalSong_Id] = @1))
',N'@0 int,@1 int',@0=6,@1=4

exec sp_executesql N'DELETE [dbo].[EditPendingApprovalSongs]
WHERE ((([Id] = @0) AND ([Song_Id] = @1)) AND ([User_Id] = @2))',N'@0 int,@1 int,@2 nvarchar(128)',@0=4,@1=1,@2=N'd40b1bdc-c5e9-4c74-bacd-6e6723e188d5'

But isn't the `ImageFile` deleted by the second statement? You won't see a delete statement for `ImageFile` with cascaded delete. — Gert Arnold, Jun 05 '16 at 20:49
@GertArnold Instead of it being deleted, the foreign key is updated to null and I don't know why. The second statement doesn't delete it, although it works on my other many to many collections. — Martin Dawson, Jun 05 '16 at 21:05

score 0 · Accepted Answer · answered Jun 05 '16 at 22:56

I did testing with other classes and it worked (should have been more thorough before posting). I was adding the the EditPendingApprovalSong through a parent class, but when I was deleting this class I wasn't removing it through that parent. That's why EntityFramework wasn't doing what I wanted.

EF Optional navigation cascade not deleting

1 Answers1