Entity Framework Many to Many Cascade Delete Issue

Question

Having a strange issue working on a code first EF project.

I have the following entities:

public class Booking
{
    public Guid BookingId { get; set; }
    public virtual List<AccountingDocumentItem> AccountingDocumentItems { get; set; }
}

public class AccountingDocumentItem
{
    public Guid AccountingDocumentItemId { get; set; }

    public virtual List<Employee> Employees { get; set; }
    public virtual List<Booking> Bookings { get; set; }
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }

    public virtual List<AccountingDocumentItem> AccountingDocumentItems { get; set; }
}

As you can see, there is meant to be many-to-many relationship between AccountingDocumentItem and both Bookings and Employees. When configuring my AccountingDocumentItem I use the following:

    public AccountingDocumentItemConfiguration()
    {
        HasMany(x => x.Employees);
        HasMany(x => x.Bookings);
    }

What is strange is that this works perfectly for Employees. I get a AccountingDocumentItemEmployees table created. But for Bookings I get the following error:

"Introducing FOREIGN KEY constraint 'FK_dbo.AccountingDocumentItemBookings_dbo.Bookings_Booking_BookingId' on table 'AccountingDocumentItemBookings' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

Now I've tried to do this along the lines of below code:

 HasMany(x => x.Bookings).WithMany(b => b.AccountingDocumentItems)...

But I only get the option to do a Map using the above line, no option to do a WillCascadeOnDelete(false).

Can someone point out what I'm doing wrong, because comparing it to how I handle Employees I can't see any difference.

EDIT:

My original post abbreviated the entities, which is probably where the problem is arising. Here is the full entity:

public class AccountingDocument
{
    public Guid AccountingDocumentId { get; set; }
    public Guid SiteId { get; set; }
    public virtual Site Site { get; set; }
    public Guid? ClientId { get; set; }
    public virtual Client Client { get; set; }
    public Guid? SupplierId { get; set; }
    public virtual Supplier Supplier { get; set; }
    public string DocumentNumber { get; set; }
    public string Reference { get; set; }
    public string Description { get; set; }
    public string Notes { get; set; }
    public Guid LinkedAccountingDocumentId { get; set; }
    public virtual AccountingDocument LinkedAccountingDocument { get; set; }
    public byte AccountingDocumentTypeId { get; set; }
    public DateTime CreationDate { get; set; }
    public DateTime DocumentDate { get; set; }
    public decimal Total { get; set; }
    public Guid UserId { get; set; }
    public virtual User User { get; set; }
    public string Room { get; set; }
    public virtual List<AccountingDocumentItem> AccountingDocumentItems { get; set; }
}

public class AccountingDocumentItem
{
    public Guid AccountingDocumentItemId { get; set; }

    public Guid AccountingDocumentId { get; set; }
    public virtual AccountingDocument AccountingDocument { get; set; }

    public string Description { get; set; }

    public Guid TaxId { get; set; }
    public virtual Tax Tax { get; set; }

    public decimal Quantity { get; set; }
    public string Unit { get; set; }

    public decimal Cost { get; set; }
    public decimal SellInclusive { get; set; }
    public decimal SellExclusive { get; set; }
    public decimal DiscountPercentage { get; set; }
    public decimal TotalInclusive { get; set; }
    public decimal TotalExclusive { get; set; }
    public decimal CommissionInclusive { get; set; }
    public decimal CommissionExclusive { get; set; }
    public int LoyaltyPoints { get; set; }
    public bool IsSeries { get; set; }

    public byte ItemType { get; set; }

    public Guid? ServiceId { get; set; }
    public virtual Service Service { get; set; }
    public Guid? ProductId { get; set; }
    public virtual Product Product { get; set; }
    public Guid? VoucherId { get; set; }
    public virtual Voucher Voucher { get; set; }

    public int SortOrder { get; set; }

    public Guid? SourceId { get; set; }
    public virtual Source Source { get; set; }

    public Guid? CostCentreId { get; set; }
    public virtual CostCentre CostCentre { get; set; }

    public Guid? ClientId { get; set; }
    public virtual Client Client { get; set; }

    public Guid PackageGroupId { get; set; }
    public Guid PackageServiceId { get; set; }

    public virtual List<Employee> Employees { get; set; }
    public virtual List<Booking> Bookings { get; set; }
    public virtual List<MedicalDiagnosis> MedicalDiagnoses { get; set; }
}

public class Booking
{
    public Guid BookingId { get; set; }

    public Guid SiteId { get; set; }
    public Site Site { get; set; }

    public Guid? ClientId { get; set; }
    public Client Client { get; set; }

    public Guid BookingStateId { get; set; }
    public BookingState BookingState { get; set; }

    public virtual List<AccountingDocumentItem> AccountingDocumentItems { get; set; }
}

And my configuration:

public class AccountingDocumentConfiguration : EntityTypeConfiguration<AccountingDocument>
{
    public AccountingDocumentConfiguration()
    {
        Property(x => x.Reference).HasMaxLength(200);
        HasRequired(x => x.Site);
        Property(x => x.DocumentNumber).IsRequired().HasMaxLength(100);
        Property(x => x.Reference).HasMaxLength(200);
        Property(x => x.Description).HasMaxLength(500);
        Property(x => x.Notes).HasMaxLength(500);
        HasOptional(x => x.LinkedAccountingDocument);
        Property(x => x.AccountingDocumentTypeId).IsRequired();
        Property(x => x.CreationDate).IsRequired();
        Property(x => x.DocumentDate).IsRequired();
        Property(x => x.Total).IsRequired();
        Property(x => x.Room).HasMaxLength(50);
    }
}

public class AccountingDocumentItemConfiguration : EntityTypeConfiguration<AccountingDocumentItem>
{
    public AccountingDocumentItemConfiguration()
    {
        Property(x => x.Description).IsRequired().HasMaxLength(200);
        HasMany(x => x.Employees);
        HasMany(x => x.Bookings);
        HasMany(x => x.MedicalDiagnoses);
        Property(x => x.Unit).HasMaxLength(50);
    }
}

Answer 1

Even with the added text above it's working for me, once I comment out the added nav properties that aren't fully defined above. The FK error means that there might be a race condition if you happen to delete (See this article), but with whats here I can't tell. Do you need to have cascading deletes on your database? If not, you could just turn it off - I realize it's a pretty broad stroke on a minor problem though.

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();  

If this is not an option - it's something else that you haven't included. Do you have mappings for the Bookings table? It looks like Bookings have a required Site - is it possible that deleting a site, could trigger a delete in a bunch of other things? It looks like Site could do something like this Site -> Account Document -> Accounting Document item.. Site -> Booking possibly?

Here's another SO question that could possibly be related.