LINQ experience (9) -- Insert, Update, and Delete operations in a LINQ to SQL statement

We will continue to explain the LINQ to SQL statements. In this article, we will discuss the Insert, Update, and Delete operations. This is the most common in our program. Let's look at the example.

Insert/Update/Delete operation Insert (Insert) 1. Simple Form

Note: A new object is added to the corresponding set using the InsertOnSubmit method and submitted to the database using SubmitChanges.

NorthwindDataContext db = new NorthwindDataContext();var newCustomer = new Customer{    CustomerID = "MCSFT",    CompanyName = "Microsoft",    ContactName = "John Doe",    ContactTitle = "Sales Manager",    Address = "1 Microsoft Way",    City = "Redmond",    Region = "WA",    PostalCode = "98052",    Country = "USA",    Phone = "(425) 555-1234",    Fax = null};db.Customers.InsertOnSubmit(newCustomer);db.SubmitChanges();

Statement Description: Use the InsertOnSubmit method to add a new customer to the MERs table object. Call SubmitChanges to save the new Customer to the database.

2. One-to-multiple relationship

Note: The relationship between Category and Product is one-to-multiple. When data of one end of Category is submitted, LINQ to SQL will automatically submit data of Product (Multi-terminal) together.

var newCategory = new Category{    CategoryName = "Widgets",    Description = "Widgets are the ……"};var newProduct = new Product{    ProductName = "Blue Widget",    UnitPrice = 34.56M,    Category = newCategory};db.Categories.InsertOnSubmit(newCategory);db.SubmitChanges();

Statement Description: Use the InsertOnSubmit method to add the new Category to the Categories table, and add the new Product object to the Products table that has a foreign key relationship with the new Category. Call SubmitChanges to save these new objects and their relationships to the database.

3. many-to-many relationships

Note: In the many-to-many relationship, we need to submit them in sequence.

var newEmployee = new Employee{    FirstName = "Kira",    LastName = "Smith"};var newTerritory = new Territory{    TerritoryID = "12345",    TerritoryDescription = "Anytown",    Region = db.Regions.First()};var newEmployeeTerritory = new EmployeeTerritory{    Employee = newEmployee,    Territory = newTerritory};db.Employees.InsertOnSubmit(newEmployee);db.Territories.InsertOnSubmit(newTerritory);db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);db.SubmitChanges();

Statement Description: Use the InsertOnSubmit method to add a new employee to the Employees table and add the new Territory to the Territories table, add the new EmployeeTerritory object to the EmployeeTerritories table that has a foreign key relationship with the new Employee object and the new Territory object. Call SubmitChanges to maintain these new objects and their relationships to the database.

4. Override using Dynamic CUD)

Note: CUD is the abbreviation of Create, Update, and Delete. The following example creates a Region with the ID (primary key) of 32, regardless of whether there is data with the ID of 32 in the database. If yes, the original data is replaced, and no data is inserted.

Region nwRegion = new Region(){    RegionID = 32,    RegionDescription = "Rainy"};db.Regions.InsertOnSubmit(nwRegion);db.SubmitChanges();

Statement Description: Use the division method InsertRegion provided by DataContext to insert a region. The call To SubmitChanges calls InsertRegion To rewrite, which uses the dynamic CUD To run the default SQL query generated by Linq To SQL.

Update)

Note: To update an object, obtain the object and modify the object. Then, call the SubmitChanges () method to submit the object. Note: This is in the same DataContext. For different DataContex, see the following description.

1. Simple Form

Customer cust =    db.Customers.First(c => c.CustomerID == "ALFKI");cust.ContactTitle = "Vice President";db.SubmitChanges();

Statement Description: Use SubmitChanges to keep updates made to a retrieved Customer object back to the database.

2. Multiple changes

var q = from p in db.Products        where p.CategoryID == 1        select p;foreach (var p in q){    p.UnitPrice += 1.00M;}db.SubmitChanges();

Statement Description: Use SubmitChanges to keep the retrieved updates back to the database.

Delete (Delete) 1. Simple Form

Note: Call the DeleteOnSubmit method.

OrderDetail orderDetail =    db.OrderDetails.First    (c => c.OrderID == 10255 && c.ProductID == 36);db.OrderDetails.DeleteOnSubmit(orderDetail);db.SubmitChanges();

Statement Description: Use the DeleteOnSubmit method to delete the OrderDetail object from the OrderDetail table. Call SubmitChanges to save the deletion to the database.

2. One-to-multiple relationship

Note: Order and OrderDetail are one-to-multiple relationships. First, DeleteOnSubmit its OrderDetail (Multi-terminal), and then DeleteOnSubmit its Order (one end ). Because one end is the primary key.

var orderDetails =    from o in db.OrderDetails    where o.Order.CustomerID == "WARTH" &&    o.Order.EmployeeID == 3    select o;var order =    (from o in db.Orders     where o.CustomerID == "WARTH" && o.EmployeeID == 3     select o).First();foreach (OrderDetail od in orderDetails){    db.OrderDetails.DeleteOnSubmit(od);}db.Orders.DeleteOnSubmit(order);db.SubmitChanges();

Statement description statement Description: Use the DeleteOnSubmit method to delete the Order and Order Detail objects from the Order and Order Details tables. First, delete Order Details and then Orders. Call SubmitChanges to save the deletion to the database.

3. Delete inference (Inferred Delete)

Note: Order and OrderDetail are in a one-to-multiple relationship. In the preceding example, we delete all the data with CustomerID being WARTH and EmployeeID being 3. So we do not need to delete all the data? For example, there are many OrderDetail with Order ID 10248, but we only need to delete OrderDetail with ProductID 11. Then the Remove method is used.

Order order = db.Orders.First(x => x.OrderID == 10248);OrderDetail od =     order.OrderDetails.First(d => d.ProductID == 11);order.OrderDetails.Remove(od);db.SubmitChanges();

Statement description statement Description: This example shows how to cause the actual delete operation on the object when the referenced object of the object removes the object from its EntitySet. Only when DeleteOnNull is set to true and CanBeNull is set to false for the association ing of the object will there be a logic deletion behavior.

Update with Attach)

Note: Use the Attach method to update data between different DataContext. For example, in a NorthwindDataContext named tempdb, the Customer and Order are queried. In another NorthwindDataContext, the Customer address is updated to 123 First Ave, and the Order CustomerID is updated to CHOPS.

// Normally, get the object to be appended by deserializing XML from other layers // It does not support appending an object from one DataContext to another DataContext // so to copy the deserialization object operation, these entities will be re-created here: Customer c1; List <Order> deserializedOrders = new List <Order> (); Customer deserializedC1; using (NorthwindDataContext tempdb = new NorthwindDataContext ()) {c1 = tempdb. MERs. single (c => c. customerID = "ALFKI"); region = new Customer {Address = c1.Address, City = c1.City, CompanyName = c1.CompanyName, ContactName = c1.ContactName, ContactTitle = c1.ContactTitle, Country = c1.Country, customerID = c1.CustomerID, Fax = c1.Fax, Phone = c1.Phone, PostalCode = c1.PostalCode, Region = c1.Region}; Customer tempcust = tempdb. MERs. single (c => c. customerID = "ANTON"); foreach (Order o in tempcust. orders) {deserializedOrders. add (new Order {CustomerID = o. customerID, EmployeeID = o. employeeID, Freight = o. freight, OrderDate = o. orderDate, OrderID = o. orderID, RequiredDate = o. requiredDate, ShipAddress = o. shipAddress, ShipCity = o. shipCity, ShipName = o. shipName, ShipCountry = o. shipCountry, ShippedDate = o. shippedDate, ShipPostalCode = o. shipPostalCode, ShipRegion = o. shipRegion, ShipVia = o. shipVia}) ;}} using (NorthwindDataContext db2 = new NorthwindDataContext () {// attaches the first entity to the current data context to track changes // updates to the Customer, db2.Customers cannot be written incorrectly. attach (deserializedC1); // change the tracked object deserializedC1.Address = "123 First Ave"; // Add all entities in the order list db2.Orders. attachAll (deserializedOrders); // update the Order to another customer foreach (Order o in deserializedOrders) {o. customerID = "CHOPS";} // submit the change db2.SubmitChanges () in the current data context ();}

Statement Description: obtains an object from another layer. Attach and AttachAll are used to append the deserialized object to the data context and update the object. The change is submitted to the database.

Update and Delete with Attach)

Description: inserts, updates, and deletes data in different DataContext. Let's take a look at the following example:

// Normally, get the object to be attached by deserializing XML from other layers // This example uses LoadWith to pre-load the customer and order in a query, // disable delayed loading. Customer cust = null; using (NorthwindDataContext tempdb = new NorthwindDataContext () {DataLoadOptions shape = new DataLoadOptions (); shape. loadWith <Customer> (c => c. orders); // load the first customer entity and its order tempdb. loadOptions = shape; tempdb. deferredLoadingEnabled = false; cust = tempdb. MERs. first (x => x. customerID = "ALFKI");} Order orderA = cust. orders. first (); Order orderB = cust. orders. first (x => x. orderID> orderA. orderID); using (NorthwindDataContext db2 = new NorthwindDataContext () {// attaches the first entity to the current data context to track changes to db2.Customers. attach (cust); // Add relevant orders for tracking; otherwise, they will be inserted at the time of submission db2.Orders. attachAll (cust. orders. toList (); // update the customer's Phone. cust. phone = "2345 5436"; // update the ShipCity of the first OrderA. orderA. shipCity = "Redmond"; // remove OrderB from the second order. cust. orders. remove (orderB); // Add a new Order to the Customer's Customer. order orderC = new Order () {ShipCity = "New York"}; cust. orders. add (orderC); // submit and execute db2.SubmitChanges ();}

Statement Description: Extract an object from a context, append the object from another context with Attach and AttachAll, update the two objects, delete one object, and add another object. The change is submitted to the database.

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.