none
When deleting entries in DB RRS feed

  • Question

  • Assuming I have a blog and posts DB where each blog can have many posts, how can I quickly delete one blog so that all its posts are deleted as well?

    Thanks to all


    MarcinMR

    Monday, February 22, 2016 9:31 PM

Answers

  • Yes, now I see.

    Setting Cascade in ssms is not enough for ef to recognize this.  You have to go into the Context.cs file and find it's OnModelCreating(DbModelBuilder modelBuilder) method.  In it you'll find the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(false);

    and you have to change IT to the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(true);

    thanks to all


    MarcinMR

    • Marked as answer by MarcinMR Tuesday, February 23, 2016 4:07 PM
    Tuesday, February 23, 2016 4:07 PM

All replies

  • Assuming I have a blog and posts DB where each blog can have many posts, how can I quickly delete one blog so that all its posts are deleted as well?

    Thanks to all


    MarcinMR

    Use cascading delete on the parent table at the DB engine level.

    http://www.codeproject.com/Articles/620086/CASCADE-in-SQL-Server-with-example

    You can set this up by using SSMS on the parent table to cascade all child table records. So you ust delete the parent using EF and the DB engine does the rest

    Tuesday, February 23, 2016 2:39 AM
  • Hi MarcinMR,

    >> how can I quickly delete one blog so that all its posts are deleted as well?

    You could use fluent API to achieve it. You will need something like this.

    modelBuilder.Entity<Blog>()
    
                    .HasMany(e => e.Posts)
    
                    .WithOptional(e => e.Blog)
    
                    .HasForeignKey(e => e.Blog_Id).WillCascadeOnDelete(true);
    

    You can configure cascade delete on a relationship by using the WillCascadeOnDelete method. If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

    For more information, please see below link.

    https://msdn.microsoft.com/en-us/data/jj591620.aspx#CascadeDelete

    Best regards,

    Cole Wu

    Tuesday, February 23, 2016 4:09 AM
    Moderator
  • Hi

    Using ssms I changed the foreign key Insert and Update property Delete rule to Cascade.  Now IN SSMS, when I delete a Blog, it will automatically delete all the Posts associated with that Blog...great, but this is in SSMS.  When I do this from EF however it does not work.  For example, when I run the following code, it comes back with an exception when I push the saveChanges to the db:

    var blog = db.Blogs.Find(4);
    db.Blogs.Remove(blog);
    db.SaveChanges();

    However, If I write code to first delete all the posts and then delete the blog, it will work?  

    PS.

    I should perhaps mention that in my program I only have Context.cs, Blog.cs and Post.cs.  No edmx file and no dbmodelbuilder that I have found anywhere.  I think I chose code first from existing db.


    MarcinMR


    • Edited by MarcinMR Tuesday, February 23, 2016 3:47 PM
    Tuesday, February 23, 2016 3:15 PM
  • I actually can't find dbmodelbuilder, I only have Blog.cs, Post.cs and Context.cs.  I assume this is a result of me choosing a "CodeFirst from Database" and not one of the other three options?  (I noticed that choosing EF Designer from database provides you with an edmx file where you can easily update your model if you changed it in say ssms).

    MarcinMR

    Tuesday, February 23, 2016 3:58 PM
  • Yes, now I see.

    Setting Cascade in ssms is not enough for ef to recognize this.  You have to go into the Context.cs file and find it's OnModelCreating(DbModelBuilder modelBuilder) method.  In it you'll find the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(false);

    and you have to change IT to the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(true);

    thanks to all


    MarcinMR

    • Marked as answer by MarcinMR Tuesday, February 23, 2016 4:07 PM
    Tuesday, February 23, 2016 4:07 PM
  • Yes, now I see.

    Setting Cascade in ssms is not enough for ef to recognize this.  You have to go into the Context.cs file and find it's OnModelCreating(DbModelBuilder modelBuilder) method.  In it you'll find the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(false);

    and you have to change IT to the following:

    modelBuilder.Entity<Blog>()
                    .HasMany(e => e.Posts)
                    .WithRequired(e => e.Blog)
                    .WillCascadeOnDelete(true);

    thanks to all


    MarcinMR

    Just take note that the cascading delete with EF can get one into trouble if it is taking too long on a timeout condition. I have seen it happen with EF sitting behind a WCF service where the WCF service timed out.

    Tuesday, February 23, 2016 4:15 PM