Resources
Data Access has been discontinued. Please refer to this page for more information.

Performing Bulk Update

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 update operation allows you to update data, based on filters expressed as LINQ queries, without loading it in memory. It is supported through the UpdateAll() 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 introduce unexpected updates by comparing the number of the rows to be updated and the number of the rows actually updated. 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 UpdateAll(), InvalidOperationException will be thrown and the transaction will be rolled back.

You can perform UpdateAll() over both persistent and artificial types and can update the values of all properties from certain type for all of its objects. The reference navigation properties can be set to one of the following: an existing entity instance, a new entity instance that is managed by an instance of the OpenAccessContext class or NULL. Their underlying foreign keys can be set to any admissible value. The collection navigation properties, if the types have such, cannot be set through UpdateAll().

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

In order to perform a bulk update operation, you can use the following workflow:

In order to use the UpdateAll() 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 update operation
  2. Perform the operation
using Telerik.OpenAccess;
using (EntitiesModel context = new EntitiesModel())
{
    string tooOldName = "Too old";
    Category tooOldCategory = context.GetAll<Category>.FirstOrDefault(c => c.CategoryName == tooOldName);
    if (tooOldCategory == null)
    {
        tooOldCategory = new Category();
        tooOldCategory.CategoryName = tooOldName;
        context.Add(tooOldCategory);
        context.SaveChanges();
    }
    //Define a query that is the source of the bulk operation. 
    //All matching elements will be affected by UpdateAll
    IQueryable<Car> query = context.GetAll<Car>().Where(c => c.CarYear < 1990);
    //Perform bulk update
    int updated = query.UpdateAll(u => u.Set(c => c.Category, c => tooOldCategory)
                                        .Set(c => c.Available, c => false)
                                        .Set(c => c.TagNumber, c => c.TagNumber + " Old Reg")
                                 ); 
    Console.WriteLine("Updated cars: {0}", updated);
}

Option Strict On            
Imports Telerik.OpenAccess
Using context = New EntitiesModel()
    Dim tooOldName As String = "Too old"
    Dim tooOldCategory As Category = context.Categories.FirstOrDefault(Function(c) c.CategoryName = tooOldName)
    If tooOldCategory Is Nothing Then
        tooOldCategory = New Category()
        tooOldCategory.CategoryName = tooOldName
        context.Add(tooOldCategory)
        context.SaveChanges()
    End If
    ' Define a query that is the source of the operation. 
    ' All matching elements will be affected by the UpdateAll operation
    Dim query As IQueryable(Of Car) = context.GetAll(Of Car)() _
        .Where(Function(c) c.CarYear.HasValue And c.CarYear.Value < 1990)
    'Perform bulk update
    Dim updated As Integer = query.UpdateAll(Function(u) _
        u.Set(Function(c) c.Category, Function(c) tooOldCategory) _
         .Set(Function(c) c.Available, Function(c) False) _
         .Set(Function(c) c.TagNumber, Function(c) c.TagNumber + " Old Reg"))
    Console.WriteLine("Updated cars: {0}", updated)
End Using

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

In VB, the UpdateAll() method call requires Option Strict = ON either for the whole project or for the file that calls the method.

The first parameter of the lambda expression in Set holds the old value and the second one is the new value of the property. The number of the Set calls depends on the number of the object's properties that you want to update. The result from this query would be that the tag numbers of all cars produced before 1990 will be appended with "Old Reg". If the update fails at some point, the method will throw an exception and any changes that might have been done during the transaction would be rolled back.

A difference between the expected updates and the actual updates is often caused by an update trigger for the updated table. In such scenarios, if you want to continue utilizing the trigger and be able to use UpdateAll(), 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 UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Trigger body
END

Bulk Update Over Batches Of Data

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

int updated;
do
{
    updated = query.OrderBy(c => c.Rating).Take(3)
                 .UpdateAll(u => u.Set(c => c.Category,
                                            c => tooOldCategory)
                                  .Set(c => c.Available, c => false)
                                  .Set(c => c.TagNumber, c => c.TagNumber + " Old Reg"));
}
while(updated > 0);

Dim updated As Integer
Do
    updated = query.OrderBy(Function(c) c.Rating).Take(3)_
                 .UpdateAll(Function(u) _
                     u.Set(Function(c) c.Category, _
                            Function(c) tooOldCategory) _
                     .Set(Function(c) c.Available, Function(c) False) _
                     .Set(Function(c) c.TagNumber, Function(c) c.TagNumber + " Old Reg"))
Loop While deleted > 0

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

Setting Command Timeout

The command timeout for a bulk update 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> query = context.GetAll<Car>().Where(c => c.CarYear < 1990)
                                                 .WithOption(new QueryOptions()
                                                 {
                                                     CommandTimeout = 100 //in seconds
                                                 });
    int updated = query.UpdateAll(u => u.Set(c => c.Category,
                                                  c => tooOldCategory)
                                         .Set(c => c.Available, c => false)
                                         .Set(c => c.TagNumber, c => c.TagNumber + " Old Reg")
                                         );
}

Using dbContext As New EntitiesModel()
    Dim query As IQueryable(Of Car) = context.GetAll(Of Car)() _
        .Where(Function(c) c.CarYear.HasValue And c.CarYear.Value < 1990).
            WithOption(New QueryOptions() With
            {
              .CommandTimeout = 100 'in seconds
            })
    Dim updated As Integer = query.UpdateAll(Function(u) _
        u.Set(Function(c) c.Category, Function(c) tooOldCategory) _
         .Set(Function(c) c.Available, Function(c) False) _
         .Set(Function(c) c.TagNumber, Function(c) c.TagNumber + " Old Reg"))
End Using