Entity framework code first delete with cascade

35,734

Solution 1

You mentioned EF code first which means EF 4.1 but you have shown example of deleting object in EF 4. Correct approach to delete object in EF 4.1 without loading it from database is:

var category = new Category() { CategoryId = 1 };
context.Categories.Attach(category);
context.Categories.Remove(category);
context.SaveChanges();

If you didn't change anything in configuration of defalut conventions it will also delete all related products because OneToManyCascadeDeleteConventions ensures that all one-to-many relations are created with ON CASCADE DELETE. There will be no additional roundtrips to database - only single DELETE statement for Category with Id = 1.

The different situation can occure if you want to delete fully loaded Category (with loaded Products navigation property) in such case EF will create separate delete statement for each Product so you will have N+1 roundtrips to database where N is number of products in category. Here is how cascade delete works in EF. It is related to entity designer but described principles are the same.

Solution 2

Taken from the VS2010 auto generated code:

Category category = db.Categories.Find(id);
db.Categories.Remove(category);
db.SaveChanges();
Share:
35,734

Related videos on Youtube

Shawn Mclean
Author by

Shawn Mclean

:)

Updated on July 09, 2022

Comments

  • Shawn Mclean
    Shawn Mclean over 1 year

    How do I set up my domain and the LINQ statement so I can delete a record from a database?

    public class Category    {
        public int CategoryId { get; set; }
        public string Name { get; set; }
    
        public List<Product> Products{ get; set; }
    
    }
    public class Product {
        public int ProductId { get; set; }
        public string Name { get; set; }
    
        public int CategoryId {get; set; }
        public Category Category{ get; set; }
    }
    

    What I'd like to do is delete Category and be able to cascade the delete to all the child products.

    1. Is there any other additional attributes needed in my domain?
    2. What is the LINQ statement to delete objects without doing a round trip? (I do not want to select, just a direct delete).

    Is this the only way to do this?

    Category category = new Category() {  CategoryId = 1   } ; 
    context.AttachTo("Category", category);
    context.DeleteObject(category);
    context.Savechanges();
    
  • Wil
    Wil about 12 years
    +1 You are a godsend at the moment for me! Your answers seem to keep coming up on top of Google when searching for EF issues!.... RE: Your last paragraph - If I have an object which has a virtual property (a list) which has not been accessed/lazy loaded, and I have cascade delete on, will it send the one delete command, or will it then load the virtual property and then send multiples?
  • Ladislav Mrnka
    Ladislav Mrnka about 12 years
    @William: It should delete them without loading because deletion is handled by cascade delete in the database.
  • Chris Moschini
    Chris Moschini almost 11 years
    This has never worked for me in EF4.1, 4.3, or 5.0. Attempting to delete an EF Code First entity this way with one-to-many children consistently gets me the Exception: The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.ChildTable_dbo.ParentTable_Parent_Id". The conflict occurred in database "Database", table "dbo.ChildTable", column 'Parent_Id'. The statement has been terminated.
  • Ladislav Mrnka
    Ladislav Mrnka almost 11 years
    @Chirs: It means that your database doesn't have cascade delete defined on relationships. EF's ability for cascade delete is dependent on cascade delete configured in the database.
  • Chris Moschini
    Chris Moschini almost 11 years
    @LadislavMrnka So after the db is created by EF Migrations, you have to go in and manually modify the schema? Or, I suppose, add manual migrations that keep updating and adding these constraints? My impression from other articles on this was that EF would delete only what's actually loaded into the context, rather than delegating it to the DB...?
  • Ladislav Mrnka
    Ladislav Mrnka almost 11 years
    @Chris: If you didn't changed the behavior EF code first and migration should use cascade deletes in database by default. Your impression is correct in terms of EF and that is a reason why we use database cascade delete to avoid errors when deleting entity which have not loaded dependencies.
  • Chris Moschini
    Chris Moschini almost 11 years
    @LadislavMrnka That's odd - I have no code manually modifying its behavior, and again this has never worked for me - to delete an Entity I have to load up and remove all child entities and work my way up the tree to the root object, in code, or use SQL. Otherwise I get a foreign key error from the DB. My code for creating my DbContext couldn't be simpler, so it's not the code in there for sure.
  • Elad Benda
    Elad Benda over 10 years
    how come I don't have the method Remove?
  • ajbeaven
    ajbeaven about 10 years
    @LadislavMrnka Do you have a reference to an article that supports teh cascading delete by default claim? I can't find anything on this.
  • Vasil Popov
    Vasil Popov over 9 years
    You should have it here as the "db.Categories..." is of type DbSet<T>, which has the Remove method.
  • esde84
    esde84 almost 9 years
    @ChrisMoschini I too was coming up against this issue with EF 6.1.1 I found that adding the [Required] attribute to parent reference in the child class, got the delete cascade working for me.