Delete a single record from Entity Framework?

Question

I have a SQL Server table in Entity Framework named employ with a single key column named ID.

How do I delete a single record from the table using Entity Framework?

• Carter Medlin: db.employ.Remove(db.employ.Find(ID1))
• Davor: @CarterMedlin - while that will work, those are two database hits: one SELECT and one DELETE. Most people find that extremely wasteful, especially since select will probably take significantly more time than a delete.
• curiousBoy: I would not suggest to use entity framework Remove or RemoveRange due to the performance issues. I would rather just use something super simple as following: var sql = "DELETE FROM YOUR_TABLE WHERE YOUR_FIELD= @your_parameter"; this.your_context.Database.ExecuteSqlCommand(sql, new SqlParameter("@your_parameter", yourParameter))
• Yitzchak: @curiousBoy I think that when you execute statements like you suggested, the EF6 cache doesn't reflect the change.
• Gert Arnold: EF-core alternative: ExecuteDelete.

Answer 1

It's not necessary to query the object first, you can attach it to the context by its id. Like this:

var employer = new Employ { Id = 1 };
ctx.Employ.Attach(employer);
ctx.Employ.Remove(employer);
ctx.SaveChanges();

Alternatively, you can set the attached entry's state to deleted :

var employer = new Employ { Id = 1 };
ctx.Entry(employer).State = EntityState.Deleted;
ctx.SaveChanges();

• Simon Belanger: Alternatively, ctx.Entry(employer).State = EntityState.Deleted
• baruchl: this will only work if the relationships are defined as delete cascade. otherwise the code above will fail on an FK exception.
• baruchl: @mt_serg, I'm looking 3 steps ahead. when was the last time you really had to remove such a simple record from the DB? usually you are dealing with more complex records that include FK relations. hence my comment.
• Simon Belanger: @IanWarburton The 2nd and 3rd line (Attach and Remove)
• siride: @PaulZahra: sometimes you have a list of IDs from some other query or source, and you need to delete one. Rather than loading up the objects just to delete them, this way you can delete by ID. You know, that's how the DELETE statement works in SQL normally.

Answer 2

You can use SingleOrDefault to get a single object matching your criteria, and then pass that to the Remove method of your EF table.

var itemToRemove = Context.Employ.SingleOrDefault(x => x.id == 1); //returns a single item.
if (itemToRemove != null) {
 Context.Employ.Remove(itemToRemove);
 Context.SaveChanges();
}

• Ali Yousefi: this is not good way, because you are select all field from database!
• Jack Fairfield: This is the way I do it.
• Uzair Khan: @Ali, Jack - But I think this is preferable because it first checks if the data you are trying to delete actually exists which can prevent any trouble. The accepted answer has no check as such.
• Yusha: This is the better way. Think about it. What if John Smith is trying to remove an item with an id = 1 that Susie Smith removed 30 seconds ago but John doesn't know? You need to hit the database in that case.
• 9Rune5: @Yusha Why? In both scenarios the outcome is that the record is gone. Do we really care if that happened now or 30 seconds ago? Some race conditions just aren't all that interesting to keep track of.

Answer 3

 var stud = (from s1 in entities.Students
 where s1.ID== student.ID
 select s1).SingleOrDefault();
 //Delete it from memory
 entities.DeleteObject(stud);
 //Save to database
 entities.SaveChanges();

• Mark Sowul: FirstOrDefault is dangerous. Either you know there's only one (so use SingleOrDefault), or there is more than one, and it should be done in a loop.

Answer 4

I am using entity framework with LINQ. Following code was helpful for me;

1- For multiple records

 using (var dbContext = new Chat_ServerEntities())
 {
 var allRec= dbContext.myEntities;
 dbContext.myEntities.RemoveRange(allRec);
 dbContext.SaveChanges();
 }

2- For Single record

 using (var dbContext = new Chat_ServerEntities())
 {
 var singleRec = dbContext.ChatUserConnections.FirstOrDefault( x => x.ID ==1);// object your want to delete
 dbContext.ChatUserConnections.Remove(singleRec);
 dbContext.SaveChanges();
 }

• Mark Sowul: For Single record why not use SingleOrDefault instead of FirstOrDefault?
• Baqer Naqvi: Whenever you use SingleOrDefault, you clearly state that the query should result in at most a single result. On the other hand, when FirstOrDefault is used, the query can return any amount of results but you state that you only want the first one stackoverflow.com/a/1745716/3131402
• Mark Sowul: Yes, so why would it be correct to delete an arbitrary record, if there is more than one? Particularly in this case the id is the key, so there should be one: if there is more than one, it is a bug (which Single would detect)
• Baqer Naqvi: @MarkSowul you are right. I have edited the answer to use FirstOrDefault.
• curiousBoy: @BaqerNaqvi RemoveRange is terrible way to remove entity from the performance perspective.. Especially when your entity is heavy with all the navigational properties by foreign keys. I would rather use var sql = "DELETE FROM YOUR_TABLE WHERE YOUR_FIELD= @your_parameter"; this.your_context.Database.ExecuteSqlCommand(sql, new SqlParameter("@your_parameter", yourParameter))

Answer 5

Employer employer = context.Employers.First(x => x.EmployerId == 1);
context.Customers.DeleteObject(employer);
context.SaveChanges();

• Jack Fairfield: Does this protect if there is no object with Id 1? Wouldn't it throw an exception?
• Jawand Singh: @JackFairfield i think you should check for null object. and according to it perform remove.
• Mark Sowul: First is dangerous. Either you know there's only one (so use Single), or there is more than one, and it should be done in a loop.

Answer 6

Just wanted to contribute the three methods I've bounced back and forth with.

Method 1:

var record = ctx.Records.FirstOrDefault();
ctx.Records.Remove(record);
ctx.SaveChanges();

Method 2:

var record = ctx.Records.FirstOfDefault();
ctx.Entry(record).State = EntityState.Deleted;
ctx.SaveChanges();
ctx.Entry(record).State = EntityState.Detached;

One of the reasons why I prefer to go with Method 2 is because in the case of setting EF or EFCore to QueryTrackingBehavior.NoTracking, it's safer to do.

Then there's Method 3:

var record = ctx.Records.FirstOrDefault();
var entry = ctx.Entry(record);
record.DeletedOn = DateTimeOffset.Now;
entry.State = EntityState.Modified;
ctx.SaveChanges();
entry.State = EntityState.Detached;

This utilizes a soft delete approach by setting the record's DeletedOn property, and still being able to keep the record for future use, what ever that may be. Basically, putting it in the Recycle Bin.

---

Also, in regards to Method 3, instead of setting the entire record to being modified:

entry.State = EntityState.Modified;

You would also simply set only the column DeletedOn as modified:

entry.Property(x => x.DeletedOn).IsModified = true;

Answer 7

More generic approach:

public virtual void Delete<T>(int id) where T : BaseEntity, new()
{
 T instance = Activator.CreateInstance<T>();
 instance.Id = id;
 if (dbContext.Entry<T>(entity).State == EntityState.Detached)
 {
 dbContext.Set<T>().Attach(entity);
 }
 
 dbContext.Set<T>().Remove(entity);
}

• Arjan Einbu: Why Activator.CreateInstance<T>() instead of new T()
• valentasm: Overall it is the same under the hood, just new T() is syntactic sugar. new () enforces that class has empty constructor, so all good for activator since if class dont have empty ctor then it would throw exception

Answer 8

With Entity Framework 6, you can use Remove. Also it 's a good tactic to use using for being sure that your connection is closed.

using (var context = new EmployDbContext())
{
 Employ emp = context.Employ.Where(x => x.Id == id).Single<Employ>();
 context.Employ.Remove(emp);
 context.SaveChanges();
}

Answer 9

Using EntityFramework.Plus could be an option:

dbContext.Employ.Where(e => e.Id == 1).Delete();

More examples are available here

Answer 10

 [HttpPost]
 public JsonResult DeleteCotnact(int id)
 {
 using (MycasedbEntities dbde = new MycasedbEntities())
 {
 Contact rowcontact = (from c in dbde.Contact
 where c.Id == id
 select c).FirstOrDefault();
 dbde.Contact.Remove(rowcontact);
 dbde.SaveChanges();
 return Json(id);
 }
 }

What do you think of this, simple or not, you could also try this:

 var productrow = cnn.Product.Find(id);
 cnn.Product.Remove(productrow);
 cnn.SaveChanges();

Answer 11

u can do it simply like this

 public ActionResult Delete(int? id)
 {
 using (var db = new RegistrationEntities())
 {
 Models.RegisterTable Obj = new Models.RegisterTable();
 Registration.DAL.RegisterDbTable personalDetail = db.RegisterDbTable.Find(id);
 if (personalDetail == null)
 {
 return HttpNotFound();
 }
 else
 {
 Obj.UserID = personalDetail.UserID;
 Obj.FirstName = personalDetail.FName;
 Obj.LastName = personalDetail.LName;
 Obj.City = personalDetail.City;
 }
 return View(Obj);
 }
 }
 [HttpPost, ActionName("Delete")]
 public ActionResult DeleteConfirmed(int? id)
 {
 using (var db = new RegistrationEntities())
 {
 Registration.DAL.RegisterDbTable personalDetail = db.RegisterDbTable.Find(id);
 db.RegisterDbTable.Remove(personalDetail);
 db.SaveChanges();
 return RedirectToAction("where u want it to redirect");
 }
 }

model

 public class RegisterTable
{
 public int UserID
 { get; set; }
 public string FirstName
 { get; set; }
 public string LastName
 { get; set; }
 public string Password
 { get; set; }
 public string City
 { get; set; }
} 

view from which u will call it

 <table class="table">
 <tr>
 <th>
 FirstName
 </th>
 <th>
 LastName
 </th>
 <th>
 City
 </th>
 <th></th>
 </tr>
 @foreach (var item in Model)
 {
 <tr>
 <td> @item.FirstName </td>
 <td> @item.LastName </td>
 <td> @item.City</td>
 <td>
 <a href="@Url.Action("Edit", "Registeration", new { id = item.UserID })">Edit</a> |
 <a href="@Url.Action("Details", "Registeration", new { id = item.UserID })">Details</a> |
 <a href="@Url.Action("Delete", "Registeration", new { id = item.UserID })">Delete</a>
 </td>
 </tr>
 }
</table>

i hope this will be easy for u to understand

Answer 12

You can do something like this in your click or celldoubleclick event of your grid(if you used one)

if(dgEmp.CurrentRow.Index != -1)
 {
 employ.Id = (Int32)dgEmp.CurrentRow.Cells["Id"].Value;
 //Some other stuff here
 }

Then do something like this in your Delete Button:

using(Context context = new Context())
{
 var entry = context.Entry(employ);
 if(entry.State == EntityState.Detached)
 {
 //Attached it since the record is already being tracked
 context.Employee.Attach(employ);
 } 
 //Use Remove method to remove it virtually from the memory 
 context.Employee.Remove(employ);
 //Finally, execute SaveChanges method to finalized the delete command 
 //to the actual table
 context.SaveChanges();
 //Some stuff here
}

Alternatively, you can use a LINQ Query instead of using LINQ To Entities Query:

var query = (from emp in db.Employee
where emp.Id == employ.Id
select emp).Single();

employ.Id is used as filtering parameter which was already passed from the CellDoubleClick Event of your DataGridView.

• arvin aquio: The Idea behind the code is you wire the id(employ.Id) of the record you want to delete to the model(Employee Class) and then attach it to the actual Table from the Context then execute in-memory Remove() Method then finally execute actual saving to the database using SaveChanges() Method. Though the LINQ Query also works fine but I don't like the idea of querying to the table just to get the id of the record.

Answer 13

Here's a safe way:

using (var transitron = ctx.Database.BeginTransaction())
{
 try
 {
 var employer = new Employ { Id = 1 };
 ctx.Entry(employer).State = EntityState.Deleted;
 ctx.SaveChanges();
 transitron.Commit();
 }
 catch (Exception ex)
 {
 transitron.Rollback();
 //capture exception like: entity does not exist, Id property does not exist, etc...
 }
}

Here you can pile up all the changes you want, so you can do a series of deletion before the SaveChanges and Commit, so they will be applied only if they are all successful.

Answer 14

The best way is to check and then delete

 if (ctx.Employ.Any(r=>r.Id == entity.Id))
 {
 Employ rec = new Employ() { Id = entity.Id };
 ctx.Entry(rec).State = EntityState.Deleted;
 ctx.SaveChanges();
 }

Answer 15

For a generic DAO this worked:

 public void Delete(T entity)
 {
 db.Entry(entity).State = EntityState.Deleted;
 db.SaveChanges();
 }