The common scenarios of working with database-persisted data include retrieving these data, changing them and then saving the changes back to the database. The first part of these is described in the Querying Data section, the others are discussed here.

The main purpose of any ORM is to make working with relational data as similar to working with objects as possible. Applied to data modification, this thesis means, in particular, that a developer does not want to keep the information about each small change that was done, as well as to invoke a separate method to persist this change.

Another important thing about persisting the modifications is that it should be transactional for most of the common scenarios. Indeed, usually a single unit of work includes data changes that, being performed via plain SQL, need more than a single insert/update/delete operation, and all of these operations should either succeed or fail together.

Thus, it is convenient if the ORM solution tracks all data changes done in the application, and saves all of them in a transactional way at a single moment specified by the developer. The same as for querying, this is done via the DataContext class in LinqConnect. The instances of this class hold the state of all entity objects that pass through it, thus having the information about the insert, update, and delete operations that should be performed when persisting the changes. And all these operations are executed when the SubmitChanges method of DataContext is invoked. For the case that an error occurs when saving the changes, the DataContext class provides the methods that allow resolving the most common errors.

Let us demonstrate how create/update/delete (CUD) operations work in LinqConnect with a short sample:

C#csharp

Copy Code

CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; Productcategory tragedy = new Productcategory() { CategoryID = 20, CategoryName = "tragedy" }; Productcategory comedy = new Productcategory() { CategoryID = 21, CategoryName = "comedy" }; // Here the context is informed that a new ProductCategory // object should be persisted in the database: context.Productcategories.InsertOnSubmit(tragedy); context.Productcategories.InsertOnSubmit(comedy); Product ShakespearesWorks = context.Products .Where(p => p.ProductID == 7007).Single(); // Instruct the context that the 'drama' category should be deleted: context.Productcategories.DeleteOnSubmit(ShakespearesWorks.Productcategory); // The context tracks changes in entity properties. // Thus, it is sufficient to update an object for the corresponding row to be updated. ShakespearesWorks.ProductName = "Shakespeare W. Shakespeare's dramatic works."; // When changing a reference to another entity, the foreign key column is updated. ShakespearesWorks.Productcategory = tragedy; // Until now, no CUD commands were executed. // Tell the context that all needed changes are done and may be sent to the server. context.SubmitChanges();

Visual Basic

Copy Code

Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim tragedy As New Productcategory() With { _ .CategoryID = 20, _ .CategoryName = "tragedy" _ } Dim comedy As New Productcategory() With { _ .CategoryID = 21, _ .CategoryName = "comedy" _ } ' Here the context is informed that a new ProductCategory ' object should be persisted in the database: context.Productcategories.InsertOnSubmit(tragedy) context.Productcategories.InsertOnSubmit(comedy) Dim ShakespearesWorks As Product = context.Products.Where( _ Function(p) p.ProductID = 7007).[Single]() ' Instruct the context that the 'drama' category should be deleted: context.Productcategories.DeleteOnSubmit(ShakespearesWorks.Productcategory) ' The context tracks changes in entity properties. ' Thus, it is sufficient to update an object for the corresponding row to be updated. ShakespearesWorks.ProductName = "Shakespeare W. Shakespeare's dramatic works." ' When changing a reference to another entity, the foreign key column is updated. ShakespearesWorks.Productcategory = tragedy ' Until now, no CUD commands were executed. ' Tell the context that all needed changes are done and may be sent to the server. context.SubmitChanges()

The sample is based on the CRM Demo database. The idea is that for some reason the store decided to split the 'drama' product category into 'comedy' and 'tragedy'. The only product in the former 'drama' category should be then moved to either of new ones, and 'drama' therefore may be deleted.

The actions needed to perform this unit of work are:

1. First, we create a DataContext instance. As it was said, all data querying and modifying is performed via DataContext. With the Log property, we enable monitoring of database interoperations performed with this context, so that you are able to check the generated SQL commands.
2. We insert the new categories via the InsertOnSubmit method of the Table class. This operation is deferred in the sense that the actual insert into the database won't occur till the SubmitChanges invocation.
3. We get the only product of the 'drama' category and delete the said category. Similarly, a method (DeleteOnSubmit) of the Table class is used, and it is deferred until calling SubmitChanges as well.
4. The category and name of the retrieved product is changed. The context 'keeps an eye' on all entities attached to it, so it is sufficient to modify an entity property to this change being persisted during SubmitChanges (and once more, no update is done till that). When a 'plain' property is modified, the corresponding column is updated in the database; when changing a navigation property, the context updates the foreign key this property is based on.
5. Finally, call the SubmitChanges method. At this point, all the changes are persisted in a single transaction.

The SQL generated for the SubmitChanges call in this sample (in the Oracle dialect):

Notes on the generated SQL:

• The context saves entities in the proper order: though we marked the category for deletion before updating its single product, the context executed the update first so that the delete does not violate the foreign key constraint.
• The way new categories are persisted: since saving either of them needs no feedback from the server, the context sends them in a batch, effectively using a single SQL command instead of a couple.
• The WHERE statements of the update and delete commands: though it is sufficient to check for the primary key value only, LinqConnect validates other fields as well. These additional verifications, or update checks, are an effective mechanism for detecting concurrency conflicts that may anytime occur in a multi-user database.

All of these steps are described in details in the next topics:

Persisting New Entities

Describes persisting of newly created entities.

Deleting Entities

Describes how to delete persisted entities.

Updating Entities

Describes how to update persisted entities.

Saving Modified Data

Describes how LinqConnect saves modified data and process errors.