5

I have a Base class, a Derived class and Derived has a collection of Items. I want to configure EF to delete Items when their parent Derived is deleted.

The following minimal (LinqPad) example shows how I'm trying to achieve that, but it doesn't generate the on delete cascade part, just regular FKs.

I tried [Required] attribute - didn't work.

How do I make it add delete cascade option to the FK specification?

[System.ComponentModel.DataAnnotations.Schema.Table("Bases")]
public class Base
{
    public int Id {get;set;}
    public string Name {get; set;}
}
[System.ComponentModel.DataAnnotations.Schema.Table("Derived")]
public class Derived : Base
{
    public virtual ICollection<Item> Items {get;set;}

    public Derived()
    {
        Items = new HashSet<Item>();
    }
}

public class Item 
{
    public int Id {get;set;}

    public int ParentId {get;set;}
    public Derived Parent {get;set;}
}

public class TestDbContext : System.Data.Entity.DbContext
{
    public System.Data.Entity.DbSet<Base> Bases { get; set; }
    public System.Data.Entity.DbSet<Derived> Derived { get; set; }
    public System.Data.Entity.DbSet<Item> Items { get; set; }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        System.Data.Entity.Database.SetInitializer<TestDbContext>(null);                    
        modelBuilder.Entity<Item>().HasRequired(x=>x.Parent).WithMany(x=>x.Items).HasForeignKey(x=>x.ParentId).WillCascadeOnDelete(true);
    }
}

void Main()
{
    var ctx = new TestDbContext();
    var ddl = (ctx as   System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext.CreateDatabaseScript();
    Console.WriteLine(ddl);
}

This is the DDL it generates:

create table [dbo].[Bases] (
    [Id] [int] not null identity,
    [Name] [nvarchar](max) null,
    primary key ([Id])
);
create table [dbo].[Derived] (
    [Id] [int] not null,
    primary key ([Id])
);
create table [dbo].[Items] (
    [Id] [int] not null identity,
    [ParentId] [int] not null,
    primary key ([Id])
);
alter table [dbo].[Derived] add constraint [Derived_TypeConstraint_From_Base_To_Derived] foreign key ([Id]) references [dbo].[Bases]([Id]);
alter table [dbo].[Items] add constraint [Item_Parent] foreign key ([ParentId]) references [dbo].[Derived]([Id]);
Zar Shardan
  • 5,675
  • 2
  • 39
  • 37
  • 1
    **First**, you have miss configured constraint, there are two FK(s) on Items that is supposed only to be one, because you missed to specify the property `x => x.Parent` on `WithRequired`. **Second**, with TPT you can't, but it's possible with TPH (which generates only 1 table with discriminator column), to do so, just remove the `[System.ComponentModel.DataAnnotations.Schema.Table("Derived")]`. Check out this [link](http://stackoverflow.com/questions/8599617/problems-using-tpt-table-per-type-in-ef-4-2-and-deletion-of-parent-objects) – tsuta Jun 30 '15 at 20:43
  • Ok, fair enough, fixed the config, but the issue still persists. Unfortunately TPH is not what I need here. – Zar Shardan Jun 30 '15 at 20:50
  • Interestingly it doesn't add a cascade delete even for the inheritance related FK - although when you delete the base class record - you'd expect the derived one (and all its dependent records) deleted as well? The DB permits this just fine... – Zar Shardan Jun 30 '15 at 21:00
  • Yes, I think there is still a flaw in the design related to the cascade delete for TPT and TPC, and as in the link I mentioned, the cascade delete is also not generated for base-derived relationship, so you need alter the FK manually, either in the DB or using migration. – tsuta Jun 30 '15 at 21:04
  • If I make Base class abstract (TPC) and remove public DbSet Bases { get; set; } - it does add on delete cascade. I guess my question only pertains to TPT then. – Zar Shardan Jun 30 '15 at 21:14
  • have a look at http://stackoverflow.com/questions/26870441/entity-framework-cascading-delete?rq=1 – Kirsten Jul 01 '15 at 06:15
  • @kirsten-g that question is not dealing with inheritance, and in this case it's the inheritance that's causing this problem. Without it everything works as expected. – Zar Shardan Jul 01 '15 at 09:45
  • Ok... I think that Parent should be declared as virtual in Item. – Kirsten Jul 01 '15 at 12:06
  • virtual does not help. As far as I remember that is mainly needed for lazy loading. – Zar Shardan Jul 02 '15 at 13:26

0 Answers0