LINQ to SQL CRUD 操作完整指南（9）：Insert/Update/Delete、一對多關聯、Attach 與 SubmitChanges 交易行為

LINQ to SQL 透過實體集合的 InsertOnSubmit/DeleteOnSubmit 追蹤變更，SubmitChanges 以交易方式一次提交。

Insert 插入操作

1. 簡單插入

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"
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();

2. 一對多關聯同步插入

var newCategory = new Category { CategoryName = "Widgets", Description = "..." };
var newProduct = new Product { ProductName = "Blue Widget", UnitPrice = 34.56M, Category = newCategory };
db.Categories.InsertOnSubmit(newCategory); // 僅插入一端，多端自動關聯
db.SubmitChanges();

Update 更新操作

var cust = db.Customers.First(c => c.CustomerID == "ALFKI");
cust.ContactName = "New Name";
db.SubmitChanges(); // 自動偵測異動欄位，產生 UPDATE SQL

Delete 刪除操作

var cust = db.Customers.First(c => c.CustomerID == "ALFKI");
db.Customers.DeleteOnSubmit(cust);
db.SubmitChanges();

進階：Attach 離線實體更新

// 實體來自其他 DataContext 或序列化傳輸
db.Customers.Attach(updatedCust, originalCust); // 原始值用於並發檢查
db.SubmitChanges();

關鍵特性

• SubmitChanges 隱含交易：全成功或全失敗回滾
• 關聯導覽屬性自動維護外鍵
• 僅產生異動欄位的 UPDATE 語句