EntityFramework: CascadeOnDelete only if parent object is deleted

Question

I got into some trouble with EntityFramework and the following datamodel (see simplified diagram).

The Matter object can be thinked as the "main container". There are Bill and BillRecord. There is a one-to-many association from Bill to BillRecord. Precisely, a Bill can reference many BillRecord (possibly 0) and a BillRecord can be referenced to at most one bill.

1) I want to be able to delete a BillRecord but it should not delete the Bill, if there is an association (that is why I did not set a OnCascadeDelete For Bill on BillRecords entity). Similarly, if I delete a Bill I do not want to delete the BillRecord that may be associated with.

2) However, when I delete a Matter I want everything to disappear: the Matter, Bill and BillRecords.

With the following code, I manage to have 1) right and 2) works if there is no BillRecord associated to a Bill, if there is one I get the following error.

System.Data.SqlServerCe.SqlCeException: The primary key value cannot be deleted because references to this key still exist. [ Foreign key constraint name = FK_dbo.BillRecordDboes_dbo.BillDboes_BillId ]

Here is my entities and my logic for OnModelCreating

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<MatterDbo>().HasMany<BillRecordDbo>(s => s.BillRecordDbos)
   .WithRequired(s => s.Matter).HasForeignKey(s => s.MatterId).WillCascadeOnDelete(true);

    modelBuilder.Entity<MatterDbo>().HasMany<BillDbo>(s => s.BillDbos)
    .WithRequired(s => s.Matter).HasForeignKey(s => s.MatterId).WillCascadeOnDelete(true);
}

public class MatterDbo
{
    public MatterDbo()
    {
        BillDbos = new List<BillDbo>();
        BillRecordDbos = new List<BillRecordDbo>();
    }

    public Guid Id { get; set; }

    public virtual List<BillDbo> BillDbos { get; set; }

    public virtual List<BillRecordDbo> BillRecordDbos { get; set; }
}

public class BillRecordDbo
{
    public Guid Id { get; set; }

    public Guid MatterId { get; set; }

    public virtual MatterDbo Matter { get; set; }

    public Guid? BillId { get; set; }

    public virtual BillDbo Bill { get; set; }
}

public class BillDbo
{
    public BillDbo()
    {
        BilledRecords = new List<BillRecordDbo>();
    }

    public Guid Id { get; set; }

    public virtual List<BillRecordDbo> BilledRecords { get; set; }

    public Guid MatterId { get; set; }

    public virtual MatterDbo Matter { get; set; }
}

Of course, when deleting a Matter I could check and remove all the associations of Bill and BillRecords manually but I think it would be a wrong usage of EF.

I am using EntityFramework 6.0 and SQL CE targetting .NET 4.0

Thank you very much.

Answer 1

If you really need BillRecord to remain in the database if its parent, Bill entity, is deleted, then you have to set the parent property to null before deleting the parent. However, having a nullable FK in the database is often a sign there might be a better db design solution.