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 | ![]() |
---|---|
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 | ![]() |
---|---|
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:
The SQL generated for the SubmitChanges call in this sample (in the Oracle dialect):
BEGIN
INSERT INTO "Product Categories" ("CategoryID", "CategoryName", "ParentCategory") VALUES (:p1, :p2, :p3);
INSERT INTO "Product Categories" ("CategoryID", "CategoryName", "ParentCategory") VALUES (:p4, :p5, :p6);
END;
UPDATE "Products" SET "ProductName" = :p1, "CategoryID" = :p2 WHERE "ProductID" = :key1 AND "ProductName" = :chk1 AND ((:nullchk2 = 1 AND "CategoryID" IS NULL) OR ("CategoryID" = :chk2)) AND ((:nullchk3 = 1 AND "UnitName" IS NULL) OR ("UnitName" = :chk3)) AND ((:nullchk4 = 1 AND "UnitScale" IS NULL) OR ("UnitScale" = :chk4)) AND ((:nullchk5 = 1 AND "InStock" IS NULL) OR ("InStock" = :chk5)) AND ((:nullchk6 = 1 AND "Price" IS NULL) OR ("Price" = :chk6)) AND ((:nullchk7 = 1 AND "DiscontinuedPrice" IS NULL) OR ("DiscontinuedPrice" = :chk7))
DELETE FROM "Product Categories" WHERE "CategoryID" = :key1 AND ((:nullchk1 = 1 AND "CategoryName" IS NULL) OR ("CategoryName" = :chk1)) AND ((:nullchk2 = 1 AND "ParentCategory" IS NULL) OR ("ParentCategory" = :chk2))
Notes on the generated SQL:
All of these steps are described in details in the next topics:
Describes persisting of newly created entities.
Describes how to delete persisted entities.
Describes how to update persisted entities.
Describes how LinqConnect saves modified data and process errors.