Two EF Entities referencing a common Entity type. (Multiple classes sharing a common Address entity type)

Question

I am trying to figure out how to configure 2 EF entities to reference a common Address entity.
All of the keys in the database are defined to Cascade on delete

I can create instances of the Property object with a reference to a single Address.
For the Business object, I reference 2 Addresses.

The problem is when I delete the Property object and the Business object, the Address objects referenced are not deleted.

Does EF support Cascade delete for this type of entity mapping?

If so, what does the mapping configuration need to look like?

I have a complete simple test application that shows the problem. I can upload it if needed.

ALTER TABLE [dbo].[Properties]
    ADD CONSTRAINT [FK_dbo.Properties_dbo.Addresses_PropertyID] FOREIGN KEY ([PropertyID]) REFERENCES [dbo].[Addresses] ([AddressID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[BusinessAddresses]
    ADD CONSTRAINT [FK_dbo.BusinessAddresses_dbo.Addresses_BusinessAddressID] FOREIGN KEY ([BusinessAddressID]) REFERENCES [dbo].[Addresses] ([AddressID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[BusinessAddresses]
    ADD CONSTRAINT [FK_dbo.BusinessAddresses_dbo.Businesses_Business_BusinessID] FOREIGN KEY ([Business_BusinessID]) REFERENCES [dbo].[Businesses] ([BusinessID]) ON DELETE CASCADE;

Here are the entities I defined.

    public class Address
    {
        public int AddressID { get; set; }
        public string AddressLine { get; set; }
    }

    public class Property
    {
        public int PropertyID { get; set; }
        public string PropertyNumber { get; set; }

        //[ForeignKey("AddressID")]
        //public int AddressID { get; set; }
        public virtual Address Address { get; set; }
    }

    public class BusinessAddress
    {
        public int BusinessAddressID { get; set; }
        public string AddressType { get; set; }

        //[ForeignKey("Business")]
        //public int BusinessID { get; set; }
        public virtual Business Business { get; set; }

        //[ForeignKey("AddressID")]
        //public int AddressID { get; set; }
        public virtual Address Address { get; set; }
    }

    public class Business
    {
        public int BusinessID { get; set; }
        public string Name { get; set; }
        public virtual ICollection<BusinessAddress> Addresses { get; set; }
    }

Here are the EF mappings I have defined.

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Property>()
                .HasRequired(p => p.Address)
                .WithRequiredDependent()
                .WillCascadeOnDelete();

            modelBuilder.Entity<Business>()
                .HasMany(ba => ba.Addresses)
                .WithRequired(b => b.Business)
                .WillCascadeOnDelete();

            modelBuilder.Entity<BusinessAddress>()
                .HasRequired(ba => ba.Address)
                .WithRequiredDependent()
                .WillCascadeOnDelete();
        }

Answer 1

Does EF support Cascade delete for this type of entity mapping?

Why don't you configure the DB engine to do the cascade delete? All you do in EF is delete the table record that starts cascade delete and let the DB engine do the rest of it.

Answer 2

That is the problem I am trying to figure out. All of the Foreign Keys seemed to be marked as ON DELETE CASCADE but when I delete the top level EF Entity object, the referenced Address objects do not get deleted.

If I delete a Business object, the referenced BusinessAddress objects gets deleted but not the Address objects referenced by the BusinessAddress objects.

If I delete a Property object, the referenced Address object is not deleted.

If in my Delete logic, I call 'context.Addresses.Remove(addrs)' before deleting the Business or Property objects, the referenced address will be deleted.

How do I configure the EF entity mappings so that the Database is configured to do the cascade delete and delete the Address records.

Answer 3

If you are saying that the DB engine, MS SQL Server, is configured to do the cascading deletes, then EF really has nothing to do with it other than you issuing the RemoveAT() on the parent record.

You should use SSMS and delete the parent record using T-SQL to verify that the cascading deletes works from the DB engine perspective. If it works, then you may want to use a raw T-SQL delete statement and delete the parent record and let the DB engine do the cascading deletes.

https://msdn.microsoft.com/en-us/data/jj592907.aspx?f=255&MSPPError=-2147217396

Answer 4

I just used SSMS to delete the parent Property record and the referenced Address record was not deleted. So it looks like the DB is not configured properly to do the cascade delete.

What I am asking is how to configure EF entities and entity mappings so that the DB created by EF will have the correct configuration so that deleting the parent record tells the DB engine to cascade delete referenced objects.

I included the Entity classes and Entity mappings I am currently using with my original question. But this configuration does not seem to setup the DB correctly to do the referenced objects cascade delete.

Does the Property Entity need the AddressID ForeignKey() property?

Is the Fluent Api Entity Mapping correct for the referenced property cascade delete?

Answer 5

I just used SSMS to delete the parent Property record and the referenced Address record was not deleted. So it looks like the DB is not configured properly to do the cascade delete.

You can set it up using SSMS for the parent table and identify the foreign key constraint tables, which is fairly simple.

Well, I got burnt using EF to do the cascading deletes as it took too long do it, and the Web application using the DAL that was using EF blew-up in not getting back to the client in under a minute, the default timeout setting .  

So, I just issued the delete of the parent record, let the DB engine do it and the processing returned to the client side immediately.

I suggest that you do the same here and configure SQL Server through SSMS to do it.