LINQ Experience (9)--LINQ to SQL statement insert/update/delete operation

Let's go on to the LINQ to SQL statement, which we'll talk about Insert/update/delete operations. This is most commonly used in our programs. We look directly at examples.

Insert/update/delete operation Insert (INSERT) 1. Simple Form

Description: The new object, which is added to the corresponding collection using the InsertOnSubmit method, is 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 Wa Y ", City    =" Redmond ", Region    =" WA ",    PostalCode =" 98052 ",    country =" USA ",    Phone =" (425) 555-1234 ",    Fax = null};d B. Customers.insertonsubmit (Newcustomer);d B. SubmitChanges ();

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

2. One-to-many relationships

Description: The category and product are a one-to-many relationship, and LINQ to SQL automatically submits the data for the category (one end) together with the product (multi-port).

var newcategory = new category{    CategoryName = "Widgets",    Description = "Widgets is the ..."};var newproduct = new product{    ProductName = "Blue Widget",    UnitPrice = 34.56M,    Category = newcategory};d b. Categories.insertonsubmit (newcategory);d B. SubmitChanges ();

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

3. Many-to-many relationships

Note: In a many-to-many relationship, we need to submit in turn.

var newemployee = new employee{    FirstName = "Kira",    LastName = "Smith"};var newterritory = new territory{    Terr Itoryid = "12345",    territorydescription = "Anytown", Region    = db. Regions.first ()};var newemployeeterritory = new employeeterritory{    Employee = newemployee,    Territory = Newterritory};d B. Employees.insertonsubmit (NewEmployee);d B. Territories.insertonsubmit (newterritory);d B. Employeeterritories.insertonsubmit (newemployeeterritory);d B. 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. and add the new Employeeterritory object to the Employeeterritories table that has a foreign key relationship to this new employee object and the new Territory object. Call SubmitChanges to persist these new objects and their relationships to the database.

4. Using dynamic CUD overrides (override using dynamically CUD)

Description: CUD is the abbreviation for Create, Update, delete. The following example is to create a new ID (primary key) of region 32, regardless of the database has no ID 32 data, if any, replace the original data, not inserted.

Region Nwregion = new Region () {    RegionID = +,    regiondescription = "Rainy"};d B. Regions.insertonsubmit (nwregion);d B. SubmitChanges ();

Statement Description: Inserts an area using the partial method insertregion provided by DataContext. Calls to SubmitChanges call Insertregion overrides, which use dynamic cud to run the default SQL query generated by LINQ to SQL.

Updates (update)

Note: The update operation, first gets the object, after the modification operation, directly calls the SubmitChanges () method to commit. Note that this is in the same DataContext, for the different Datacontex see the following explanation.

1. Simple Form

Customer cust =    db. Customers.first (c = C.customerid = = "ALFKI"); Cust. ContactTitle = "Vice President";d B. SubmitChanges ();

Statement Description: Use SubmitChanges to persist the updates made to the retrieved one 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 persist the retrieved updates back to the database.

Remove (delete) 1. Simple Form

Note: You can call the DeleteOnSubmit method.

OrderDetail OrderDetail =    db. Orderdetails.first    (c = C.orderid = = 10255 && C.productid = =);d B. Orderdetails.deleteonsubmit (OrderDetail);d B. SubmitChanges ();

Statement Description: Use the DeleteOnSubmit method to remove the OrderDetail object from the OrderDetail table. Call SubmitChanges to persist this deletion to the database.

2. One-to-many relationships

Note: Order and OrderDetail are a one-to-many relationship, first deleteonsubmit its orderdetail (multi-port), and second 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);d B. SubmitChanges ();

Statement Description Statement Description: Removes the order and order detail objects from the order and Order Details table using the DeleteOnSubmit method. First, delete from order details and then delete from orders. Call SubmitChanges to persist this deletion to the database.

3. Inference deletion (inferred delete)

Description: Order with OrderDetail is a one-to-many relationship, in the above example, we all delete CustomerID for Warth and EmployeeID 3 data, then we do not have to delete all? For example, the order of OrderID is 10248 OrderDetail There are many, but we just delete the OrderDetail ProductID is 11. Then use the Remove method.

Order order = db. Orders.first (x = X.orderid = = 10248); OrderDetail od =     order. Orderdetails.first (d = D.productid = = one); order. Orderdetails.remove (OD);d B. SubmitChanges ();

Statement Description: This example shows how the inferred delete causes the actual delete operation to occur on an entity object when it is removed from its entityset by a reference entity. The inference delete behavior occurs only if the entity's association map sets DeleteOnNull to True and Canbenull is false.

Using Attach updates (update with Attach)

Description: Use the Attach method to update data between different DataContext. For example, in a northwinddatacontext named Tempdb, the customer and order are queried, and in another NorthwindDataContext, the customer's address is updated to 123 first Ave, The Order's CustomerID is updated to chops.

Typically, you get an entity to attach by deserializing the XML from another layer//does not support attaching an entity from one DataContext to another datacontext//therefore, to replicate the actions of the deserialized entity, the entities are recreated here customer C1; list<order> deserializedorders = new list<order> (); Customer deserializedc1;using (northwinddatacontext tempdb = new NorthwindDataContext ()) {c1 = tempdb.    Customers.single (c = C.customerid = = "ALFKI"); deserializedC1 = 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.    Customers.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, Sh ipAddress = 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 and cannot write wrong DB2.    Customers.attach (deserializedC1);    Change the tracked entity deserializedc1.address = "123 First Ave"; All entity DB2 in the list of attached orders.    Orders.attachall (deserializedorders);    Update the order to belong to another customer foreach (Order o in deserializedorders) {O.customerid = "chops"; }//Commit the change DB2 in the current data context. SubmitChanges ();}

Statement Description: Gets an entity from another layer, appends the deserialized entity to the data context using attach and Attachall, and then updates the entity. The changes are committed to the database.

Update and delete using Attach (update and delete with Attach)

Description: In different DataContext, implement INSERT, UPDATE, delete. Look at one of the following examples:

Typically, you get an entity to attach by deserializing the XML from another layer//This example uses LoadWith to preload customers and orders in a query,//and disables lazy loading of customer cust = null;using (northwinddatacontext    tempdb = new NorthwindDataContext ()) {dataloadoptions shape = new DataLoadOptions (); Shape.    Loadwith<customer> (c = c.orders); Loads the first customer entity and its order tempdb.    Loadoptions = shape; Tempdb.    deferredloadingenabled = false; Cust = tempdb. Customers.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 ()) {// An entity is attached to the current data context to track changes DB2.    Customers.attach (Cust); Attach related orders for tracking; Otherwise, they will be inserted DB2 at commit time. Orders.attachall (Cust.    Orders.tolist ());    Update the customer's phone. Cust.    Phone = "2345 5436";    Update the shipcity of the first order Ordera.    ordera.shipcity = "Redmond";    Remove the second order Orderb. Cust.    Orders.remove (Orderb);    Add a new order to customer customers.    Order Orderc = New Order () {ShipCity = "new York"}; Cust.   Orders.add (ORDERC); Submit the execution DB2. SubmitChanges ();}

Statement Description: Extracts entities from one context and appends entities from other contexts using Attach and Attachall, then updates the two entities, deletes one entity, and adds another entity. The changes are committed to the database.

This series of Links: LINQ Experience Series articles Navigation

LINQ Experience (9)--LINQ to SQL statement insert/update/delete operation