Bulk Updates and Deletes

The normal cycle for working with an existing database row begins by loading it as an entity. You then modify or remove the entity, and save the changes to the database. This results in an UPDATE or DELETE statement for each affected row.

In some bulk operations, the same change is being applied to all affected rows, and no entity operations are required. In this case, loading each row as an entity, then sending a statement per row, is not efficient. It would be more efficient to send a single UPDATE or DELETE with an expression indicating which rows to apply to it.

This can be done using the IUnitOfWork bulk Update and Remove methods.

Bulk Updates

IUnitOfWork.Update takes a query specifying which rows to update, and an object or dictionary specifying the values to update those rows with. If you pass an object, then LightSpeed uses the names and values of its properties to compose the update. If you pass a dictionary, then LightSpeed uses the string keys of the dictionary and their associated values.

The following example uses an anonymous type to update the IsBadPaymentRisk column.

Performing a bulk update using a change object

DateTime overdueDate = DateTime.Now.AddMonths(-6);
Query overdueAccounts = new Query(typeof(Customer),
  Entity.Attribute("Balance") < 0 && Entity.Attribute("LastPaymentDate") < overdueDate);
unitOfWork.Update(overdueAccounts, new { IsBadPaymentRisk = true });
unitOfWork.SaveChanges();

The following example uses a dictionary to perform the same update.

Performing a bulk update using a change dictionary

DateTime overdueDate = DateTime.Now.AddMonths(-6);
Query overdueAccounts = new Query(typeof(Customer),
  Entity.Attribute("Balance") < 0 && Entity.Attribute("LastPaymentDate") < overdueDate);
Dictionary<string, object> changes = new Dictionary<string, object>();
changes.Add("IsBadPaymentRisk", true);
unitOfWork.Update(overdueAccounts, changes);
unitOfWork.SaveChanges();

Note that like entity operations, bulk operations are not applied to the database immediately. You must still call SaveChanges. This allows bulk updates to be carried out atomically and to be coordinated with entity operations.

Bulk Deletes

IUnitOfWork.Remove takes a query specifying which rows to delete.

Performing a bulk delete

DateTime expiryDate = DateTime.Now.AddMonths(-6);
Query expiredUsers = new Query(typeof(User),
  Entity.Attribute("LastActiveDate") < expiryDate);
unitOfWork.Remove(expiredUsers);
unitOfWork.SaveChanges();

As noted under Bulk Updates, you must remember to call SaveChanges to commit the bulk delete.

Considerations for Bulk Operations

Bulk operations bypass the identity map. After performing a bulk operation, dispose the unit of work, or at least reset it (by passing true to SaveChanges).

Bulk operations bypass the cache, and do not update the full text search index. Avoid using bulk updates if your application uses either of these features.