《一步一步学Linqtosql6探究特性》由会员分享，可在线阅读，更多相关《一步一步学Linqtosql6探究特性（10页珍藏版）》请在金锄头文库上搜索。
1、一步一步学 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
2、= 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, t
3、0.FaxFROM dbo.Customers AS t0SELECT t0.CustomerID, t0.CompanyName, t0.ContactName, t0.ContactTitle, t0.Address, t0.City, t0.Region, t0.PostalCode, t0.Country, t0.Phone, t0.FaxFROM dbo.Customers AS t0对于这样的需求，建议你先使用 ToList()等方法把查询结果先进行保存，然后再对集合进行查询：IEnumerable customers = (from c in ctx.Customers sele
4、ct 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);DataLoadOpt
5、ionsvar 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.QuantityPe
6、rUnit, t0.UnitPrice, t0.UnitsInStock, t0.UnitsOnOrder, t0.ReorderLevel, t0.DiscontinuedFROM 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.OrderI
7、D, t0.ProductID, t0.UnitPrice, t0.Quantity, t0.DiscountFROM dbo.Order Details AS t0WHERE t0.ProductID = p0- p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) 1这样的语句查询了 N 次。这样的查询不是很合理，我们可以通过设置DataContext 的 DataLoadOption，来指示 DataContext 再加载产品信息的同时把对应的产品订单信息一起加载：DataLoadOptions options = new DataLoadOpt
8、ions();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
9、, t0.Discontinued, t1.OrderID, t1.ProductID AS ProductID2, t1.UnitPrice AS UnitPrice2, t1.Quantity, t1.Discount, (SELECT COUNT(*)FROM dbo.Order Details AS t2WHERE t2.ProductID = t0.ProductID) AS countFROM dbo.Products AS t0LEFT OUTER JOIN dbo.Order Details AS t1 ON t1.ProductID = t0.ProductIDORDER B
10、Y 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 的订单详细信息会和产
11、品一起加载：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
12、Details AS t2WHERE (t2.Quantity p0) AND (t2.ProductID = t0.ProductID) AS countFROM dbo.Products AS t0LEFT 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) 80DataLoadOptions 限制Lin
13、q to sql 对 DataLoadOptions 的使用是有限制的，它只支持 1 个 1 对多的关系。一个顾客可能有多个订单，一个订单可能有多个详细订单：DataLoadOptions options = new DataLoadOptions();options.LoadWith(c = c.Orders);options.LoadWith(o = o.Order_Details);ctx.LoadOptions = options;IEnumerable customers = ctx.Customers.ToList();这样的语句执行后会导致下面的 SQL 执行 N 次（参数不同）
14、：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 t2WHERE t2.OrderID = t0.OrderID) AS countFROM dbo.Orders AS t0LEFT OUTER JOIN dbo.Order Details AS t1 ON t1.OrderID = t0.OrderIDWHERE t0.CustomerID = x1ORDER
