延遲執行
IQueryable query = from c in ctx.Customers select c;
這樣的查詢句法不會導致語句立即執行，它僅僅是一個描述，對應一個SQL。僅僅在需要使用的時候才會執行語句，比如：
IQueryable query = from c in ctx.Customers select c;
foreach (Customer c in query)
Response.Write(c.CustomerID);
如果你執行兩次foreach操作，將會捕獲到兩次SQL語句的執行：
IQueryable query = from c in ctx.Customers select c;
foreach (Customer c in query)
Response.Write(c.CustomerID);
foreach (Customer c in query)
Response.Write(c.ContactName);
對應SQL：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
對於這樣的需求，建議你先使用ToList()等方法把查詢結果先進行保存，然後再對集合進行查詢：
IEnumerable<Customer> customers = (from c in ctx.Customers select c).ToList();
foreach (Customer c in customers)
Response.Write(c.CustomerID);
foreach (Customer c in customers)
Response.Write(c.ContactName);
延遲執行的優點在於我們可以像拼接SQL那樣拼接查詢句法，然後再執行：
var query = from c in ctx.Customers select c;
var newquery = (from c in query select c).OrderBy(c => c.CustomerID);
DataLoadOptions
var products = from p in ctx.Products select p;
foreach (var p in products)
{
if (p.UnitPrice > 10)
ShowDetail(p.Order_Details);
}
private void ShowDetail(EntitySet<Order_Detail> orderdetails)
{}
由於ShowDetail方法並沒有使用到訂單詳細信息，所以這個操作只會執行下面的SQL：
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
現在修改一下ShowDetail方法：
private void ShowDetail(EntitySet<Order_Detail> orderdetails)
{
foreach (var o in orderdetails)
{
Response.Write(o.Quantity + "<br>");
}
}
你會發現Linq to sql對每個價格大於10的產品都根據產品號進行了一次查詢：
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[ProductID] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
這樣的語句查詢了N次。這樣的查詢不是很合理，我們可以通過設置DataContext的DataLoadOption，來指示 DataContext再加載產品信息的同時把對應的產品訂單信息一起加載：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Product>(p => p.Order_Details);
ctx.LoadOptions = options;
var products = from p in ctx.Products select p;
。。。。。。。。
再執行先前的查詢會發現Linq to sql進行了左連接：
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t1].[OrderID], [t1].[ProductID] AS [ProductID2], [t1].[UnitPrice] AS [UnitPrice2], [t1].[Quantity], [t1].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[ProductID] = [t0].[ProductID]
) AS [count]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[ProductID] = [t0].[ProductID]
ORDER BY [t0].[ProductID], [t1].[OrderID]
那麼，我們怎麼限制訂單詳細表的加載條件那？
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Product>(p => p.Order_Details);
options.AssociateWith<Product>(p => p.Order_Details.Where(od => od.Quantity > 80));
ctx.LoadOptions = options;
var products = from p in ctx.Products select p;
這樣，就只會有數量大於80的訂單詳細信息會和產品一起加載：
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t1].[OrderID], [t1].[ProductID] AS [ProductID2], [t1].[UnitPrice] AS [UnitPrice2], [t1].[Quantity], [t1].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE ([t2].[Quantity] > @p0) AND ([t2].[ProductID] = [t0].[ProductID])
) AS [count]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON ([t1].[Quantity] > @p0) AND ([t1].[ProductID] = [t0].[ProductID])
ORDER BY [t0].[ProductID], [t1].[OrderID]
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [80]
DataLoadOptions限制
Linq to sql對DataLoadOptions的使用是有限制的，它只支持1個1對多的關系。一個顧客可能有多個訂單，一個訂單可能有多個詳細訂單：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Orders);
options.LoadWith<Order>(o => o.Order_Details);
ctx.LoadOptions = options;
IEnumerable<Customer> customers = ctx.Customers.ToList<Customer>();
這樣的語句執行後會導致下面的SQL執行N次（參數不同）：
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[OrderID] = [t0].[OrderID]
) AS [count]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
WHERE [t0].[CustomerID] = @x1
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [ALFKI]
而對於多對1的關系，Linq to sql對於DataLoadOptions沒有限制：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Product>(c => c.Category);
options.LoadWith<Product>(c => c.Order_Details);
options.LoadWith<Order_Detail>(o => o.Order);
ctx.LoadOptions = options;
IEnumerable<Product> products = ctx.Products.ToList<Product>();
由於多個產品對應1個分類，多個詳細訂單對應1個訂單，只有產品和詳細訂單才是多對1的關系，所以也只會有1次SQL（不過這樣的操作還是少執行為妙，消耗太大了）：
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t3].[OrderID], [t3].[ProductID] AS [ProductID2], [t3].[UnitPrice] AS [UnitPrice2], [t3].[Quantity], [t3].[Discount], [t4].[OrderID] AS [OrderID2], [t4].[CustomerID], [t4].[EmployeeID], [t4].[OrderDate], [t4].[RequiredDate], [t4].[ShippedDate], [t4].[ShipVia], [t4].[Freight], [t4].[ShipName], [t4].[ShipAddress], [t4].[ShipCity], [t4].[ShipRegion], [t4].[ShipPostalCode], [t4].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t5]
INNER JOIN [dbo].[Orders] AS [t6] ON [t6].[OrderID] = [t5].[OrderID]
WHERE [t5].[ProductID] = [t0].[ProductID]
) AS [count], [t2].[test], [t2].[CategoryID] AS [CategoryID2], [t2].[CategoryName], [t2].[Description], [t2].[Picture]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[CategoryID], [t1].[CategoryName], [t1].[Description], [t1].[Picture]
FROM [dbo].[Categories] AS [t1]
) AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]
LEFT OUTER JOIN ([dbo].[Order Details] AS [t3]
INNER JOIN [dbo].[Orders] AS [t4] ON [t4].[OrderID] = [t3].[OrderID]) ON [t3].[ProductID] = [t0].[ProductID]
ORDER BY [t0].[ProductID], [t2].[CategoryID], [t3].[OrderID]
主鍵緩存
Linq to sql對查詢過的對象進行緩存，之後的如果只根據主鍵查詢一條記錄的話會直接從緩存中讀取。比如下面的代碼：
Customer c1 = ctx.Customers.Single(customer => customer.CustomerID == "ANATR");
c1.ContactName = "zhuye";
Customer c2 = ctx.Customers.Single(customer => customer.CustomerID == "ANATR");
Response.Write(c2.ContactName);
執行後只會產生一條SQL：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [ANATR]
由於沒有提交修改，所以數據庫中的記錄還是沒有更新。由於這個特性，我們在使用存儲過程作為實體更新方法的時候就要當心了，存儲過程書寫錯誤，即使你提交了修改也很可能導致緩存中的數據和數據庫中的數據不一致，引起不必要的麻煩。
DataContext隔離
有的時候我們會把對象從外部傳入DataContext，要求它更新，由於不同的DataContext是相對獨立的。由於新的DataContext中還沒有獲取實體，我們只能通過附加方式更新數據。
首先把Customer表的主鍵字段加上IsVersion標識：
[Column(Storage="_CustomerID", DbType="NChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true, IsVersion = true)]
運行下面的測試代碼：
Customer c = new Customer { CustomerID = "ALFKI", ContactName = "zhuye", CompanyName = "1111" };
ctx.Customers.Attach(c, true);
ctx.SubmitChanges();
會捕捉到下面的SQL語句：
UPDATE [dbo].[Customers]
SET [CompanyName] = @p2, [ContactName] = @p3, [ContactTitle] = @p4, [Address] = @p5, [City] = @p6, [Region] = @p7, [PostalCode] = @p8, [Country] = @p9, [Phone] = @p10, [Fax] = @p11
WHERE ([CustomerID] = @p0) AND ([CustomerID] = @p1)
-- @p0: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [ALFKI]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [ALFKI]
-- @p2: Input String (Size = 4; Prec = 0; Scale = 0) [1111]
-- @p3: Input String (Size = 5; Prec = 0; Scale = 0) [zhuye]
-- @p4: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p5: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p6: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p7: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p8: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p9: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p10: Input String (Size = 0; Prec = 0; Scale = 0) []
-- @p11: Input String (Size = 0; Prec = 0; Scale = 0) []
今天就到這裡，下次講並發與事務問題。
