Bulk Update and Delete as easy as LINQ

One of the core features of most Object Relational Mapping frameworks is the ability to work with each entity in a single data access context and once a number of changes is accumulated, to generate the appropriate SQL statement for each change and post all statements to the database. This is quite useful for most scenarios, as the typical data intensive application performs CRUD operations mostly based on user interaction, which means that the volume of the changes is usually not very large. For instance:



On the other hand, consider a scenario where a maintenance task, a deployment script or just some complex business logic requires changes to be applied not on one, two or three entities, but on ten thousand. In a typical relational database, that shouldn't be an issue. You would just write and execute a simple SQL script:

While you can always do that using ADO.NET API, or the OpenAccess ADO API in your Data Layer, it is both error prone and backend-dependent. Not only that you are never sure if you are not compromising the referential integrity of your database, but if you change your database type from MS SQL Server to Oracle, you will probably have to update all the statements in your data access logic. An ORM framework is supposed to handle all that for you, but can it do it on the server side, without having to load in memory 10 000 rows of obsolete data?

This is exactly a situation that the Q2 2013 release of OpenAccess ORM is capable of handling for you! Not only that you are able to generate such bulk updates and deletes, but they are made available to you through a convenient, LINQ-like API. It allows you to define a LINQ query as if you are retrieving data, but based on its selection to perform updates or deletes. Here is a simple example of how you can achieve this particular deletion using the OpenAccess Context in a single line of code:

On the other hand, a similar API is available for update operations as well. Consider the situation, where deleting the orders requires setting an IsDeleted flag instead of physically removing them:

Here an additional object is used by the API to help you define the values for each update - the UpdateDescription<T> with a method Set, taking as parameters a member selector and value selector expressions.

What happens behind the scenes? In order to use both the LINQ statement for the query and the update / delete definition, a temporary table is created first in the database with all the IDs that are selected. Then, OpenAccess uses it for the actual statement execution, in order to filter the records with an inner join. As a result, the bulk operations are both incredibly faster and with a minimal memory consumption in comparison with the one-by-one execution still offered by many frameworks as the only choice.

Note that those operations are running outside of the usual transaction handling of OpenAccess so they do not require a SaveChanges() call in order to be applied - they are executed instantaneously! So be careful with the delete statements - powerful weapons should be handled with caution.

Finally, we have chosen this feature for Q2 2013 with an important input from our Ideas & Feedback portal and previous requests. Do not forget to provide your feedback on this or any other feature of the product - just post your ideas for improvements in the portal, so that the entire community can express interest in them!