ExecuteDeleteAsync

Delete rows with a single DELETE statement, purge large tables in batches, and know how cascades, query filters and soft deletes behave.

Signature

public static Task<int> ExecuteDeleteAsync<TSource>(this IQueryable<TSource> source, CancellationToken cancellationToken = default)

Added in .NET EF Core 7.0

Parameters

Parameter	Type	Description
`$source`	`IQueryable<TSource>`	The rows to delete. Where, navigations in predicates, OrderBy with Take, and global query filters all shape the DELETE.
`$cancellationToken`optional	`CancellationToken`= `default`	Cancels the command.

Return value

Task<int>The number of rows the database reports as deleted from the entity's table (rows removed by database cascades are not counted).

Errors

The provider's DbException, for example PostgresException with SqlState 23503 when a foreign key with ON DELETE RESTRICT or NO ACTION still references a row. InvalidOperationException when the query cannot be translated to a DELETE of one table.

ExecuteDeleteAsync turns a LINQ query into one DELETE FROM ... WHERE ... and runs it at once. Nothing is loaded or tracked, and the return value is the row count the database reports. Deleting a row by id without a prior SELECT, purging expired carts, or removing a tenant's data becomes one statement.

Cascades: the database decides, not EF Core

With Remove plus SaveChangesAsync, EF Core cascades in memory to tracked dependents and then relies on the foreign key. With ExecuteDeleteAsync only the database's foreign key rules apply:

FK rule in the schema	Result of deleting the principal
`ON DELETE CASCADE`	Dependents are deleted by the database; they are not in the returned count
`ON DELETE SET NULL`	Dependents keep their rows with a null foreign key
`RESTRICT` / `NO ACTION`	The statement fails with a foreign key violation; nothing is deleted

The EF Core model's OnDelete setting matters only through the constraint the migration created. If you configured ClientCascade, nothing cascades here. Delete the dependents first (db.OrderLines.Where(...).ExecuteDeleteAsync(), then the orders) inside one transaction.

Query filters and soft deletes

Global query filters are part of the WHERE: with a tenant filter, a delete only reaches the current tenant's rows, which is a useful safety net. The trap is soft delete. If deletes are normally turned into IsDeleted = true by an interceptor or a SaveChanges override, ExecuteDeleteAsync skips that code and removes the rows for real. On entities that are soft-deleted, use ExecuteUpdateAsync(s => s.SetProperty(e => e.IsDeleted, true)) instead, and keep ExecuteDeleteAsync for the retention job that purges rows deleted long ago, with IgnoreQueryFilters made explicit.

Large deletes in batches

A single DELETE of ten million rows holds locks and generates write-ahead log or transaction log for the whole run, delays replicas, and on failure rolls everything back. Delete in chunks: Where(predicate).OrderBy(c => c.Id).Take(5000).ExecuteDeleteAsync() in a loop until fewer than 5,000 rows come back. Where the database cannot apply a limit to a DELETE directly, EF Core rewrites the statement to delete by key from a subquery. Each chunk commits on its own, so the job can stop and resume.

Production failure modes

Tracked entities that were deleted this way are still in the change tracker as Unchanged; a later SaveChangesAsync that modifies one fails with a concurrency exception because its row is gone.
A purge running inside one huge DELETE blocks inserts on the same table for minutes at peak time. A PeriodicTimer of 24 hours counts from startup, so every deploy triggers a purge, and every replica runs its own; schedule the job for an off-peak time on one instance (a Kubernetes CronJob, or a leader lock).
A hard delete through ExecuteDelete on a soft-delete entity removes data that support expected to be able to restore.

Example

using Microsoft.EntityFrameworkCore;

var builder = Host.CreateApplicationBuilder(args);
builder.Services.AddDbContextFactory<ShopDb>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));
builder.Services.AddHostedService<CartPurge>();
builder.Build().Run();

// Nightly retention at 03:00 UTC: delete carts untouched for 30 days in chunks of 5,000 rows.
// Run it on one replica only (or as a Kubernetes CronJob); every replica would otherwise purge the same rows.
public sealed class CartPurge(IDbContextFactory<ShopDb> contexts, ILogger<CartPurge> log) : BackgroundService
{
    private const int BatchSize = 5_000;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            // Wait for the next 03:00 UTC, not 24 hours from startup: a deploy must not trigger a purge at peak time.
            var now = DateTimeOffset.UtcNow;
            var next = new DateTimeOffset(now.Date.AddHours(3), TimeSpan.Zero);
            if (next <= now) next = next.AddDays(1);
            await Task.Delay(next - now, stoppingToken);

            try
            {
                await PurgeAsync(stoppingToken);
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                // An unhandled exception would stop the host; log it and try again tomorrow.
                log.LogError(ex, "Cart purge failed");
            }
        }
    }

    private async Task PurgeAsync(CancellationToken ct)
    {
        await using var db = await contexts.CreateDbContextAsync(ct);
        var cutoff = DateTimeOffset.UtcNow.AddDays(-30);
        var total = 0;

        while (!ct.IsCancellationRequested)
        {
            // CartItems has ON DELETE CASCADE to Carts, so the database removes the items.
            var deleted = await db.Carts
                .Where(c => c.LastTouchedAt < cutoff)
                .OrderBy(c => c.Id)
                .Take(BatchSize)
                .ExecuteDeleteAsync(ct);

            total += deleted;
            if (deleted < BatchSize) break;
            await Task.Delay(TimeSpan.FromMilliseconds(200), ct);   // let other writers in
        }

        log.LogInformation("Purged {Count} abandoned carts older than {Cutoff}", total, cutoff);
    }
}

public class Cart
{
    public Guid Id { get; set; }
    public DateTimeOffset LastTouchedAt { get; set; }
    public List<CartItem> Items { get; } = [];
}

public class CartItem
{
    public int Id { get; set; }
    public Guid CartId { get; set; }
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Cart> Carts => Set<Cart>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Cart>()
            .HasMany(c => c.Items).WithOne()
            .HasForeignKey(i => i.CartId)
            .OnDelete(DeleteBehavior.Cascade);   // becomes ON DELETE CASCADE in the migration
}

When to use it

Purging abandoned carts older than 30 days every night in chunks of 5,000 so inserts on the table are never blocked for long
Removing a sandbox customer's order lines and orders in one transaction where the foreign key uses RESTRICT
Hard-deleting customers that were soft-deleted more than 90 days ago, with IgnoreQueryFilters made explicit

Frequently asked questions

Does ExecuteDeleteAsync cascade to child rows?

Only if the database foreign key says ON DELETE CASCADE. EF Core does no in-memory cascade here. With RESTRICT or NO ACTION the statement fails, so delete the children first in the same transaction.

Why did ExecuteDeleteAsync return 0 when the rows exist?

A global query filter is part of the WHERE. A soft-delete or tenant filter can exclude the rows you meant to delete. Call IgnoreQueryFilters, or IgnoreQueryFilters(["SoftDelete"]) with EF Core 10 named filters, to lift only the one you intend.

More examples

Soft delete with ExecuteUpdate, hard delete only for retention

Because the filter <code>!IsDeleted</code> is added to every query, <code>PurgeExpired</code> without <code>IgnoreQueryFilters</code> would find nothing and return 0. Being explicit here also documents that this is the one place where hard deletes are intended.

using Microsoft.EntityFrameworkCore;

public sealed class CustomerDeletion(ShopDb db, TimeProvider clock)
{
    // What DELETE /customers/{id} should do: a soft delete that support can undo.
    public async Task<bool> SoftDelete(int id, CancellationToken ct) =>
        await db.Customers.Where(c => c.Id == id)
            .ExecuteUpdateAsync(s => s
                .SetProperty(c => c.IsDeleted, true)
                .SetProperty(c => c.DeletedAt, clock.GetUtcNow()), ct) == 1;

    // The retention job: rows soft-deleted over 90 days ago are removed for real.
    public Task<int> PurgeExpired(CancellationToken ct)
    {
        var cutoff = clock.GetUtcNow().AddDays(-90);
        return db.Customers
            .IgnoreQueryFilters()   // the soft-delete filter would otherwise hide every target row
            .Where(c => c.IsDeleted && c.DeletedAt < cutoff)
            .ExecuteDeleteAsync(ct);
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Customer>().HasQueryFilter(c => !c.IsDeleted);
}

Deleting children before parents under RESTRICT

Deleting the orders first would fail with a foreign key violation (PostgreSQL SqlState 23503). The transaction keeps the two statements atomic, so a failure on the second leaves the lines in place instead of orphaning orders without lines.

using Microsoft.EntityFrameworkCore;

public sealed class TestOrderCleanup(ShopDb db)
{
    // OrderLines -> Orders uses RESTRICT, so the database will not cascade.
    public async Task<int> RemoveSandboxOrders(int sandboxCustomerId, CancellationToken ct)
    {
        await using var tx = await db.Database.BeginTransactionAsync(ct);

        await db.OrderLines
            .Where(l => l.Order.CustomerId == sandboxCustomerId)   // navigation in the WHERE is fine
            .ExecuteDeleteAsync(ct);

        var orders = await db.Orders
            .Where(o => o.CustomerId == sandboxCustomerId)
            .ExecuteDeleteAsync(ct);

        await tx.CommitAsync(ct);
        return orders;
    }
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<OrderLine>().HasOne(l => l.Order).WithMany()
            .HasForeignKey(l => l.OrderId).OnDelete(DeleteBehavior.Restrict);
}

Test: the returned count and what remains

SQLite supports ExecuteDelete, so the translation and the count can be checked quickly. Behaviour that depends on the real schema, such as foreign key rules and PostgreSQL error codes, belongs in a Testcontainers test against PostgreSQL.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;

public sealed class ExpiredCartTests
{
    [Fact]
    public async Task Deletes_only_carts_older_than_the_cutoff()
    {
        var ct = TestContext.Current.CancellationToken;
        await using var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync(ct);
        var options = new DbContextOptionsBuilder<ShopDb>().UseSqlite(connection).Options;

        await using (var seed = new ShopDb(options))
        {
            await seed.Database.EnsureCreatedAsync(ct);
            seed.Carts.AddRange(
                new Cart { Id = 1, AgeInDays = 45 },
                new Cart { Id = 2, AgeInDays = 31 },
                new Cart { Id = 3, AgeInDays = 2 });
            await seed.SaveChangesAsync(ct);
        }

        await using var db = new ShopDb(options);
        var deleted = await db.Carts.Where(c => c.AgeInDays > 30).ExecuteDeleteAsync(ct);

        Assert.Equal(2, deleted);
        Assert.Equal(new[] { 3 }, await db.Carts.Select(c => c.Id).ToListAsync(ct));
    }
}

public class Cart
{
    public int Id { get; set; }
    public int AgeInDays { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Cart> Carts => Set<Cart>();
}

Discussion

Be the first to comment on this lesson.

Parameters

Return value

Errors

See also

Cascades: the database decides, not EF Core

Query filters and soft deletes

Large deletes in batches

Production failure modes

Example

When to use it

Frequently asked questions

Does ExecuteDeleteAsync cascade to child rows?

Why did ExecuteDeleteAsync return 0 when the rows exist?

More examples

Soft delete with ExecuteUpdate, hard delete only for retention

Deleting children before parents under RESTRICT

Test: the returned count and what remains

Related

Discussion