I am having problems with EntityFramework and the following datamodel (see simplified diagram).
The Matter object can be thought of as the "main container". There are Bill and Bill Record. There is a one-to-many association from Bill to Bill Record. In the same way, Bill can refer to many BillRecord (possibly 0), and BillRecord can refer to no more than one account.
1) I want to be able to delete BillRecord, but it should not delete Bill if there is an association (therefore, I did not set OnCascadeDelete For Bill to the BillRecords object). Similarly, if I delete Bill, I do not want to delete BillRecord, which may be associated with.
2) However, when I delete Matter, I want everything to disappear: Matter, Bill and Bill Records.
Using the following code, I manage to have 1) the right and 2) it works, if Bill Record is not connected with 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 are 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 Matter, I could check and remove all the associations of Bill and Bill Records manually, but I think that this will be the wrong use of EF.
I am using EntityFramework 6.0 and SQL CE for .NET 4.0.
Many thanks.