none
Code First Table-Per-Type (TPT) Cascade Delete RRS feed

  • Question

  • How can I configure my code first model to enable cascading deletes in a table per type scenario?

    Currently, EF creates the correct foreign key relationship between the derived type table and the base type table but it does not enable cascading deletes automatically (i.e. in SQL: Delete Rule = No Action).

    Thank you.

    Tuesday, September 27, 2011 7:32 PM

Answers

  • I just realized that enabling cascading deletes won't help me in this scenario anyway... the records that are being deleted are child records.  Cascade only works from the top down, not the other way around.

    So, I guess my only choice is to load the related entities into memory before deleting so that EF can correctly execute the necessary SQL commands.

    Unless somebody has a better idea???

    • Proposed as answer by Alan_chenModerator Friday, September 30, 2011 7:23 AM
    • Marked as answer by dwkrp Friday, September 30, 2011 12:36 PM
    Thursday, September 29, 2011 9:00 PM

All replies

  • Hi

    This article provides information on this

    http://msdn.microsoft.com/en-us/library/bb738695.aspx


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Wednesday, September 28, 2011 3:19 AM
  • Hi dwkrp,

    Welcome!

    You are right, TPT doesn't set the Cascade Delete Rule in database, but based on my test, it will run two T-SQLs(insert or delete) synchronized. Let me show you:

    Here is my test code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace TPTTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var context= new InheritanceMappingContext())
                {
                    //var detail = new BankAccount()
                    //{
                    //    BillingDetailId = 1,
                    //    Number = "88",
                    //    Owner = "Alan",
                    //    BankName = "child",
                    //    Swift = "child1"
                    //};
                    //context.Details.Add(detail);
                    //context.SaveChanges();
    
    
                    var b = context.Details.OfType<BankAccount>().FirstOrDefault();
                    context.Details.Remove(b);
                    context.SaveChanges();
                }
            }
        }
        public abstract class BillingDetail
        {
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            [Key]
            public int BillingDetailId { get; set; }
            public string Owner { get; set; }
            public string Number { get; set; }
        }
    
        public class BankAccount : BillingDetail
        {
            public string BankName { get; set; }
            public string Swift { get; set; }
        }
        public class InheritanceMappingContext : DbContext
        {
            public DbSet<BillingDetail> Details { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
               
                modelBuilder.Entity<BillingDetail>().ToTable("BillingDetail");
                modelBuilder.Entity<BankAccount>().ToTable("BankAccount");
            }
        }
    }
    
    

    When I add a new record to database the T-SQLs are as follows:

    exec sp_executesql N'insert [dbo].[BillingDetail]([BillingDetailId], [Owner], [Number])
    values (@0, @1, @2)
    ',N'@0 int,@1 nvarchar(max) ,@2 nvarchar(max) ',@0=1,@1=N'Alan',@2=N'88'
    ------------------------------------
    exec sp_executesql N'insert [dbo].[BankAccount]([BillingDetailId], [BankName], [Swift])
    values (@0, @1, @2)
    ',N'@0 int,@1 nvarchar(max) ,@2 nvarchar(max) ',@0=1,@1=N'child',@2=N'child1'
    

    When I remove one record from EF, the T-SQLs are as follows:

    exec sp_executesql N'delete [dbo].[BankAccount]
    where ([BillingDetailId] = @0)',N'@0 int',@0=1
    --------------------------------------
    exec sp_executesql N'delete [dbo].[BillingDetail]
    where ([BillingDetailId] = @0)',N'@0 int',@0=1
    

    I think it is same with the cascade delete rule in database, I'm not sure what really you want, please feel free to let me your concerns.

    Have a nice day.


     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 7:33 AM
    Moderator
  • Hi Alan,

    Thanks for the response... and I agree that the EF works correctly in your scenario.  What I am experiencing is a little bit different.

    If I modify your code slightly to include another entity type (Person) that has a required relationship to the derived BankAccount type, you'll see my concern.

    When I delete a Person from the EF, the person record is deleted from SQL which cascades to delete the related BankAccount record too.  However, the BillingDetail record is now orphaned because the BillingDetail-BankAccount constraint is not set to cascade deletes.

    Any thoughts?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace TPTTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var context = new InheritanceMappingContext())
                {
                    //var person = new Person()
                    //{
                    //    PersonId = 1,
                    //    Name = "DWKRP"
                    //};
                    //context.People.Add(person);
    
                    //var detail = new BankAccount()
                    //{
                    //    BillingDetailId = 1,
                    //    Number = "88",
                    //    Owner = "Alan",
                    //    BankName = "child",
                    //    Swift = "child1",
                    //    Person = person
                    //};
                    //context.Details.Add(detail);
                    //context.SaveChanges();
    
    
                    //var b = context.Details.OfType<BankAccount>().FirstOrDefault();
                    //context.Details.Remove(b);
                    //context.SaveChanges();
    
                    // delete person and BillingDetail record is orphaned
                    var p = context.People.FirstOrDefault();
                    context.People.Remove(p);
                    context.SaveChanges();
                }
            }
        }
        
        public abstract class BillingDetail
        {
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            [Key]
            public int BillingDetailId { get; set; }
            public string Owner { get; set; }
            public string Number { get; set; }
        }
    
        public class BankAccount : BillingDetail
        {
            public string BankName { get; set; }
            public string Swift { get; set; }
            [Required]
            public virtual Person Person { get; set; }
        }
    
        public class Person
        {
            [Key]
            public int PersonId { get; set; }
            public string Name { get; set; }
            public virtual ICollection<BankAccount> BankAccounts { get; set; }
        }
        
        public class InheritanceMappingContext : DbContext
        {
            public DbSet<BillingDetail> Details { get; set; }
            public DbSet<Person> People { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<BillingDetail>().ToTable("BillingDetail");
                modelBuilder.Entity<BankAccount>().ToTable("BankAccount");
            }
        }
    }
    

    Wednesday, September 28, 2011 3:02 PM
  • Hi,

    I guess, EntitFramework applys Cascade Delete only to loaded entities in the context and this is what happening in your case...

    Suggested solution is apply Cascade delete in database level.

     


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Thursday, September 29, 2011 12:48 AM
  • it seems no way to set the cascade from model configuratios in TPT scenario, because there is not property exporting out as foreign key in models. The suggested way is set cascade rule in database level


    I am fish.
    • Edited by Fly_fish Thursday, September 29, 2011 8:59 AM
    Thursday, September 29, 2011 8:59 AM
  • Yes, the 'cascade' does appear to work if I load the related entity into the context before marking the parent for deletion and saving.  For example, in the previous code I could get it to work by just changing it as follows...

    var p = context.People.Include(person => person.BankAccounts).FirstOrDefault();
    context.People.Remove(p);
    context.SaveChanges();
    

    Obviously, in a real-world application with many entity relationships and multiple levels of inheritance, it doesn't make a lot of sense to have to load everything so that it gets deleted properly... especially when the EF is abstracted away behind a data service.

    In my opinion, I think it would make sense for EF code first to create these constraints with cascading deletes enabled by default... with a way to disable them using the fluent API.

    For now, I guess I will just enable it manually in the database... or 'automate' it in my IDatabaseInitializer class using context.Database.ExecuteSqlCommand.

    Thursday, September 29, 2011 1:27 PM
  • I just realized that enabling cascading deletes won't help me in this scenario anyway... the records that are being deleted are child records.  Cascade only works from the top down, not the other way around.

    So, I guess my only choice is to load the related entities into memory before deleting so that EF can correctly execute the necessary SQL commands.

    Unless somebody has a better idea???

    • Proposed as answer by Alan_chenModerator Friday, September 30, 2011 7:23 AM
    • Marked as answer by dwkrp Friday, September 30, 2011 12:36 PM
    Thursday, September 29, 2011 9:00 PM
  • Hi dwkrp,

    Thanks for your feedback.

    I think you are right!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 30, 2011 7:23 AM
    Moderator