延遲執(zhí)行
IQueryable query = from c in ctx.Customers select c;
這樣的查詢句法不會(huì)導(dǎo)致語句立即執(zhí)行，它僅僅是一個(gè)描述，對應(yīng)一個(gè)SQL。僅僅在需要使用的時(shí)候才會(huì)執(zhí)行語句，比如：
IQueryable query = from c in ctx.Customers select c;
foreach (Customer c in query)
Response.Write(c.CustomerID);
如果你執(zhí)行兩次foreach操作，將會(huì)捕獲到兩次SQL語句的執(zhí)行：
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);
對應(yīng)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()等方法把查詢結(jié)果先進(jìn)行保存，然后再對集合進(jìn)行查詢：
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);
延遲執(zhí)行的優(yōu)點(diǎn)在于我們可以像拼接SQL那樣拼接查詢句法，然后再執(zhí)行：
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方法并沒有使用到訂單詳細(xì)信息，所以這個(gè)操作只會(huì)執(zhí)行下面的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]
現(xiàn)在修改一下ShowDetail方法：
private void ShowDetail(EntitySet<Order_Detail> orderdetails)
{
foreach (var o in orderdetails)
{
Response.Write(o.Quantity + "<br>");
}
}
你會(huì)發(fā)現(xiàn)Linq to sql對每個(gè)價(jià)格大于10的產(chǎn)品都根據(jù)產(chǎn)品號進(jìn)行了一次查詢：
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次。這樣的查詢不是很合理，我們可以通過設(shè)置DataContext的DataLoadOption，來指示 DataContext再加載產(chǎn)品信息的同時(shí)把對應(yīng)的產(chǎn)品訂單信息一起加載：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Product>(p => p.Order_Details);
ctx.LoadOptions = options;
var products = from p in ctx.Products select p;
。。。。。。。。
再執(zhí)行先前的查詢會(huì)發(fā)現(xiàn)Linq to sql進(jìn)行了左連接：
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]
那么，我們怎么限制訂單詳細(xì)表的加載條件那？
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;
這樣，就只會(huì)有數(shù)量大于80的訂單詳細(xì)信息會(huì)和產(chǎn)品一起加載：
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個(gè)1對多的關(guān)系。一個(gè)顧客可能有多個(gè)訂單，一個(gè)訂單可能有多個(gè)詳細(xì)訂單：
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>();
這樣的語句執(zhí)行后會(huì)導(dǎo)致下面的SQL執(zhí)行N次（參數(shù)不同）：
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的關(guān)系，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>();
由于多個(gè)產(chǎn)品對應(yīng)1個(gè)分類，多個(gè)詳細(xì)訂單對應(yīng)1個(gè)訂單，只有產(chǎn)品和詳細(xì)訂單才是多對1的關(guān)系，所以也只會(huì)有1次SQL（不過這樣的操作還是少執(zhí)行為妙，消耗太大了）：
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對查詢過的對象進(jìn)行緩存，之后的如果只根據(jù)主鍵查詢一條記錄的話會(huì)直接從緩存中讀取。比如下面的代碼：
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);
執(zhí)行后只會(huì)產(chǎn)生一條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]
由于沒有提交修改，所以數(shù)據(jù)庫中的記錄還是沒有更新。由于這個(gè)特性，我們在使用存儲(chǔ)過程作為實(shí)體更新方法的時(shí)候就要當(dāng)心了，存儲(chǔ)過程書寫錯(cuò)誤，即使你提交了修改也很可能導(dǎo)致緩存中的數(shù)據(jù)和數(shù)據(jù)庫中的數(shù)據(jù)不一致，引起不必要的麻煩。
DataContext隔離
有的時(shí)候我們會(huì)把對象從外部傳入DataContext，要求它更新，由于不同的DataContext是相對獨(dú)立的。由于新的DataContext中還沒有獲取實(shí)體，我們只能通過附加方式更新數(shù)據(jù)。
首先把Customer表的主鍵字段加上IsVersion標(biāo)識：
[Column(Storage="_CustomerID", DbType="NChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true, IsVersion = true)]
運(yùn)行下面的測試代碼：
Customer c = new Customer { CustomerID = "ALFKI", ContactName = "zhuye", CompanyName = "1111" };
ctx.Customers.Attach(c, true);
ctx.SubmitChanges();
會(huì)捕捉到下面的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) []
今天就到這里，下次講并發(fā)與事務(wù)問題。
0
0
0(請您對文章做出評價(jià))
