Entity Framework. Delete all rows in table

sql

entity-framework

database-management

data-deletion

byAlex Kataev·Oct 12, 2024

Delete all rows in an Entity Framework table without loading entities in memory by running a SQL DELETE command:

context.Database.ExecuteSqlCommand("DELETE FROM YourEntity");
context.SaveChanges();

In EF Core, the method changes to ExecuteSqlRaw or the async version ExecuteSqlRawAsync:

await context.Database.ExecuteSqlRawAsync("DELETE FROM YourEntity");

But when it comes to identity resets, then TRUNCATE TABLE is your savior:

context.Database.ExecuteSqlRaw("TRUNCATE TABLE [YourEntity]");

Taming the beast: Deleting all rows

Entity Framework mandates different strategies based on the data volume and contextual needs. To annihilate all rows, here are your main weapons:

Death by one thousand cuts: `RemoveRange`

The RemoveRange method, when used with SaveChanges or SaveChangesAsync, can be an efficient way to delete data in smaller tables:

context.YourEntities.RemoveRange(context.YourEntities);
await context.SaveChangesAsync();
// Now this is how you wipe out a small table!

Lightning bolt: `ExecuteDeleteAsync`

EF Core 7+ supercharges deletion with ExecuteDeleteAsync, executing a direct SQL DELETE without the need to load entities:

await context.Set<YourEntity>().ExecuteDeleteAsync(x => true);
// EF7 users: Who needs entities after all!?

Diving into the deep end: Large scale data handling and constraints

When handling large datasets, the TRUNCATE TABLE command makes mincemeat of myriads of rows. But wait, let's not invoke wrath of the foreign key constraints:

Good ol' SQL Server: `TRUNCATE` with identity reset

For SQL Server, TRUNCATE resets identity columns, paving the way for shiny new data:

context.Database.ExecuteSqlRaw("DBCC CHECKIDENT('[YourEntity]', RESEED, 0)");
// It's like a new table, just at the same address!

MySQL, we got your back: Disabling foreign key checks

With MySQL, disable foreign key checks before truncating, unless you enjoy random mishaps:

await context.Database.ExecuteSqlRawAsync("SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE YourEntity; SET FOREIGN_KEY_CHECKS=1;");
// Relax MySQL, we know what we're doing...I hope 🤞

The magic wand: Extension methods

Writing your own extension methods can isolate truncate-related logic, keeping your main codebase as clean as a cat in a meme:

public static async Task ClearTable<T>(this DbContext context) where T : class
{
    var tableName = context.Model.FindEntityType(typeof(T)).GetTableName();
    await context.Database.ExecuteSqlRawAsync($"TRUNCATE TABLE {tableName}");
    // Presto! The table vanished... but not really!
}

Call this handy-dandy method with:

await context.ClearTable<YourEntity>();
// Table, meet our magic wand!

Note to SQL adventurers

When venturing with raw SQL execution, always double-check you're communicating with the right table, unless you fancy thrill of unexpected data loss.
Use atomic transactions for data safety, with, you've guessed it right, using blocks.
Post-deletion, some tables may need identity resets. It’s no MMORPG, but it's still an important quest.