Entity Framework unable to complete delete

Question

I am trying to delete an Invoice along with any related Costs. An Invoice to Costs is a 1 to many relationship.

My Snipp:

Invoice invoiceToDelete = db.Invoices.First(i => i.Id == id);
db.Invoices.Remove(invoiceToDelete);

var costs = db.Costs.Where(i => i.InvoiceId == id);

foreach (var cost in costs)
{
    //Delete all related costs.
    db.Costs.Remove(cost);
}

//Tried using this as well to try and delete all related costs
//db.Costs.RemoveRange(db.Costs.Where(x => x.InvoiceId == id));

db.SaveChanges();

If an Invoice has no related Costs, the code executes fine and the Invoice is removed from the table.

When an Invoice has related Costs, however, it throws the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_Costs_Invoices". The conflict occurred in database "MyDB", table "dbo.Costs", column 'InvoiceId'. The statement has been terminated.

Writing a delete SQL statement in SSMS, i'm able to delete from the Costs table according to the foreign key of InvoiceId.

Matt Rowland · Answer 1 · 2015-11-26T00:03:25.970

1

The best way to complete this would be to add a cascade on delete in your database. You most likely have a simple foreign key constraint. You will need to drop that constraint and add it again with a cascade on delete.

The statements to complete this will look something like the following:

ALTER TABLE dbo.Costs
   DROP CONSTRAINT FK_Invoices_Costs --name of constraint

ALTER TABLE dbo.Costs
   ADD CONSTRAINT FK_Invoices_Costs_Cascade
   FOREIGN KEY (InvoiceId) REFERENCES dbo.Invoices(InvoiceId) ON DELETE CASCADE

Once you have this setup; all the costs with the InvoiceId of the invoice that you delete with EF will then also be deleted.

edited Nov 26 '15 at 00:03

answered Nov 25 '15 at 23:53

Matt Rowland

4,575
4
25
34

Agreed, except I'd keep this within the entity framework so a dropped db will rebuild it back with the cascade. See this SO: http://stackoverflow.com/questions/17487577/entity-framework-ef-code-first-cascade-delete-for-one-to-zero-or-one-relations – MutantNinjaCodeMonkey Nov 25 '15 at 23:55
Very true, either way will accomplish the same thing. It all depends on if you need all interactions with the database to have the cascade or not. – Matt Rowland Nov 25 '15 at 23:57
@MutantNinjaCodeMonkey I am using a code first from an existing database implementation. Is it best practice to setup the cascade in the manner described in the SO post you linked or is Matt's SQL essentially doing the same thing and maybe a cleaner approach? – KidBatman Nov 26 '15 at 00:48
Adding the mapping that is mentioned in @MutantNinjaCodeMonkey's link will be the route to go if you do not want the cascade with other interactions with the same database. If you set it in the database using the FK constraint then it will always cascade not just in your current application, but every application (or direct SQL) that uses it. The short answer would be that the situation dictates which route to choose but either one will accomplish what you are wanting to do. – Matt Rowland Nov 26 '15 at 00:52
There is another item to consider since you are using Code First from DB. If you already had the FK set up to cascade on delete then when you generated your Code First model the mapping in your context would had auto generated. So, you could think about adding the cascade to both the DB and your EF context. – Matt Rowland Nov 26 '15 at 00:55
Added the `Cascade` to my `FK`, but now i'm getting a totally different error. "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See nfor information on understanding and handling optimistic concurrency exceptions." Possibly a race condition? I'm considering using `context.Database.SqlCommand` and just write a SQL statement to delete the desired costs. – KidBatman Nov 26 '15 at 08:33
You are getting this because of the code first database seed. If you want to do away with this error add this line of code to your `OnModelCreating` Method in your context. `Database.SetInitilizer(null);` Make sure you replace the `TContext` with the name of your context class. The documentation for this method is here. https://msdn.microsoft.com/en-us/library/gg679461(v=vs.113).aspx (This method is to seed how your model manages your database. Since you are using Code First from DB you want to make the seed null.) – Matt Rowland Nov 26 '15 at 12:45

score 0 · Answer 2 · answered Nov 25 '15 at 23:50

0

Can you move delete invoice to be after loop? e.g.:

var costs = db.Costs.Where(i => i.InvoiceId == id);

foreach (var cost in costs)
{
    //Delete all related costs.
    db.Costs.Remove(cost);
}
db.Invoices.Remove(invoiceToDelete);

answered Nov 25 '15 at 23:50

Eckd

358
1
10

score 0 · Answer 3 · answered Nov 26 '15 at 00:06

If your model is set up correctly so that it knows you have a foreign key constraint, I believe all you really need to do is make sure EF has all the relevant info before you delete it, like so:

Invoice invoiceToDelete = db.Invoices.Include(i=>i.Costs).First(i => i.Id == id);
db.Invoices.Remove(invoiceToDelete);
db.SaveChanges();

score -1 · Accepted Answer · answered Nov 29 '15 at 03:22

Unfortunately, none of the suggestions worked for my situation. My work around was to make the changes using two different DbContext instances to make the changes to both Invoices & Costs, respectively.

using (var ctx = new MyDbContext())
{
      //Delete Invoice
      ctx.SaveChanges();
}

using (var ctx = new MyDbContext())
{
      //Delete related costs
      ctx.SaveChanges();
}

Many thanks to Matt Rowland. His answer was probably the more appropriate solution, but unfortunately for whatever reason I could not get to work for my particular instance. The sln that i'm working on was originally developed by consultants and they may not have been adhering to EF standard practices.

Entity Framework unable to complete delete

4 Answers4