LINQ to SQL語句(12)之Delete和使用Attach - SuperMetalMax－IT工程師數位筆記本

文章出處

1.簡單形式

說明：調用DeleteOnSubmit方法即可。

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

語句描述：使用DeleteOnSubmit方法從OrderDetail 表中刪除OrderDetail對象。調用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();

語句描述語句描述：使用DeleteOnSubmit方法從Order 和Order Details表中刪除Order和Order Detail對象。首先從Order Details刪除，然后從Orders刪除。調用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();

語句描述語句描述：這個例子說明在實體對象的引用實體將該對象從其EntitySet 中移除時，推理刪除如何導致在該對象上發生實際的刪除操作。僅當實體的關聯映射將DeleteOnNull設置為true且CanBeNull 為false 時，才會發生推理刪除行為。

使用Attach更新(Update with Attach)

說明：在對于在不同的DataContext之間，使用Attach方法來更新數據。例如在一個名為tempdb的NorthwindDataContext中，查詢出Customer和Order，在另一個NorthwindDataContext中，Customer的地址更新為123 First Ave，Order的CustomerID 更新為CHOPS。

//通常，通過從其他層反序列化 XML 來獲取要附加的實體
//不支持將實體從一個DataContext附加到另一個DataContext
//因此若要復制反序列化實體的操作，將在此處重新創建這些實體
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";
    //附加訂單列表中的所有實體
    db2.Orders.AttachAll(deserializedOrders);
    //將訂單更新為屬于其他客戶
    foreach (Order o in deserializedOrders)
    {
        o.CustomerID = "CHOPS";
    }
    //在當前數據上下文中提交更改
    db2.SubmitChanges();
}

語句描述：從另一個層中獲取實體，使用Attach和AttachAll將反序列化后的實體附加到數據上下文，然后更新實體。更改被提交到數據庫。

使用Attach更新和刪除(Update and Delete with Attach)

說明：在不同的DataContext中，實現插入、更新、刪除。看下面的一個例子：

//通常，通過從其他層反序列化XML獲取要附加的實體
//此示例使用 LoadWith 在一個查詢中預先加載客戶和訂單，
//并禁用延遲加載
Customer cust = null;
using (NorthwindDataContext tempdb = new NorthwindDataContext())
{
    DataLoadOptions shape = new DataLoadOptions();
    shape.LoadWith<Customer>(c => c.Orders);
    //加載第一個客戶實體及其訂單
    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())
{
    //將第一個實體附加到當前數據上下文，以跟蹤更改
    db2.Customers.Attach(cust);
    //附加相關訂單以進行跟蹤; 否則將在提交時插入它們
    db2.Orders.AttachAll(cust.Orders.ToList());
    //更新客戶的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();
}