Entity Framework Extensions Delete from Query

Definition

DELETE all rows from the database using a LINQ Query without loading entities in the context.

A DELETE statement is built using the LINQ expression and directly executed in the database.

// DELETE all customers that are inactive
context.Customers.Where(x => !x.IsActive).DeleteFromQuery();

// DELETE customers by id
context.Customers.Where(x => x.ID == userId).DeleteFromQuery();

Try it in EF Core | Try it in EF6

Purpose

Deleting entities using SaveChanges normally requires loading them first in the ChangeTracker. These additional round-trips are often not necessary.

DeleteFromQuery gives you access to directly execute a DELETE statement in the database and provide a HUGE performance improvement.

Performance Comparisons

Operations 1,000 Entities 2,000 Entities 5,000 Entities
SaveChanges 1,000 ms 2,000 ms 5,000 ms
DeleteFromQuery 1 ms 1 ms 1 ms
## FAQ

Why DeleteFromQuery is faster than SaveChanges, BulkSaveChanges, and BulkDelete?

DeleteFromQuery executes a statement directly in SQL such as DELETE FROM [TableName] WHERE [Key].

Other operations normally require one or multiple database round-trips which makes the performance slower.

Limitation

EF Core & Client Evaluation

DeleteFromQuery 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.


Last updated: 2023-03-01
Author:


Contents