學習 Linq to SQL (九)：進階補充 — 外部對應、空值、編譯查詢、批次操作

1. 外部對應檔

使用 sqlmetal 產生 XML 對應檔與實體類別，避免每次修改資料庫結構需重新拖拉 DBML：

sqlmetal /conn:server=xxx;database=Northwind;uid=xxx;pwd=xxx /map:c:\northwind.map /code:c:\northwind.cs

載入方式：

var xms = XmlMappingSource.FromXml(File.ReadAllText(@"C:\Northwind.map"));
var ctx = new Northwind("connectionString", xms);

2. 處理 Null 值

// 查詢 Region 為 null 的客戶數
var count = (from c in ctx.Customers where c.Region == null select c).Count();

// 列舉員工主管 (ReportsTo 為 nullable int)
foreach (Nullable<int> r in (from e in ctx.Employees select e.ReportsTo))
  Console.WriteLine(r.HasValue ? r.Value.ToString() : "無");

產生 SQL 正確使用 IS NULL。

3. 編譯查詢

將常用查詢封裝為 CompiledQuery 提升效能：

static class Queries {
  public static Func<NorthwindDataContext, string, IQueryable<Customer>> CustomersByCity =
    CompiledQuery.Compile((NorthwindDataContext ctx, string city) => from c in ctx.Customers where c.City == city select c);
}
// 呼叫
GridView1.DataSource = Queries.CustomersByCity(ctx, "London");

4. 取得中繼資訊

var q = from c in ctx.Customers select c;
Console.WriteLine("Provider: " + ctx.Mapping.ProviderType);
Console.WriteLine("Database: " + ctx.Mapping.DatabaseName);
Console.WriteLine("Table: " + ctx.Mapping.GetTable(typeof(Customer)).TableName);
Console.WriteLine("Expression: " + q.Expression);
Console.WriteLine("SQL: " + q.Provider);

5. 撤銷變更 (取得原始狀態)

var cust = ctx.Customers.Single(c => c.CustomerID == "AROUT");
cust.ContactName = "zhuye"; cust.Country = "Shanghai";
Console.WriteLine($"修改後: {cust.ContactName}, {cust.Country}");
var orig = ctx.Customers.GetOriginalEntityState(cust);
Console.WriteLine($"原始值: {orig.ContactName}, {orig.Country}");

輸出：修改後 zhuye/Shanghai，原始值 Thomas Hardy/UK。

6. 批次操作

刪除：ctx.Customers.RemoveAll(query) 會產生 N 條 DELETE，改用 SQL 批次：

string sql = $"delete from {ctx.Mapping.GetTable(typeof(Customer)).TableName}";
ctx.ExecuteCommand(sql);

更新 同理，使用 ExecuteCommand 執行批次 UPDATE。