一步一步学Linq to sql(12)

-- @p1: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [notadmin] -- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) [] 到这里，你应该能明白当前值和原始值的含义了吧。

一步一步学Linq to sql（六）：探究特性 延迟执行

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 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 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 orderdetails) { foreach (var o in orderdetails) { Response.Write(o.Quantity + \); } } 你会发现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(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(p => p.Order_Details); options.AssociateWith(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(c => c.Orders); options.LoadWith(o => o.Order_Details); ctx.LoadOptions = options; IEnumerable …… 此处隐藏：2574字，全部文档内容请下载后查看。喜欢就下载吧 ……