|
|
系列文章導航：
一步一步學Linq to sql（二）：DataContext與實體
延遲執行
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); |
系列文章導航：
一步一步學Linq to sql（二）：DataContext與實體
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] |
系列文章導航：
一步一步學Linq to sql（二）：DataContext與實體
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（二）：DataContext與實體
主鍵緩存
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) [] |
今天就到這里，下次講并發與事務問題。
it知識庫：一步一步學Linq to sql（六）：探究特性，轉載需保留來源！
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。
