Introduction

The common way to delete an entity in Entity Framework is to retrieve the entity from the database into the context and then delete it from the context. Generally to delete the entity in Entity Framework, the developer uses the following.

  1. // Remove the entity from the entity collection
  2. using (Entities Context = new Entities())
  3. {
  4. DepartmentMaster deptDelete = Context.DepartmentMasters.Find(9);
  5. Context.DepartmentMasters.Remove(deptDelete);
  6. Context.SaveChanges();
  7. }
  9. // OR Changing the state of entity
  10. using (Entities Context = new Entities())
  11. {
  12. DepartmentMaster deptDelete = Context.DepartmentMasters.Find(9);
  13. Context.Entry(deptDelete).State = EntityState.Deleted;
  14. Context.SaveChanges();
  15. }

The code above has one problem; there are two database queries for one operation (one query for retrieving the data from the database and the other to delete the data from the database).

Interception/SQL logging in Entity Framework

Entity Framework 6.0 introduced the feature called "Logging SQL". While working with Entity Framework, it sends commands (or an equivalent SQL query) to the database to do a CRUD operation and this command can be intercepted by application code of Entity Framework. This feature of the Entity Framework is to capture an equivalent SQL query generated by Entity Framework internally and provide it as output.

How to enable SQL logging

The DbContext.Database.Log property can be set to delegate for any method that accepts a string as the parameter. Using this method, all SQL generated by the current context will be logged. For example, the following code can be used to send output to the Console.

  1. public Entities() : base("name=Entities")
  2. {
  3. Database.Log = Console.WriteLine;
  4. }

The following is the SQL logging output of the code above:

SQL logging output

Solution

The probable solutions of deleting the entity without retrieving it are given below.

  1. By Changing State

    DbContext has methods called Entry and Entry<TEntity>, these methods get a DbEntityEntry for the given entity and provide access to the information about the entity and return a DbEntityEntry object able to perform the action on the entity. Now we can perform the delete operation on the context by just changing the entity state to EntityState.Deleted.

    1. using (Entities Context = new Entities())
    2. {
    3. DepartmentMaster deptDelete = new DepartmentMaster { DepartmentId = 6 };
    4. Context.Entry(deptDelete).State = EntityState.Deleted;
    5. Context.SaveChanges();
    6. }

    Log SQL Output

    Log SQL output
  2. By Executing SQL Query

    1. using (Entities Context = new Entities())
    2. {
    3. Context.Database.ExecuteSqlCommand("Delete DepartmentMasters where DepartmentId = {0}", new object[] { 8 });
    4. }

    Log SQL Output

    Output

Summary

Using the methods described above, we can delete an existing entity without retrieving it from the database and we can gain some performance benefit.