none
My Entity Framework 4.3 cascading delete does not work RRS feed

  • Question


  • I have a simple model like this:

                modelBuilder.Entity<ParentTable>().Property(c => c.ParentID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                modelBuilder.Entity<ParentTable>().HasKey(c => c.ParentID);
                modelBuilder.Entity<ChildTable>().HasKey(c => c.ChildID);
                modelBuilder.Entity<ChildDetail>().HasKey(c => c.ChildDetailID);

                modelBuilder.Entity<ChildTable>()
                     .HasRequired(p => p.ParentTable)
                     .WithMany(b => b.ChildTables)
                     .HasForeignKey(p => p.ParentID)
                     .WillCascadeOnDelete(true);

                modelBuilder.Entity<ChildDetail>()
                   .HasRequired(p => p.ChildTable)
                   .WithMany(b => b.ChildDetails)
                   .HasForeignKey(p => p.ChildID)
                   .WillCascadeOnDelete(true);

    And my delete statement using Entity Framework 4.3:

    DbContext dbc = new DbContext(connString);

    parent = GetById(p => p.ParentID == Utils.Parent2ID, "ChildTables.ChildDetails");

    dbc.Set<ParentTable>().Remove(parent);

    dbc.SaveChange();

    Then I get error like follows (my explanation is I need to explicitly delete the orphan objects?).

    Test method TestEngine.Cascade.DeleteParentChildrenDetails threw exception:
    System.InvalidOperationException: Adding a relationship with an entity which is in the Deleted state is not allowed.

    by the way, the table definition is like this:

    CREATE TABLE [dbo].[ParentTable](
        [ParentID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [ParentName] [varchar](250) NOT NULL);

    CREATE TABLE [dbo].[ChildTable](
        [ChildID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [ParentID] [int] NOT NULL,
        [ChildName] [varchar](250) NOT NULL)

    ALTER TABLE [dbo].[ChildTable]  WITH CHECK ADD  CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY([ParentID])
    REFERENCES [dbo].[ParentTable] ([ParentID])
    GO

    ALTER TABLE [dbo].[ChildTable] CHECK CONSTRAINT [FK_ChildTable_ParentTable]
    GO

    CREATE TABLE [dbo].[ChildDetail](
        [ChildDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [ChildID] [int] NOT NULL,
        [DetailName] [varchar](350) NOT NULL)

    ALTER TABLE [dbo].[ChildDetail]  WITH CHECK ADD  CONSTRAINT [FK_ChildDetail_ChildTable] FOREIGN KEY([ChildID])
    REFERENCES [dbo].[ChildTable] ([ChildID])
    GO

    ALTER TABLE [dbo].[ChildDetail] CHECK CONSTRAINT [FK_ChildDetail_ChildTable]
    GO

    (I used management studio to set on delete cascade for the two foreign keys).



    • Edited by Tony Zheng Saturday, February 18, 2012 12:57 AM
    Saturday, February 18, 2012 12:55 AM

Answers

  • Hi Tony,

    Welcome!

    I think your Fluent API looks good! Here is my testing code and works on EF4.3:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace Cascade
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new EFTestEntities())
                {
                    var parent = db.ParentTable.Include("ChildTables.ChildDetails").FirstOrDefault();
                    db.Set<ParentTable>().Remove(parent);
                    db.SaveChanges();
                }
            }
        }
        public partial class ParentTable
        {
            public ParentTable()
            {
                this.ChildTables = new HashSet<ChildTable>();
            }
    
            public int ParentID { get; set; }
            public string ParentName { get; set; }
    
            public virtual ICollection<ChildTable> ChildTables { get; set; }
        }
        public partial class ChildDetail
        {
            public int ChildDetailID { get; set; }
            public int ChildID { get; set; }
            public string DetailName { get; set; }
    
            public virtual ChildTable ChildTable { get; set; }
        }
        public partial class ChildTable
        {
            public ChildTable()
            {
                this.ChildDetails = new HashSet<ChildDetail>();
            }
    
            public int ChildID { get; set; }
            public int ParentID { get; set; }
            public string ChildName { get; set; }
    
            public virtual ICollection<ChildDetail> ChildDetails { get; set; }
            public virtual ParentTable ParentTable { get; set; }
        }
        public partial class EFTestEntities : DbContext
        {
            public EFTestEntities()
                : base("name=EFTest")
            {
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<ParentTable>().Property(c => c.ParentID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                modelBuilder.Entity<ParentTable>().HasKey(c => c.ParentID);
                modelBuilder.Entity<ChildTable>().HasKey(c => c.ChildID);
                modelBuilder.Entity<ChildDetail>().HasKey(c => c.ChildDetailID);
                modelBuilder.Entity<ParentTable>().ToTable("ParentTable");
                modelBuilder.Entity<ChildTable>().ToTable("ChildTable");
                modelBuilder.Entity<ChildDetail>().ToTable("ChildDetail");
    
                modelBuilder.Entity<ChildTable>()
                      .HasRequired(p => p.ParentTable)
                      .WithMany(b => b.ChildTables)
                      .HasForeignKey(p => p.ParentID)
                      .WillCascadeOnDelete(true);
    
                modelBuilder.Entity<ChildDetail>()
                    .HasRequired(p => p.ChildTable)
                    .WithMany(b => b.ChildDetails)
                    .HasForeignKey(p => p.ChildID)
                    .WillCascadeOnDelete(true);
    
            }
            public DbSet<ChildDetail> ChildDetail { get; set; }
            public DbSet<ChildTable> ChildTable { get; set; }
            public DbSet<ParentTable> ParentTable { get; set; }
        }
    }
    
    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.

    Monday, February 20, 2012 7:22 AM
    Moderator

All replies

  • Hi Tony,

    Welcome!

    I think your Fluent API looks good! Here is my testing code and works on EF4.3:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace Cascade
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new EFTestEntities())
                {
                    var parent = db.ParentTable.Include("ChildTables.ChildDetails").FirstOrDefault();
                    db.Set<ParentTable>().Remove(parent);
                    db.SaveChanges();
                }
            }
        }
        public partial class ParentTable
        {
            public ParentTable()
            {
                this.ChildTables = new HashSet<ChildTable>();
            }
    
            public int ParentID { get; set; }
            public string ParentName { get; set; }
    
            public virtual ICollection<ChildTable> ChildTables { get; set; }
        }
        public partial class ChildDetail
        {
            public int ChildDetailID { get; set; }
            public int ChildID { get; set; }
            public string DetailName { get; set; }
    
            public virtual ChildTable ChildTable { get; set; }
        }
        public partial class ChildTable
        {
            public ChildTable()
            {
                this.ChildDetails = new HashSet<ChildDetail>();
            }
    
            public int ChildID { get; set; }
            public int ParentID { get; set; }
            public string ChildName { get; set; }
    
            public virtual ICollection<ChildDetail> ChildDetails { get; set; }
            public virtual ParentTable ParentTable { get; set; }
        }
        public partial class EFTestEntities : DbContext
        {
            public EFTestEntities()
                : base("name=EFTest")
            {
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<ParentTable>().Property(c => c.ParentID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                modelBuilder.Entity<ParentTable>().HasKey(c => c.ParentID);
                modelBuilder.Entity<ChildTable>().HasKey(c => c.ChildID);
                modelBuilder.Entity<ChildDetail>().HasKey(c => c.ChildDetailID);
                modelBuilder.Entity<ParentTable>().ToTable("ParentTable");
                modelBuilder.Entity<ChildTable>().ToTable("ChildTable");
                modelBuilder.Entity<ChildDetail>().ToTable("ChildDetail");
    
                modelBuilder.Entity<ChildTable>()
                      .HasRequired(p => p.ParentTable)
                      .WithMany(b => b.ChildTables)
                      .HasForeignKey(p => p.ParentID)
                      .WillCascadeOnDelete(true);
    
                modelBuilder.Entity<ChildDetail>()
                    .HasRequired(p => p.ChildTable)
                    .WithMany(b => b.ChildDetails)
                    .HasForeignKey(p => p.ChildID)
                    .WillCascadeOnDelete(true);
    
            }
            public DbSet<ChildDetail> ChildDetail { get; set; }
            public DbSet<ChildTable> ChildTable { get; set; }
            public DbSet<ParentTable> ParentTable { get; set; }
        }
    }
    
    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.

    Monday, February 20, 2012 7:22 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?
    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    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.

    Thursday, February 23, 2012 2:06 AM
    Moderator
  • Thanks Alan. In my code I have turned off the change tracking;

    Configuration.AutoDetectChangesEnabled = false;

    I turned it off in order to get much better insert performance. Then possibly this makes cascade delete not work. Can you help to try if your code still work when turn off auto change tracking?

    Thanks

    Tony

    Thursday, February 23, 2012 6:10 PM
  • Alan, my code has worked. The root cause is I have overwritten some methods. After I took off these code, cascade delete worked for me even I turned off auto change tracking. Many thanks for you Alan.

    //Adding the following code will cause run time message when SaveChanges(). some message like "System.InvalidOperationException: Adding a relationship with an entity which is in the Deleted state is not allowed."

            public override bool Equals(object obj)
            {
                if (obj == null)
                {
                    return false;
                }

                if (obj.GetType() != typeof(ChildTable))
                    return false;

                return this == obj as ChildTable;
            }

            public override int GetHashCode()
            {
                return this.ChildID.GetHashCode();
            }

    • Marked as answer by Alan_chenModerator Friday, February 24, 2012 2:50 AM
    • Unmarked as answer by Tony Zheng Friday, February 24, 2012 2:49 PM
    Friday, February 24, 2012 1:17 AM
  • Alan, Can you test your code by overwriting the Equals and GetHashCode method of your POCO class and then run your code again to see if your cascading delete still works? I do need to overwrite these two methods in some cases. I wish overwriting Equals and GetHashCode will not prevent me from using cascading delete. thanks!

    Friday, February 24, 2012 2:51 PM