More Efficient Deletes With Entity Framework Core

Deleting records from an existing database is a contentious topic. Whether you should or shouldn’t destroy data is up for debate, with strategies like Event Sourcing and Soft-Deletes being prominent proponents of not ever deleting anything. Regardless, Users still see the technical function of deleting records in Entity Framework Core as necessary. For example, without the delete, you can’t have Create, Read, Update, and Delete (CRUD); it would just be CRU.

All kidding aside, if you are familiar with Entity Framework Core deletes, you likely know that, in most cases, you might have to perform at least two database calls to remove an entity.

In this post, I’ll show you how to cut that in half while achieving the same result.

Multiple Calls To Delete An Entity

When you’re working with Entity Framework Core and the models you’ve added to your DbContext, you’re delegating to EF Core’s internal change tracker. The self-describing change tracker will determine what entities currently stored in memory have changed and generate SQL based on those changes. Change tracking applies to all CRUD operations, including delete. So, for example, look at a typical delete operation in an ASP.NET Core API endpoint.

app.MapDelete("/regular/{id:int}", async (Database db, int id) =>
{
    var person = await db.Persons.FindAsync(id); // 1 database call
    if (person is not null) db.Remove(person);
    await db.SaveChangesAsync(); // 1 database call

    // 2 total database calls
});

The code uses the identifier of an entity to call the database and confirm its existence. In doing so, you pull all the data related to that entity across the network and into an in-memory object. You then need to call Remove to tell EF Core’s change tracker to mark the entity for deletion. Finally, you make a second database call to execute a DELETE statement on the database record.

The fundamental problem here is two-fold: network calls are potentially expensive, and they may fail.

So every additional call you make is a point of failure you could avoid with one less database request. While the query to find a record may not be computationally expensive, it is typically unnecessary to retrieve all the data when a record’s ID is generally enough to determine its existence. Of course, you may need more data if there’s business logic around deleting records, and then the above approach would be perfectly valid and necessary.

What if you don’t? What if you could assume any identifier retrieved is ready for you to use for deletion?

One Less Network Call For EF Core Deletes

In the previous section, you read about EF Core’s most significant feature, change tracking. You can use change tracking to generate SQL for deletes without retrieving the entity from the database. The trick removes a network request from your deletion code paths. First, let’s have a look at the modified ASP.NET Core endpoint.

app.MapDelete("/efficient/{id:int}", async (Database db, int id) =>
{
    try
    {
        var person = db.Persons.Attach(new Person { Id = id });
        person.State = EntityState.Deleted;
        await db.SaveChangesAsync(); // 1 database call
    }
    catch (DbUpdateConcurrencyException e)
    {
        Console.WriteLine(e);
        // will happen if record no longer exists
    }
});

Let’s break down the above code and see how you can convert two database calls into one.

You create a new in-memory entity with an identifier you assume is in the database.
You change the EntityEntry<Person> to have a State of Deleted. The assignment informs the change tracker to generate the delete SQL.
You call SaveChangesAsync to issue the database request.
You catch a DbUpdateConcurrencyException on the chance that another process or client request has already deleted the record.

The technique cuts the network calls down to one, and the resulting network traffic is significantly less than if you retrieved the record and deleted it. So, appropriately, let’s take a look at the generated delete SQL.

In my testing, I saw endpoints go from 90ms down to 11ms. That’s a substantial difference when you’re talking about heavily trafficked endpoints.

  DELETE FROM "Persons"
  WHERE "Id" = @p0
  RETURNING 1;

Neat! One network call without the overhead. The RETURNING 1; assumes you’ll get a value back, so be sure to account for the DbUpdateConcurrencyException mentioned previously.

I think we can still do better, making it easier to reproduce the technique’s functionality without the additional code. So let’s wrap this in an extension method class.

I’ve written two implementations. The first extension method handles wrapping the call to the database and the logic required to handle any exceptions in the case the entity no longer exists.

The second allows you to add entities into the change tracker with the idea that you will handle any potential exceptions.

public static class DeleteExtensions
{
    public static async Task DeleteAsync<TEntity>(
        this DbContext db, 
        TEntity? entity, 
        bool throwOnNotFound = false)
    {
        try
        {
            if (entity is null) return;
            var track = db.Attach(entity);
            track.State = EntityState.Deleted;
            await db.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            // if entity doesn't exist in database, 
            // this will throw 
            if (throwOnNotFound) 
                throw;
        }
    }

    public static void Delete<TEntity>(
        this DbSet<TEntity> db, 
        TEntity entity)
        where TEntity: class
    {
        var track = db.Attach(entity);
        track.State = EntityState.Deleted;
    }
}

Let’s see each in use. The first is the call that does it all for you.

app.MapDelete(
    "/helper-one/{id:int}", 
    async (Database db, int id) =>
{
    await db.DeleteAsync(new Person { Id = id });
});

This approach is nice since it’s one call, but deleting multiple records from the database is an additional network request for each entity. The all-in-one approach might not be ideal when dealing with collections associated with an entity.

Let’s take a look at the second approach in action, where we add entries to the change tracker and then call the database.

app.MapDelete(
    "/helper-many/{id:int}", 
    async (Database db, int id) =>
{
    try
    {
        db.Persons.Delete(new () { Id = id });
        await db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException) { }
});

Neat! But you may be asking. What’s the difference between your Delete method and the out-of-the-box Remove method?

The Remove implementation doesn’t assume a non-attached entity should be automatically attached to your context. Let’s have a look.

public virtual EntityEntry<TEntity> Remove<TEntity>(TEntity entity)
    where TEntity : class
{
    Check.NotNull(entity, nameof(entity));
    CheckDisposed();

    var entry = EntryWithoutDetectChanges(entity);

    var initialState = entry.State;
    if (initialState == EntityState.Detached)
    {
        SetEntityState(entry.GetInfrastructure(), EntityState.Unchanged);
    }

    // An Added entity does not yet exist in the database. If it is then marked as deleted there is
    // nothing to delete because it was not yet inserted, so just make sure it doesn't get inserted.
    entry.State =
        initialState == EntityState.Added
            ? EntityState.Detached
            : EntityState.Deleted;

    return entry;
}

Essentially, it’s crucial to Attach an entity before EF Core attempts to generate your SQL. Otherwise, EF Core assumes you made a mistake and ignores the entity.

Entity Framework Core 7 Updates

If you’re reading this in the future and .NET 7 and Entity Framework Core 7 have been released, you’ll be happy to know there’s a new ExecuteDelete and ExecuteDeleteAsync method. You’ll be able to delete entities based on a LINQ criteria.

await context
    .Tags
    .Where(t => t.Text.Contains(".NET"))
    .ExecuteDeleteAsync()

The documentation states the following about the delete methods:

Calling ExecuteDelete or ExecuteDeleteAsync on a DbSet immediately deletes all entities of that DbSet from the database. – Microsoft Docs

A great addition to Entity Framework Core, but be aware of the potential to delete too much if you have a malformed LINQ statement.

Conclusion

There you have it. You can now perform more efficient delete operations and cut one whole database request out of your entity management lifecycle. It just takes a bit of understanding of EF Core’s internals and how the change tracker expects things to work. As mentioned before, in my testing, endpoints go from 100’s of milliseconds down to 10’s of milliseconds with the same result. So it’s an approach worth considering.

As I mentioned at the start of this post, there are different strategies around “deleting” entities, but this is a decent approach for folks looking to expel rows from the database. An everyday use case that would fit this approach is data management from a spreadsheet. You can delete rows knowing you’ll be reimporting them into your database through another mechanism.

Well, I hope you enjoyed this post, and if you could do me a favor, follow me on Twitter at @buhakmeh and be sure to share this post with friends and colleagues. As always, thanks for reading.