Conflict with reference constraint in TPT code-first Entity Framework

Question

I am using TPT code-first in Entity Framework 6 and have the following setup:

public abstract class Product
{
    [Key]
    public string ProductID { get; set; }
    // a bunch of trivial properties like dates and floats
}

[Table("SpecialProducts")]
public class SpecialProduct : Product
{
    // more trivial properties
    public List<Property> MyProperties { get; set; }
}

public class Property
{
    [Key]
    public int ID { get; set; }
    [Required]
    public SpecialProduct Product { get; set; }
    // property data
}

public class MyDbContext : DbContext
{
    public DbSet<Product> AllProducts { get; set; }

    public MyDbContext()
        : base("MyDataBase")
    {}

    public RemoveSomeProducts()
    {
        var products = from product in AllProducts where /* some condition */ select product;
        AllProducts.RemoveRange(products);
        SaveChanges();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // I know I don't need both statements, and my guess is I need the first, but at this point I don't know anything anymore
        modelBuilder.Entity<Property>()
            .HasRequired(property => property.Product)
            .WithMany(product => product.MyProperties)
            .WillCascadeOnDelete(true);

        modelBuilder.Entity<SpecialProduct>()
            .HasMany(product => product.MyProperties)
            .WithRequired(property => property.Product)
            .WillCascadeOnDelete(true);
    }

}

When calling RemoveSomeProducts() I get the following Exception:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Properties_dbo.SpecialProducts_Product_ProductID". The conflict occurred in database "MyDataBase", table "dbo.Properties", column 'Product_ProductID'.

To me this sounds like the Properties belonging to the deleted SpecialProducts are not being deleted. I have little experience with databases, but from my understanding this should be fixed using cascade delete, but I seem to fail to configure this.

So my question is obviously: how can I fix this?

Potential duplicates that did not seem to help in my case, but might be useful for someone else:

EF6 Cascade Delete

Code First Cascade Delete

TPT Cascade Delete

Does products var in RemoveSomeProducts includes the list of Property? Or is MyProperties null? — joaoruimartins, Aug 15 '18 at 10:08
@joaoruimartins You might be on to something, it is indeed `null`. I tried using `AllProducts.Include(...)` like in the third link I mentioned, but this does not work as not all `Products` have `Properties`. It throws an exception. — Jerome Reinländer, Aug 15 '18 at 10:17
That might be the problem. Or using Product all around when actually is SpecialProduct that has the relationship to properties. Any reason for splitting those classes? In my experience you can end up in all sorts of problems. Only use inheritance in EF entities when really needed. — joaoruimartins, Aug 15 '18 at 10:22
Well, let's put it this way: it does make sense to have the inheritance. I could do TPH, but having one big unnormalized table, that would have to be extended in the future, does not sound like the best solution to me. I would rather keep TPT if possible — Jerome Reinländer, Aug 15 '18 at 10:39
Try to make MyProperties a virtual property and check if you still get the same error when adding the .Include(). — joaoruimartins, Aug 15 '18 at 10:49

score 1 · Accepted Answer · answered Aug 15 '18 at 10:53

1

First of all you must include your navigation property in query explicitly. Than for some reason RemoveRange doesn't work as expected with cascade delete, but if you iterate and remove one by one it works.

var products = Set<SpecialProduct>().Include(p => p.MyProperties).ToList();
products.ForEach(p => AllProducts.Remove(p));
SaveChanges();

answered Aug 15 '18 at 10:53

Jan Muncinsky

4,282
4
22
40

The first line of your solution was exactly, what I needed. However I cannot confirm that `RemoveRange` does not work. For me it works just fine. – Jerome Reinländer Aug 15 '18 at 11:30

Conflict with reference constraint in TPT code-first Entity Framework

1 Answers1