How can I efficiently delete all records in a table using Entity Framework without using SQL?

Question

I need to remove all records in a given table, using a DbContext in Entity Framework. I need this to be portable, so it can't rely on SQL statements or stored procedures. I've looked through the answers to this question, but none of them meet my requirements or are ideal solutions for a variety of reasons.

I could use the RemoveRange method, i.e.

DbContext.Table.RemoveRange(all);

But this doesn't scale well, because it selects all entries before deleting, which could take a long, long time with Entity Framework. Iterating through each record and removing them individually with Remove(record) has the same problem.

Using SQL this is simple, using a TRUNCATE command. Even a simple DELETE FROM [TableName] command works, but I don't know how scalable that is.

Is there any solution that uses only Entity Framework (no SQL), and doesn't require selecting all records first before deleting them?

https://stackoverflow.com/questions/15220411/entity-framework-delete-all-rows-in-table — Mitch Wheat, Jul 14 '20 at 07:14
"so it can't rely on SQL statements " - TRUNCATE TABLE seems safe enough — Mitch Wheat, Jul 14 '20 at 07:15
@MitchWheat TRUNCATE TABLE is an SQL statement. And you'll also note that I linked to that question you've linked to, and read the answers there. As [those answers point out](https://stackoverflow.com/questions/15220411/entity-framework-delete-all-rows-in-table#comment84222294_15220411), "if an answer quotes table names in SQL statements with square brackets (like: [TableName]), it isn't portable." — aforest-ccc, Jul 14 '20 at 07:17
I know it's a SQL Statement! lol I'm saying show me a a RDBMS that doesn't support TRUNCATE TABLE tablename; — Mitch Wheat, Jul 14 '20 at 07:18
You need to extend the DbContext to provide more efficient method of Bulk deleting, there's no built in alternative that will work for you. There's also a commercial option: https://entityframework-extensions.net/bulk-delete, where it takes 75ms to do BulkDelete vs 6s from SaveChanges. You can obviously write it yourself as an extension. — Tomasz, Jul 14 '20 at 07:19
@Tomasz Can you suggest a way of writing such a DbContext extension? I don't know how the commercial option you've linked does it, but I don't want to spend $80+ to find out it just uses an SQL statement behind the scenes. — aforest-ccc, Jul 14 '20 at 07:24
@MitchWheat if you read through the answers to the question both you and I have linked, you'll see that there are a number of issues with using a TRUNCATE command that others have pointed out. Not least of which is it bypasses Entity Framework to execute raw SQL, undermining the whole point of using Entity Framework. — aforest-ccc, Jul 14 '20 at 07:26
'horses for courses' - I don't subscribe to the notion that you have to do things only one way. — Mitch Wheat, Jul 14 '20 at 07:29
@MitchWheat There are also, as noted in those answers, other problems with using a TRUNCATE command, including: it requires ALTER permissions not typically available to an EF app; it runs into errors with foreign key constraints. It's not just a matter of choosing to exclusively use EF, it's also about trying to avoid those and other problems. — aforest-ccc, Jul 14 '20 at 07:33

score 1 · Accepted Answer · answered Jul 14 '20 at 12:37

1

This is currently not something that is possible using Entity Framework. see https://github.com/dotnet/efcore/issues/795

There may be an extension out there that will allow you to do that, but I am not sure it will work will all RDBMS systems.

answered Jul 14 '20 at 12:37

ErikEJ

40,951
5
75
115

Wow, that GitHub thread is a horror show. Six years, and a feature useful for almost all database-based applications is "likely to be a high-priority feature" for the release after next... It does sound like I'll have to look at a third-part extension and see what works scalably for my requirements. – aforest-ccc Jul 14 '20 at 12:48

Ernest Rutherford · Answer 2 · 2020-07-14T13:17:27.307

Let's suppose you have BrandData table with records about some brands:

public class BrandData
    {
        [Key]
        public int Id { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }
    }

Also you've created a dbcontext:

public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
        {
            Database.Migrate();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<BrandData>().HasData(new BrandData { Id = 999, Name = "LG", Description = "TV brand" });
            
        }
        public DbSet<BrandData> BrandData { get; set; }

    }

Finally, this is the method that deletes brand by its ID:

public async Task DeleteModelAsync(int id)
        {
            var data = _dbContext.ModelData.FirstOrDefault(b => b.Id == id);
            if (data != null)
            {
                _dbContext.ModelData.Remove(data);
                await _dbContext.SaveChangesAsync();
            }
        }

Changes will be done after SaveChangesAsync() method run.

UPDATE

To delete all records:

    var brands = await _dbContext.BrandData.ToListAsync();
    
                    foreach(var brand in brands)
                    {
                        _dbContext.BrandData.Remove(brand);
                    }

    await _dbContext.SaveChangesAsync();

This does not delete ALL rows in a table – ErikEJ Jul 14 '20 at 13:09 — ErikEJ, Jul 14 '20 at 13:09

score 0 · Answer 3 · answered Aug 25 '23 at 07:36

This thread merits a working answer. This is how you can do (EF 6)

using (var context = new ApplicationDbContext())
{
    var subs = context.Subscriptions
        .Where(x => x.Client == myClient)
        .ToList();

    context.Subscriptions.RemoveRange(subs);
    await context.SaveChangesAsync();
}

How can I efficiently delete all records in a table using Entity Framework without using SQL?

3 Answers3