26

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();
Shawn Mclean
  • 56,733
  • 95
  • 279
  • 406

2 Answers2

41

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.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 1
    +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? – Wil Oct 27 '11 at 20:13
  • 1
    @William: It should delete them without loading because deletion is handled by cascade delete in the database. – Ladislav Mrnka Oct 30 '11 at 21:25
  • 2
    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. – Chris Moschini Dec 15 '12 at 21:04
  • @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. – Ladislav Mrnka Dec 15 '12 at 21:14
  • @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...? – Chris Moschini Dec 16 '12 at 20:48
  • @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. – Ladislav Mrnka Dec 19 '12 at 10:47
  • @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. – Chris Moschini Dec 19 '12 at 16:08
  • @LadislavMrnka Do you have a reference to an article that supports teh cascading delete by default claim? I can't find anything on this. – ajbeaven Oct 21 '13 at 08:00
  • 1
    @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. – esde84 Nov 20 '14 at 10:04
3

Taken from the VS2010 auto generated code:

Category category = db.Categories.Find(id);
db.Categories.Remove(category);
db.SaveChanges();
Vasil Popov
  • 1,210
  • 14
  • 22