Linq to sql：探究特性-技术开发专区

Linq to sql：探究特性

作者：lovecherry博客编辑： nancy 2008-01-25 00:00

你会发现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]

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>();

第1页：概述第2页：概述第3页：概述第4页：概述

关注我们