Performing Bulk Delete

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

The bulk delete operation allows you to delete data, based on filters expressed as LINQ queries, without loading it in memory. It is supported through the DeleteAll() extension method (to IQueryable<T> for persistent types and to IQueryable for artificial types) and returns the number of records from the source query that it affects. It also verifies that it will not perform unnecessary deletes by comparing the number of the rows to be deleted and the number of the rows actually deleted. Since the method is executed in a transaction independent from the one managed by OpenAccessContext, if those numbers are different the changes are rolled back and DataStoreException is thrown. Moreover, if the provided source query is not compatible or a database error has been encountered during the execution of DeleteAll(), InvalidOperationException will be thrown and the transaction will be rolled back.

You can perform bulk delete over both persistent and artificial types and Telerik Data Access will take care for data in internal structures defined with Inheritance and Multi-Table Entities: DeleteAll() generates the delete statements in the correct order based on the metadata present in the model and not on the data in the database.

Once committed the operation will invalidate parts of your Level 2 Cache (more information is available here).

In order to perform a bulk delete operation, you can consider the following simple workflow:

To use the DeleteAll() method you need to add a using / Imports clause to the Telerik.OpenAccess namespace in your code.

1. Define a query that will be the source for the delete operation
2. Perform the operation

using Telerik.OpenAccess;
using (EntitiesModel context = new EntitiesModel())
{
    //Define a query that is the source of the operation.
    //All matching elements will be affected by the DeleteAll operation
    IQueryable<Car> query = context.GetAll<Car>().Where(c => c.CarYear < 1990);
    //Perform a bulk delete operation
    int deleted = query.DeleteAll();
    Console.WriteLine("deleted cars: {0}", deleted);
}

Imports Telerik.OpenAccess
Using context = New EntitiesModel()
    ' Define a query that is the source of the operation. 
    ' All matching elements will be affected by the DeleteAll operation
    Dim query As IQueryable(Of Car) = context.GetAll(Of Car)().Where(Function(c) c.CarYear.HasValue And c.CarYear.Value < 1990)
    'Perform a bulk delete operation
    Dim deleted = query.DeleteAll()
    Console.WriteLine("deleted cars: {0}", deleted)
End Using

Due to limitations in VB the extension methods on objects need to be called as static methods.

That operation will attempt to remove all cars produced before 1990 from the SofiaCarRental database. Any violations of the referential integrity in the storage model will be reported to the user with an InvalidOperationException exception and the transcation will be rolled back.

A difference between the expected and the actual rows deleted is often caused by a delete trigger for the source table. In such scenarios, if you want to continue utilizing the trigger and be able to use DeleteAll(), it is recommended to turn off the counting of the affected rows in the trigger:

ALTER TRIGGER [<schema_name>].[<trigger_name>]
ON [<schema_name>].[<table_name>]
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON
  -- Trigger body
END

Consider carefully the benefits from using the DeleteAll() method, because you can easily erase a significant amount of data from your database with it.

Bulk Delete Over Batches Of Data

The following sample demonstrates how to perform bulk delete on batches of data:

int deleted;
do
{
    deleted = query.OrderBy(c => c.Rating).Take(3).DeleteAll();
}
while(deleted > 0);

Dim deleted As Integer
Do
    deleted = query.OrderBy(Function(c) c.Rating).Take(3).DeleteAll()
Loop While deleted > 0

DeleteAll() can use paging using only Take() and OrderBy(), Skip() is not allowed!

Setting Command Timeout

The command timeout for a bulk delete operation can be explicitly set on query level. The provided value is considered during the execution of each command inside the operation. If one of the commands exceeds the timeout a backend specific exception is thrown and the transaction is rolled back.

using (EntitiesModel dbContext = new EntitiesModel())
{
    IQueryable<Car> cars = dbContext.Cars.Where(c => c.Available == true)
                                            .WithOption(new QueryOptions()
                                            {
                                                CommandTimeout = 100 //in seconds
                                            });
    int deleted = cars.DeleteAll();
}

Using dbContext As New EntitiesModel()
    Dim cars As IQueryable(Of Car) = dbContext.Cars.Where(Function(c) c.Available = True).
                                                    WithOption(New QueryOptions() With
                                                    {
                                                        .CommandTimeout = 100 'in seconds
                                                    })
    Dim deleted As Integer = cars.DeleteAll()
End Using