Modify or Delete Entity Object without Select

16 May 2016

For doing any action i.e., edit or delete, we normally select the particular entity and we perform the required action on it.

Look through below example, it might look too familiar for you

You have constructed a edit profile page, so on submission/HTTP POST of modified data you need persist this changes in database. Normally you can do it through following code

/*Select the entity based on ID & update the changed values*/
var person = dbContext.People.Where(p => p.Id == 22);
person.FirstName = "Robert";
person.LastName = "Hawkings";
dbContext.SaveChanges(); /* Persist */

The above Entity code would require 2 database queries for that.

Similarly, for deleting an entity object you could be doing something like below

/*Select the entity based on ID & delete*/
var person = dbContext.People.Where(p => p.Id == 22); 
dbContext.People.Remove(person);
dbContext.SaveChanges();

Solution

You can achieve above two functionalities with a single SQL query generated by the Entity Framework. From EF 4.1 or latter, Microsoft has introduced Attach and Detach methods on DbSet class.

So for our current requirement, first create a disconnected model object & specify primary key ID, attach this object to DbTable/DbSet class.

Employee employee = new Employee(){ Id = 22 };
dbContext.Employees.Attach(employee); 
/* Now the entity framework will start tracking this object, 
any update to the values will be tracked and persisted on commit*/
employee.FirstName = "Robert";
employee.LastName = "hawkings";
dbContext.SaveChanges();

Performance Improvement : Now above code would need a single SQL query to save the changes without a need of select query.

Partial update of a Large Entity object : And above method is very useful if you want to partially update an Entity (Example: 2 fields from a Entity with more than 20 properties in it) without affecting the other fields. As Entity Framework will track just those 2 modified values and persist.

Similarly, for delete you can do following

Employee employee = new Employee(){ Id = 22 };
dbContext.Employees.Attach(employee);
dbContext.Employees.Remove(employee);
dbContext.SaveChanges();

Hope this helps.