Entity Framework dbset most efficient way of deleting

Question

I have the following and looking for a more efficient way of deleting vs looping through the records and then deleting each one at a time (note using Dbset):

     var wcd = dbContext.ProgramDetails.Where(p => p.Id == Id);

     foreach (var wc in wcd.ToList())
     {
        dbContext.ProgramDetails.Remove(wc);
     }

     dbContext.SaveChanges();

Also say if we have 1 record that is as following:

    var pg = dbContext.Program.Where(p => p.Id == Id && Name == FName);

What is the best way of deleting this one record?

tried the following but gave an error:

    var pg = dbContext.Program.Where(p => p.Id == Id && Name == FName);
    dbContext.Program.Remove(wc);

Then I resorted to doing a foreach for deleting just one record as I have shown above that is not the most efficient for just 1 record.

If you don't track these entities and know the IDs (and you have a lot of IDs) you may consider using a Sql query. This way you don't need to bring entities to your context only to delete them. It's kind of going around EF so you need to answer the question whether it is worth it. — Pawel, Oct 11 '12 at 16:10

vittore · Accepted Answer · 2017-07-19T00:48:11.500

14

UPDATE FOR EF7:

using (var db = new BloggingContext())
{
  var blog = db.Blogs.First(p => p.Id == Id);
  db.Remove(blog);
  db.SaveChanges();
}

UPDATE MAY 2015: Check updated docs on msdn and examples . Example code to delete entity with EF6:

 public async Task<ActionResult> Delete(Department department) 
 { 
        try 
        { 
            db.Entry(department).State = EntityState.Deleted; 
            await db.SaveChangesAsync(); 
            return RedirectToAction("Index"); 
        } 
        catch (DbUpdateConcurrencyException) 
        { 
            return RedirectToAction("Delete", new { concurrencyError = true, id = department.DepartmentID }); 
        } 
        catch (DataException /* dex */) 
        { 
            //Log the error (uncomment dex variable name after DataException and add a line here to write a log. 
            ModelState.AddModelError(string.Empty, "Unable to delete. Try again, and if the problem persists contact your system administrator."); 
            return View(department); 
        } 
 }

The most effective way if you know ID and don't have entity loaded is to create fake entity and delete it

var p = new Program  { Id = myId } 
dbContext.Program.Remove(p)

But this won't work if you really have several records with the same id and you need to use name field as well to select right one.

Also your last example should be

var pg = dbContext.Program.First(p => p.Id == Id && p.Name == FName);
dbContext.Program.Remove(pg);

edited Jul 19 '17 at 00:48

answered Oct 11 '12 at 02:04

vittore

17,449
6
44
82

Should probably be dbContext.Program.First(), since where would return an Enumerable – Mark Oreta Oct 11 '12 at 02:05
@MarkOreta oh jeeze, for sure, didnt even notice that – vittore Oct 11 '12 at 02:42
Also don't forget to SaveChanges or else the Remove does not work. – John Henckel May 07 '15 at 13:22
This doesn't work for me! I'm using EF 6.1. I get error --> An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code. Additional information: The object cannot be deleted because it was not found in the ObjectStateManager. – John Henckel May 07 '15 at 13:59
1

The reason I got this error is my record has a non-nullable foreign key. The only way I found to delete is to first read it (using First) and then Remove it. See... http://forums.asp.net/t/1841285.aspx?How+do+I+delete+a+record+with+Entity+Framework+5 – John Henckel May 07 '15 at 14:10
@JohnHenckel things changed in two years. Check original documentation for EF6: https://msdn.microsoft.com/en-us/data/jj713564 ( part modifying relationships ) – vittore May 07 '15 at 15:57
How does the "UPDATE FOR EF7" improve anything? it looks like the "var p = new Program { Id = myId } " you wrote is much better (you don't travel twice to the DB)? – BornToCode Jan 17 '19 at 16:02
using find means we have now two transactions for one delete. Since EF only the id of object to generate required sql, can't we just initialise an object using the id: var blog = new Blog {id = 5} and then dbContext.Program.Remove(blog) – Masoud May 18 '20 at 23:53

Entity Framework dbset most efficient way of deleting

1 Answers1