Entity framework delete records without loading

This article will cover all the different approaches to delete a record using the entity framework. The default approach of entity framework delete by loading the record is not optimal from a performance point of view. Make sure you go through the different methods to make your code more performant.

Delete by Loading the Record

Delete by loading in the DBContext is the standard approach to delete a record using the entity framework. Using this approach, we first load the record in the DBContent. Once loaded, the DBContext will track all the changes done to the object. The Remove method will mark the entity state to be set a Deleted. Once we call the SaveChanges the record is deleted from the database.

context.Remove(context.Cats.Single(a => a.Id == 1));
await context.SaveChangesAsync();

If we look at the resulting SQL we can see that this is very inefficient from a performance point of view as two commands are required to delete the record. We have a call loading the record from the database and a call to delete the actual record from the database.

SELECT TOP(2)
[Extent1].[Id] AS [Id]
[Extent1].[Name] AS [Name]
        FROM [dbo].[Cats] As [Extent1]
        WHERE [Extent1].[DepartmentId] = @p0
-- p0 '1' (Type = Int32)
-- Executing ar 13/12/2020 10:15:15 AM +01:00
-- Completed in 12 ms with result: SqlDataReader

DELETE [dbo].[Cats]
WHERE ([Id] = @0)
-- p0 '9' (Type = Int32)
-- Executing ar 13/12/2020 10:15:15 AM +01:00
-- Completed in 14 ms with result: 1

Deleting by using a stub

We can optimize the delete process by using a stub of the object we want to delete. All we need is the primary record identifier. The following is an example.

Cat cat = new Cat;
cat.Id = 1;
context.Remove(cat);
await context.SaveChangesAsync();

Deleting using a stub will result in a single SQL command.

Deleting by changing the object state

Another efficient approach to delete a record is by changing the entity DBContext state of a stub object. We can change the state by accessing the Entry.State property using the DbContext.Entry method. The following is a code sample.

var cat = new Cat { Id = 1 };
context.Entry(cat).State = EntityState.Deleted;
await context.SaveChangesAsync();

Delete by Executing SQL 

With this approach, we will delete a record by passing a SQL script to be executed server side The following is the code sample.

string sql = "DELETE FROM Cats where Id = @Id"
Context.Database.ExecuteSqlCommand(sql, new SqlParameter("@Id", id));