Entity Framework Core Plus Batch Delete

Description

Deleting using Entity Framework Core can be very slow if you need to delete hundreds or thousands of entities. Entities are first loaded in the context before being deleted which is very bad for the performance and then, they are deleted one by one which makes the delete operation even worse.

EF+ Batch Delete deletes multiple rows in a single database roundtrip and without loading entities in the context.

// using Z.EntityFramework.Plus; // Don't forget to include this.

// DELETE all users inactive for 2 years
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete();

// DELETE using a BatchSize
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete(x => x.BatchSize = 1000);

Try it

Scenarios

• Query Criteria
• Batch Size
• Batch Delay Interval
• Executing Interceptor
• EF Core InMemory

Query Criteria

The Delete IQueryable extension methods deletes rows matching the query criteria without loading entities in the context.

// using Z.EntityFramework.Plus; // Don't forget to include this.

// DELETE all users
ctx.Users.Delete();

// DELETE all users inactive for 2 years
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete();

Try it

Try it (Async version)

Batch Size

The BatchSize property sets the amount of rows to delete in a single batch.

Default Value = 4000

// using Z.EntityFramework.Plus; // Don't forget to include this.
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete(x => x.BatchSize = 1000);

Try it

Batch Delay Interval

The BatchDelayInterval property sets the amount of time (in milliseconds) to wait before starting the next delete batch.

Default Value = 0

// using Z.EntityFramework.Plus; // Don't forget to include this.

// Pause 2 seconds between every batch
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete(x => x.BatchDelayInterval = 2000);

Try it

Executing Interceptor

The Executing property intercepts the DbCommand with an action before being executed.

// using Z.EntityFramework.Plus; // Don't forget to include this.

string commandText
var date = DateTime.Now.AddYears(-2);
ctx.Users.Where(x => x.LastLoginDate < date)
         .Delete(x => { x.Executing = command => commandText = command.CommandText; });

Try it

EF Core InMemory

Specify a DbContext factory in the BatchDeleteManager to enable BatchDelete with In Memory. A DbContext factory must be

// Options
var db = new DbContextOptionsBuilder();
db.UseInMemoryDatabase();

// Specify InMemory DbContext Factory
BatchDeleteManager.InMemoryDbContextFactory = () => new MyContext(db.Options);

// Use the same code as with Relational Database
var _context = new MyContext(db.Options);
_context.Foos.Delete();

EF Core & Client Evaluation

Do not use this feature if Client Evaluation is enabled

Batch Delete use the SQL generated by EF Core. When a filter is made on client-side, it means the filtering happens in the application and not in the SQL executed.

In other words, even if you put a filter, all rows tables could be potentially deleted if the filter is made on the client-side.

We always recommend to disable the client evaluation to avoid performance issue in your application.

Conclusion

EF+ Batch Delete is the most efficient way to delete records. You drastically improve your application performance by removing the need to retrieve and load entities in your context and by performing a single database roundtrip instead of one for every record.

Need help getting started? info@zzzprojects.com

We welcome all comments, ideas and suggestions to improve our library.