Conflict with reference constraint in TPT code-first Entity Framework

.net c# entity-framework entity-framework-6

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

1
2
8/15/2018 10:53:26 AM

Accepted Answer

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();
1
8/15/2018 10:53:13 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow