您的位置:知识库 » 数据库

一步一步学Linq to sql(六):探究特性

作者: lovecherry  来源: 博客园  发布时间: 2008-09-26 23:06  阅读: 10094 次  推荐: 0   原文链接   [收藏]  

系列文章导航:

一步一步学Linq to sql(一):预备知识

一步一步学Linq to sql(二):DataContext与实体

一步一步学Linq to sql(三):增删改

一步一步学Linq to sql(四):查询句法

一步一步学Linq to sql(五):存储过程

一步一步学Linq to sql(六):探究特性

一步一步学Linq to sql(七):并发与事务

一步一步学Linq to sql(八):继承与关系

一步一步学Linq to sql(九):其它补充

一步一步学Linq to sql(十):分层构架的例子


 

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次。这样的查询不是很合理,我们可以通过设置DataContextDataLoadOption,来指示 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]

0
0

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻