|
|
系列文章導(dǎo)航：
一步一步學(xué)Linq to sql（一）：預(yù)備知識(shí)
一步一步學(xué)Linq to sql（二）：DataContext與實(shí)體
一步一步學(xué)Linq to sql（五）：存儲(chǔ)過(guò)程
一步一步學(xué)Linq to sql（七）：并發(fā)與事務(wù)
一步一步學(xué)Linq to sql（八）：繼承與關(guān)系
一步一步學(xué)Linq to sql（九）：其它補(bǔ)充
一步一步學(xué)Linq to sql（十）：分層構(gòu)架的例子
延遲執(zhí)行
IQueryable query = from c in ctx.Customers select c; |
這樣的查詢句法不會(huì)導(dǎo)致語(yǔ)句立即執(zhí)行，它僅僅是一個(gè)描述，對(duì)應(yīng)一個(gè)SQL。僅僅在需要使用的時(shí)候才會(huì)執(zhí)行語(yǔ)句，比如：
IQueryable query = from c in ctx.Customers select c; foreach (Customer c in query) Response.Write(c.CustomerID); |
如果你執(zhí)行兩次foreach操作，將會(huì)捕獲到兩次SQL語(yǔ)句的執(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); |
對(duì)應(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] |
對(duì)于這樣的需求，建議你先使用ToList()等方法把查詢結(jié)果先進(jìn)行保存，然后再對(duì)集合進(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); |
系列文章導(dǎo)航：
一步一步學(xué)Linq to sql（一）：預(yù)備知識(shí)
一步一步學(xué)Linq to sql（二）：DataContext與實(shí)體
一步一步學(xué)Linq to sql（五）：存儲(chǔ)過(guò)程
一步一步學(xué)Linq to sql（七）：并發(fā)與事務(wù)
一步一步學(xué)Linq to sql（八）：繼承與關(guān)系
一步一步學(xué)Linq to sql（九）：其它補(bǔ)充
一步一步學(xué)Linq to sql（十）：分層構(gòu)架的例子
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方法并沒(méi)有使用到訂單詳細(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對(duì)每個(gè)價(jià)格大于10的產(chǎn)品都根據(jù)產(chǎn)品號(hào)進(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] |
這樣的語(yǔ)句查詢了N次。這樣的查詢不是很合理，我們可以通過(guò)設(shè)置DataContext的DataLoadOption，來(lái)指示 DataContext再加載產(chǎn)品信息的同時(shí)把對(duì)應(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] |
那么，我們?cè)趺聪拗朴唵卧敿?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] |
系列文章導(dǎo)航：
一步一步學(xué)Linq to sql（一）：預(yù)備知識(shí)
一步一步學(xué)Linq to sql（二）：DataContext與實(shí)體
一步一步學(xué)Linq to sql（五）：存儲(chǔ)過(guò)程
一步一步學(xué)Linq to sql（七）：并發(fā)與事務(wù)
一步一步學(xué)Linq to sql（八）：繼承與關(guān)系
一步一步學(xué)Linq to sql（九）：其它補(bǔ)充
一步一步學(xué)Linq to sql（十）：分層構(gòu)架的例子
DataLoadOptions限制
Linq to sql對(duì)DataLoadOptions的使用是有限制的，它只支持1個(gè)1對(duì)多的關(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>(); |
這樣的語(yǔ)句執(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] |
而對(duì)于多對(duì)1的關(guān)系，Linq to sql對(duì)于DataLoadOptions沒(méi)有限制：
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)品對(duì)應(yīng)1個(gè)分類，多個(gè)詳細(xì)訂單對(duì)應(yīng)1個(gè)訂單，只有產(chǎn)品和詳細(xì)訂單才是多對(duì)1的關(guān)系，所以也只會(huì)有1次SQL（不過(guò)這樣的操作還是少執(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] |
系列文章導(dǎo)航：
一步一步學(xué)Linq to sql（一）：預(yù)備知識(shí)
一步一步學(xué)Linq to sql（二）：DataContext與實(shí)體
一步一步學(xué)Linq to sql（五）：存儲(chǔ)過(guò)程
一步一步學(xué)Linq to sql（七）：并發(fā)與事務(wù)
一步一步學(xué)Linq to sql（八）：繼承與關(guān)系
一步一步學(xué)Linq to sql（九）：其它補(bǔ)充
一步一步學(xué)Linq to sql（十）：分層構(gòu)架的例子
主鍵緩存
Linq to sql對(duì)查詢過(guò)的對(duì)象進(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] |
由于沒(méi)有提交修改，所以數(shù)據(jù)庫(kù)中的記錄還是沒(méi)有更新。由于這個(gè)特性，我們?cè)谑褂?a href=/pingce/cunchu/ target=_blank class=infotextkey>存儲(chǔ)過(guò)程作為實(shí)體更新方法的時(shí)候就要當(dāng)心了，存儲(chǔ)過(guò)程書寫錯(cuò)誤，即使你提交了修改也很可能導(dǎo)致緩存中的數(shù)據(jù)和數(shù)據(jù)庫(kù)中的數(shù)據(jù)不一致，引起不必要的麻煩。
DataContext隔離
有的時(shí)候我們會(huì)把對(duì)象從外部傳入DataContext，要求它更新，由于不同的DataContext是相對(duì)獨(dú)立的。由于新的DataContext中還沒(méi)有獲取實(shí)體，我們只能通過(guò)附加方式更新數(shù)據(jù)。
首先把Customer表的主鍵字段加上IsVersion標(biāo)識(shí)：
[Column(Storage="_CustomerID", DbType="NChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true, IsVersion = true)] |
運(yùn)行下面的測(cè)試代碼：
Customer c = new Customer { CustomerID = "ALFKI", ContactName = "zhuye", CompanyName = "1111" }; ctx.Customers.Attach(c, true); ctx.SubmitChanges(); |
會(huì)捕捉到下面的SQL語(yǔ)句：
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ù)問(wèn)題。
it知識(shí)庫(kù)：一步一步學(xué)Linq to sql（六）：探究特性，轉(zhuǎn)載需保留來(lái)源！
鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。
