Linq to sql：DataLoadOptions

var products = from p inctx.Products select p;

 

foreach (var p inproducts)

 

                {

 

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 inorderdetails)

 

              {

 

          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 inctx.Products select p;

 

。。。。。。。。

 

再执行先前的查询会发现Linqto 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 inctx.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]