Overview

Telerik Data Access supports the Bulk Delete and Bulk Update operations. With them you can change large amount of data on the database server side without loading them on the client's side. That would reduce both the number of client-server calls and the usage of the client's resources. This section will provide you with the following details:

1. General Information
   • Bulk Operations Modes
   • Return Values
   • Source Query Constraints
   • Transaction Boundries
   • Level 2 Caching
   • Artificial Types and Fields
2. Performing Bulk Delete
3. Performing Bulk Update

General Information

Bulk operations in Telerik Data Access are implemented as extension methods to the IQueryable<T> and IQueryable interfaces - the DeleteAll() and UpdateAll() methods.

In order to use the DeleteAll() and UpdateAll() methods you need to add a using / Imports clause to the Telerik.OpenAccess namespace in your code.

DeleteAll() and UpdateAll() verify that they will not introduce unexpected changes in the database. Such may occur due to the respective usage of delete and update triggers. In these scenarios the methods will throw DataStoreException and the transaction will be rolled back. In order to use triggers and be able to execute bulk operations, 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 -- or UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Trigger body
END

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

Bulk Operations Modes

Bulk operations in Telerik Data Access are designed to minimize the resources needed when large batches of data are deleted/updated from the database. They support two distinct modes:

• With temporary table on the server side - this is the default mode where the source query is executed and the result is put in a temporary table on the server. Then one or more SQL statements are executed combining the data from the temporary table. No IDs or additional data is transported to the application. In case this mode is not supported by the backend, Telerik Data Access uses the second one:
• With in-memory list of IDs on the client side - the source query is executed and IDs of the matching rows are transported back to the application. Then one or more SQL statements are executed using these IDs as WHERE clause filters. The update method may transport some additional data when a column is updated with values from the database.

Currently, you cannot manually switch between these two modes, because some backends do not support temporary tables or have issues with them. Below is a table that shows which modes are supported by each backend:

For more information about the differences between the backends is available in the Database Specifics section.

Return Values

The return value of DeleteAll() and UpdateAll() is the number of objects that the source query will produce and not the number of objects that have their values changed. Some scenarios that include Inheritance or Multi-Table Entities will produce several delete/update statements in order to manipulate a single object, but all these operations will be counted as a single one when reporting the result.

Source Query Constraints

Although, both UpdateAll() and DeleteAll() are implemented as extension methods to IQueryable<T> and IQueryable, not all LINQ queries are valid source queries. Below is a list of LINQ operations that are considered invalid in this context:

• Skip()
• GroupBy()
• Select() - with exception of the trivial projection (query.Select(i=> i))
• SelectMany()
• Join()
• GroupJoin()
• Union() and other set operators

Any source query that cannot be 100% pushed to the database server side is considered not valid and InvalidOperationException will be thrown.

Transaction boundries

DeleteAll() and UpdateAll() are designed to be executed in transactions independent from those managed by OpenAccessContext. Additionally, every single bulk operation is executed in its own transaction and if the operation fails, it is rolled back.

Level 2 Caching

Executing a bulk operation will invalidate portions of Level 2 Cache of Telerik Data Access. When using server side mode all instances of the root source query type and all its inheritors are evicted. When using client side mode generally the eviction will happen by IDs rather than types, unless the number of affected items is huge. When manipulating big amount of objects even in client side mode the eviction will be by type and again will include the root type and all its inheritors.

Performing a bulk operation can invalidate large portions of your Level 2 Cache and this way affect negatively the performance of your application.

Artificial Types and Fields

Queries targeting artificial types or types extended with artificial fields can be used as source queries for the UpdateAll() and DeleteAll() operations. The static SymbolicFieldName class allows developers to target internal fields like:

• Discriminator column – the column that describes the type of an entity that is part of an inheritance hierarchy
• Version/Timestamp column – the column that holds concurrency control information for the version or the timestamp of the last update
• Internal Identity – the column that represents the automatic identity for an entity when the client did not supply one during creation of the entity mapping.

For more information about bulk operations with artificial types, check here:

1. How to: Bulk Delete Artificial Types
2. How to: Bulk Update Artificial Types