Code first TPT and cascade on delete

Question

I'm using EF4.1 with Code first and TPT (Table per Type) inheritance. I have a structure like this

public class Customer 
{
    public virtual ICollection<Product> Products {get; set;}
}

public class Product
{
   [Required]
   public int Id { get; set; }

   [Required]
   public virtual Customer {get; set;}

   public decimal Price { get; set; }
}

public class SpecializedProduct : Product
{
   public string SpecialAttribute { get; set; }
}

when i delete a customer i want all the products associated with that customer to be deleted. I can specify a WillCascadeOnDelete(true) between the Customer and the Product:

modelBuilder.Entity<Customer>().HasMany(e => e.Products).WithRequired(p => p.Customer).WillCascadeOnDelete(true);

but since there's a foreighn key relationship between SpecializedProduct and Product i get an Exception when I try to delete the Customer:

The DELETE statement conflicted with the REFERENCE constraint "SpecializedProduct _TypeConstraint_From_Product_To_SpecializedProduct". The conflict occurred in database "Test", table "dbo.SpecializedProduct", column 'Id'. The statement has been terminated.

If i manually set a on delete cascade on the SpecializedProduct _TypeConstraint_From_Product_To_SpecializedProduct constraint it works, but i would like to be able to specify this using the modelbuilder or some other way in code. Is this possible?

Thanks in advance!

Best Regards

Simon

Answer 1

When it comes to database, a TPT inheritance is implemented with a Shared Primary Key Association between the base class (e.g. Product) and all the derived classes (e.g. SpecializedProduct). Now, when you delete a Customer object without fetching its Products property, EF has no idea that this Customer has a bunch of products that also needs to be deleted as per your requirement. If you enable cascade deletes by marking your customer-product association as required, then database will take care of deleting the child record(s) from the product table but if this child record is a SpecializedProduct then the related row on the SpecializedProduct won't get deleted and hence the exception that you are getting. So basically the following code won't work:

// This works only if customer's products are not SpecializedProduct
Customer customer = context.Customers.Single(c => c.CustomerId == 1);
context.Customers.Remove(customer);
context.SaveChanges();    

This code will cause EF to submit the following SQL to the database:

exec sp_executesql N'delete [dbo].[Customer] where ([CustomerId] = @0)',N'@0 int',@0=1

That said, There is no way to enable the cascade deletes between Product and SpecializedProduct tables, that's just how EF Code First implements a TPT inheritance and you cannot override it.

So what's the solution?

One way is what you already figured out, manually switching the cascades on between Product and SpecializedProduct tables to avoid the exception when you deleting a customer with SpecializedProducts.

The second way is to let EF take care of the customer's SpecializedProducts when you removing the customer. Like I said before, this happens because the Customer object has not been properly fetched, and EF has no knowledge of customer's SpecializedProducts which means by fetching the customer object properly, Ef will start tracking the customer's associations and will submit necessary SQL statements to make sure that every related record is removed before removing the customer:

Customer customer = context.Customers
                           .Include(c => c.Products)
                           .Single(c => c.CustomerId == 1);

context.Customers.Remove(customer);
context.SaveChanges();    

As a result, EF will submit the following SQL statements to the database which perfectly removes everything in order:

exec sp_executesql N'delete [dbo].[SpecializedProduct] where ([Id] = @0)',N'@0 int',@0=1

exec sp_executesql N'delete [dbo].[Product] where (([Id] = @0) and ([Customer_CustomerId] = @1))',N'@0 int,@1 int',@0=1,@1=1

exec sp_executesql N'delete [dbo].[Customer] where ([CustomerId] = @0)',N'@0 int',@0=1