Deleting data with Dapper is already very fast, as you can use the IN statement and specify a list of keys. However, in some scenarios, you might have thousands of data to delete and be limited by the number of parameters supported by SQL Server or want more flexibility.
The fastest and easiest way of deleting multiple data is using the Dapper Plus third-party library (Online Benchmark).
This library is very fast and offers hundreds of features and all other bulk operations to support the most common and complex cases.
Getting Started
NuGet Package: https://www.nuget.org/packages/Z.Dapper.Plus/
Documentation: Dapper Plus – Bulk Delete
Dapper BulkDelete
By default, you only need to pass your entities in parameter, and the library will find the primary key from your database if none has been mapped:
- Dapper Plus
BulkDeletealso supports soft deletes, allowing you to mark deleted entities without actually deleting them from the database. - It makes it easier to undo mistakes while maintaining a tidy database.
- Additionally, it supports conditional deletes, allowing you to delete entities based on specific criteria that you specify.
// Easy to use
connection.BulkDelete(products);
Dapper BulkDelete with Global Context Mapping
In addition to being fast, the library is very flexible and allows many scenarios. For example, you have a particular scenario that you can only delete a row if, in addition to the key, the version of the Customer also matches the concurrency column value:
// Global Mapping requires to be mapped globally only once
DapperPlusManager.Entity<Customer>("Customer_VersionEqual")
.UseBulkOptions(x => x.DeleteMatchedAndConditionExpression = y => y.Version);
// ...code...
connection.BulkDelete("Customer_VersionEqual", customers);
Dapper BulkDelete with Instance Context Mapping
By using an instance context mapping, you can add more flexibility by being able to specify some options at runtime:
public void CustomBulkDelete<T>(List<T> entities, bool checkVersion) where T : IVersion
{
var context = new DapperPlusContext();
var mapping = context.Entity<T>();
if(checkVersion)
{
mapping = mapping.UseBulkOptions(x => x.DeleteMatchedAndConditionExpression = y => y.Version);
}
context.BulkDelete(entities);
}
All of this combined makes Dapper Plus BulkDelete an incredibly powerful and useful method for deleting large amounts of data from the database quickly and efficiently.
FAQ Chat