LINQ Insert/Update/Delete操作

 

  Insert/Update/Delete操作

 

  Insert

 

  1.简单形式

 

  说明：new一个对象，使用InsertOnSubmit方法将其加入到对应的集合中，使用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 = "[phone]",Fax = null };

 

  db.Customers.InsertOnSubmit(newCustomer);

 

  db.SubmitChanges();

 

  2.一对多关系

 

  说明：Category与Product是一对多的关系，提交Category(一端)的数据时，LINQ to SQL会自动将Product(多端)的数据一起提交。

  var newCategory = new Category { CategoryName = "Widgets", Description = "Widgets are the customer-facing

 

   analogues to sprockets and cogs." };

 

  var newProduct = new Product { ProductName = "Blue Widget", UnitPrice = 34.56M, Category = newCategory };

 

  db.Categories.InsertOnSubmit(newCategory);

 

  db.SubmitChanges();

 

  3.多对多关系

 

  说明：在多对多关系中，我们需要依次提交。

  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();

 

  4.Override using Dynamic CUD

 

  说明：CUD就是Create、Update、Delete的缩写。下面的例子就是新建一个ID(主键)为32的Region，不考虑数据库中有没有ID为32的数据，如果有则替换原来的数据，没有则插入。（不知道这样说对不对。大家指点一下）

  Region nwRegion = new Region() { RegionID = 32, RegionDescription = "Rainy" };

 

  db.Regions.InsertOnSubmit(nwRegion);

 

  db.SubmitChanges();

 

  Update

 

  说明：更新操作，先获取对象，进行修改操作之后，直接调用SubmitChanges()方法即可提交。注意，这里是在同一个DataContext中，对于不同的DataContex看下面的讲解。

  1.简单形式

 

  Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");

 

  cust.ContactTitle = "Vice President";

 

  db.SubmitChanges();

 

  2.多个项

 

  var q = from p in db.Products

 

   where p.CategoryID == 1

 

   select p;

 

  foreach (var p in q)

 

  {

 

   p.UnitPrice += 1.00M;

 

  }

 

  db.SubmitChanges();

 

  Delete

 

  1.简单形式

 

  说明：调用DeleteOnSubmit方法即可。

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

 

  db.OrderDetails.DeleteOnSubmit(orderDetail);

 

  db.SubmitChanges();

 

  2.一对多关系

 

  说明：Order与OrderDetail是一对多关系，首先DeleteOnSubmit其OrderDetail(多端)，其次DeleteOnSubmit其Order(一端)。因为一端是主键。

  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();

 

  3.Inferred Delete(推断删除)

 

  说明：Order与OrderDetail是一对多关系，在上面的例子，我们全部删除CustomerID为WARTH和 EmployeeID为3 的数据，那么我们不须全部删除呢？例如Order的OrderID为10248的OrderDetail有很多，但是我们只要删除ProductID为 11的OrderDetail。这时就用Remove方法。

  Order order = db.Orders.First(x => x.OrderID == 10248);

 

  OrderDetail od = order.OrderDetails.First(d => d.ProductID == 11);

 

  order.OrderDetails.Remove(od);

 

  db.SubmitChanges();

 

  Update with Attach

 

  说明：在对于在不同的DataContext之间，使用Attach方法来更新数据。例如在一个名为tempdb的 NorthwindDataContext中，查询出Customer和Order，在另一个NorthwindDataContext 中，Customer的地址更新为123 First Ave，Order的CustomerID 更新为CHOPS。

  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,

 

   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())

 

  {

 

   //对Customer更新，不能写错

 

   db2.Customers.Attach(deserializedC1);

 

   deserializedC1.Address = "123 First Ave";

 

   //对Order全部更新

 

   db2.Orders.AttachAll(deserializedOrders);

 

   foreach (Order o in deserializedOrders)

 

   {

 

   o.CustomerID = "CHOPS";

 

   }

 

   db2.SubmitChanges();

 

  }

 

  Update and Delete with Attach

 

  说明：在不同的DataContext中，实现插入、更新、删除。看下面的一个例子：

 

  

 

  Customer cust = null;

 

  using (NorthwindDataContext tempdb = new NorthwindDataContext())

 

  {

 

   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())

 

  {

 

   db2.Customers.Attach(cust);

 

   db2.Orders.AttachAll(cust.Orders.ToList());

 

   //更新Customer的Phone.

 

   cust.Phone = "2345 5436";

 

   //更新OrderA的ShipCity.

 

   orderA.ShipCity = "Redmond";

 

   //删除OrderB.

 

   cust.Orders.Remove(orderB);

 

   //添加一个新的Order到Customer中.

 

   Order orderC = new Order() { ShipCity = "New York" };

 

   cust.Orders.Add(orderC);

 

   //提交执行

 

   db2.SubmitChanges();

 

  }