none
Error creating DB when attempting to implement cascade delete with Composite design pattern in EF 4.1 Code First RRS feed

  • Question

  • Hi,
       I am trying to implement the Composite design pattern with Entity Framework 4.1 using Code First.
    I have a 3 entity classes (see class diagram below) :
     
     
     
    EntityFolder's contain a collection of zero or more EnityFolderItem's. These EntityFolderItem's may be other EntityFolder's or Person's. All EntityFolderItem's contain a Parent property which references the EntityFolder in which they are contained.
    The code is as follows :
    using System;
    using System.Data.Entity;
    using System.Collections.Generic;
    using System.Linq;
    
    namespace EntityFolders
    {
    
     public abstract class EntityFolderItem
     {
     public virtual int ID  { get; set; }
     public virtual string Name  { get; set; }
     public virtual EntityFolder Parent { get; set; }
     }
    
     public class EntityFolder : EntityFolderItem
     {
     public virtual ICollection<EntityFolderItem> Items { get; set; }
     }
    
     public class Person : EntityFolderItem
     {
     public virtual string Address { get; set; }
     }
    
     public class EntityFolderContext : DbContext
     {
     public DbSet<EntityFolder> EntityFolders { get; set; }
     public DbSet<Person> Persons { get; set; }
    
     public EntityFolderContext() : base("EntityFolders")
     { 
     }
    
     protected override void OnModelCreating( DbModelBuilder modelBuilder )
     {
      // Uncommenting the following line causes an error on DB creation
    //  modelBuilder.Entity<EntityFolder>().HasOptional( p=>p.Items ).WithMany().WillCascadeOnDelete( true );
    
      base.OnModelCreating(modelBuilder);
     }
    
     public EntityFolder RootFolder 
     {
      get
      {
      return (from item in this.EntityFolders where item.Parent == null let g = item as EntityFolder select g).Single();
      }
     }
    
     public class Initializer : DropCreateDatabaseIfModelChanges<EntityFolderContext>
     {
      protected override void Seed( EntityFolderContext context )
      {
      EntityFolder rootFolder = context.EntityFolders.Create();
      rootFolder.Name = "RootFolder";
      context.EntityFolders.Add( rootFolder );
    
      Person person1 = context.Persons.Create();
      person1.Name = "John Smith";
      person1.Address = "Address 1";
      rootFolder.Items.Add( person1 );
    
      EntityFolder subFolder = context.EntityFolders.Create();
      subFolder.Name = "Sub-Folder";
      rootFolder.Items.Add( subFolder );
    
      Person person2 = context.Persons.Create();
      person2.Name = "Bill Brown";
      person2.Address = "Address 2";
      subFolder.Items.Add( person2 );
    
      context.SaveChanges();
      }
     }
     }
    
     class Program
     {
     static void Main(string[] args)
     {
      Database.SetInitializer<EntityFolderContext>( new EntityFolderContext.Initializer() );
    
      using (EntityFolderContext context = new EntityFolderContext())
      {
    
      PrintFolders( context.RootFolder, "" );
    
      // Delete folder called "Sub-Folder" (this should also delete the Person Bill Brown).
      foreach( EntityFolderItem item in context.RootFolder.Items )
      {
       if ( item.Name == "Sub-Folder" )
       {
       item.Parent.Items.Remove( item );
       break;
       }
      }
      context.SaveChanges();
      }
     }
    
     private static void PrintFolders( EntityFolder folder, string indent )
     {
      Console.WriteLine( indent + folder.Name );
      indent += " ";
      foreach ( EntityFolderItem item in folder.Items )
      {
      if ( item is EntityFolder )
      {
       PrintFolders( (EntityFolder)item, indent );
      }
      else
      {
       Console.WriteLine( indent + item.Name + ", " + ((Person)item).Address );
      }
      }
     }
     }
    }
    
    I can successfully create and save four entities (see the "Seed" method of the Initializer class). I can navigate to these entities and print them out - output from the "PrintFolders" method in the Program class is shown below :
    RootFolder
       John Smith, Address 1
       Sub-Folder
          Bill Brown, Address 2
    Prior to attempting to delete the EntityFolder named "Sub-Folder" the entities appear in the EntityFolderItems table as follows :
     
     ID  Name       Address     Discriminator Parent_ID 
     --- ---------- ---------- ------------- ---------
     1   RootFolder NULL         EntityFolder  NULL
     2   John Smith Address 1  Person        1
     3   Sub-Folder NULL         EntityFolder  1
     4   Bill Brown Address 2    Person        3
    After deleting the EntityFolder named "Sub-Folder" the table is as follows :
      ID  Name       Address    Discriminator Parent_ID 
     --- ---------- ---------- ------------- ---------
     1   RootFolder NULL       EntityFolder  NULL
     2   John Smith Address 1  Person        1
     3   Sub-Folder NULL       EntityFolder  NULL
     4   Bill Brown Address 2  Person        3
    Neither the EntityFolder named "Sub-Folder" nor the person contained within this folder has been deleted - only the value in the Parent_ID column has been set to NULL.
    As I understand it, this behaviour is as designed. Entity Framework must be told all that it needs to do a cascading delete for entities in the Item collection.
     So to implement cascade delete I found the following post and added code to the "OnModelCreatingMethod" :
    However, if I uncomment the line in the "OnModelCreatingMethod" :
     modelBuilder.Entity<EntityFolder>().HasOptional( p=>p.Items ).WithMany().WillCascadeOnDelete( true );
    
    which I understand tells Entity Framework that the Items collection can contain zero or more EntityFolderItem's and enables cascading delete.
    I get a InvalidOperationException with message :
    "The database creation succeeded, but the creation of the database objects did not. See InnerException for details."

    Inner exception is SqlClientSqlException with message :
    "Introducing FOREIGN KEY constraint 'EntityFolder_Items' on table 'EntityFolderItems' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.\r\nCould not create constraint. See previous errors."
    I think I understand why this error is occurring since there could be cyclic paths in the EntityFolder table (although my business logic should be able to enforce that there isn't).
    How do I implement cascading delete in conjunction with the composite pattern ?
    Any help would be greatly appreciated.
    Thanks in advance,
    Joel Gordon.
    Wednesday, August 17, 2011 12:19 AM

Answers

  • Hi

    I think you cant implement composite pattern with casading deletes. That meansif you are using SQL Server for persistation. SQL Server is not able to resolve cascading deletes on multiple execution path. This is a limitation of sql server (by design) and not of the entity framework...

    I think you need to handle the cascades manually if you are using composite pattern...

    Here some deeper links:

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

    http://msdn.microsoft.com/en-us/library/aa902684%28SQL.80%29.aspx

    Greets

    Holger


    • Marked as answer by Larcolais Gong Wednesday, August 24, 2011 9:27 AM
    Wednesday, August 17, 2011 9:49 AM
  • Hi Joel,

    As you have seen, you cannot use Declarative Referential Integrity (DRI) to enforce cascade deletes in a self referencing scenario in SQL Server and you need to resort to triggers. But first let's examine your domain model which needs a little bit of change:

     

    public abstract class EntityFolderItem
    {
     public int ID { get; set; }
     public string Name { get; set; }
    
     public int? EntityFolderItemId { get; set; }
     public virtual EntityFolder Parent { get; set; }
    }
    
    public class EntityFolder : EntityFolderItem
    {
     public EntityFolder()
     {
     Items = new List<EntityFolderItem>();
     }
    
     public virtual ICollection<EntityFolderItem> Items { get; set; }
    }
    
    public class Person : EntityFolderItem
    {
     public virtual string Address { get; set; }
    }
    
    public class Context : DbContext
    {
     public DbSet<EntityFolder> EntityFolders { get; set; }
     public DbSet<Person> Persons { get; set; }
    
     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     {
     modelBuilder.Entity<EntityFolderItem>()
       .HasOptional(e => e.Parent).WithMany(ef => ef.Items)
       .HasForeignKey(e => e.EntityFolderItemId);  
     }
    }
    

     


    You need to write a INSTEAD OF DELETE trigger to clean up the child records once a parent record is get deleted. The following trigger, taken from this blog post does the job:

     

    CREATE TRIGGER del_MyTable ON EntityFolderItems INSTEAD OF DELETE
    AS
     CREATE TABLE #Table(
     ID INT
     )
     INSERT INTO #Table (ID)
     SELECT ID
     FROM deleted
    
     DECLARE @c INT
     SET @c = 0
    
     WHILE @c <> (SELECT COUNT(ID) FROM #Table) 
     BEGIN
     SELECT @c = COUNT(ID) FROM #Table
    
     INSERT INTO #Table (ID)
     SELECT EntityFolderItems.ID
     FROM EntityFolderItems
     LEFT OUTER JOIN #Table ON EntityFolderItems.ID = #Table.ID
     WHERE EntityFolderItems.EntityFolderItemId IN (SELECT ID FROM #Table)
     AND #Table.ID IS NULL
     END
    
     DELETE EntityFolderItems
     FROM EntityFolderItems
     INNER JOIN #Table ON EntityFolderItems.ID = #Table.ID
    GO
    

     

    Please check this blog post for explanations on the trigger. 

     

    Hope this helps,

    Morteza

     

     


    • Proposed as answer by Morteza Manavi Tuesday, August 23, 2011 12:46 AM
    • Marked as answer by Larcolais Gong Wednesday, August 24, 2011 9:27 AM
    Saturday, August 20, 2011 4:14 PM

All replies

  • Hi

    I think you cant implement composite pattern with casading deletes. That meansif you are using SQL Server for persistation. SQL Server is not able to resolve cascading deletes on multiple execution path. This is a limitation of sql server (by design) and not of the entity framework...

    I think you need to handle the cascades manually if you are using composite pattern...

    Here some deeper links:

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

    http://msdn.microsoft.com/en-us/library/aa902684%28SQL.80%29.aspx

    Greets

    Holger


    • Marked as answer by Larcolais Gong Wednesday, August 24, 2011 9:27 AM
    Wednesday, August 17, 2011 9:49 AM
  • Hi Joel,

    As you have seen, you cannot use Declarative Referential Integrity (DRI) to enforce cascade deletes in a self referencing scenario in SQL Server and you need to resort to triggers. But first let's examine your domain model which needs a little bit of change:

     

    public abstract class EntityFolderItem
    {
     public int ID { get; set; }
     public string Name { get; set; }
    
     public int? EntityFolderItemId { get; set; }
     public virtual EntityFolder Parent { get; set; }
    }
    
    public class EntityFolder : EntityFolderItem
    {
     public EntityFolder()
     {
     Items = new List<EntityFolderItem>();
     }
    
     public virtual ICollection<EntityFolderItem> Items { get; set; }
    }
    
    public class Person : EntityFolderItem
    {
     public virtual string Address { get; set; }
    }
    
    public class Context : DbContext
    {
     public DbSet<EntityFolder> EntityFolders { get; set; }
     public DbSet<Person> Persons { get; set; }
    
     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     {
     modelBuilder.Entity<EntityFolderItem>()
       .HasOptional(e => e.Parent).WithMany(ef => ef.Items)
       .HasForeignKey(e => e.EntityFolderItemId);  
     }
    }
    

     


    You need to write a INSTEAD OF DELETE trigger to clean up the child records once a parent record is get deleted. The following trigger, taken from this blog post does the job:

     

    CREATE TRIGGER del_MyTable ON EntityFolderItems INSTEAD OF DELETE
    AS
     CREATE TABLE #Table(
     ID INT
     )
     INSERT INTO #Table (ID)
     SELECT ID
     FROM deleted
    
     DECLARE @c INT
     SET @c = 0
    
     WHILE @c <> (SELECT COUNT(ID) FROM #Table) 
     BEGIN
     SELECT @c = COUNT(ID) FROM #Table
    
     INSERT INTO #Table (ID)
     SELECT EntityFolderItems.ID
     FROM EntityFolderItems
     LEFT OUTER JOIN #Table ON EntityFolderItems.ID = #Table.ID
     WHERE EntityFolderItems.EntityFolderItemId IN (SELECT ID FROM #Table)
     AND #Table.ID IS NULL
     END
    
     DELETE EntityFolderItems
     FROM EntityFolderItems
     INNER JOIN #Table ON EntityFolderItems.ID = #Table.ID
    GO
    

     

    Please check this blog post for explanations on the trigger. 

     

    Hope this helps,

    Morteza

     

     


    • Proposed as answer by Morteza Manavi Tuesday, August 23, 2011 12:46 AM
    • Marked as answer by Larcolais Gong Wednesday, August 24, 2011 9:27 AM
    Saturday, August 20, 2011 4:14 PM