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

一步一步学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限制

       Linq to sqlDataLoadOptions的使用是有限制的,它只支持11对多的关系。一个顾客可能有多个订单,一个订单可能有多个详细订单:

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

       这样的语句执行后会导致下面的SQL执行N次(参数不同):

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 [t2]

    WHERE [t2].[OrderID] = [t0].[OrderID]

    ) AS [count]

FROM [dbo].[Orders] AS [t0]

LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]

WHERE [t0].[CustomerID] = @x1

ORDER BY [t0].[OrderID], [t1].[ProductID]

-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [ALFKI]

       而对于多对1的关系,Linq to sql对于DataLoadOptions没有限制:

        DataLoadOptions options = new DataLoadOptions();

        options.LoadWith<Product>(c => c.Category);

        options.LoadWith<Product>(c => c.Order_Details);

        options.LoadWith<Order_Detail>(o => o.Order);

        ctx.LoadOptions = options;

        IEnumerable<Product> products = ctx.Products.ToList<Product>();

       由于多个产品对应1个分类,多个详细订单对应1个订单,只有产品和详细订单才是多对1的关系,所以也只会有1SQL(不过这样的操作还是少执行为妙,消耗太大了):

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t3].[OrderID], [t3].[ProductID] AS [ProductID2], [t3].[UnitPrice] AS [UnitPrice2], [t3].[Quantity], [t3].[Discount], [t4].[OrderID] AS [OrderID2], [t4].[CustomerID], [t4].[EmployeeID], [t4].[OrderDate], [t4].[RequiredDate], [t4].[ShippedDate], [t4].[ShipVia], [t4].[Freight], [t4].[ShipName], [t4].[ShipAddress], [t4].[ShipCity], [t4].[ShipRegion], [t4].[ShipPostalCode], [t4].[ShipCountry], (

    SELECT COUNT(*)

    FROM [dbo].[Order Details] AS [t5]

    INNER JOIN [dbo].[Orders] AS [t6] ON [t6].[OrderID] = [t5].[OrderID]

    WHERE [t5].[ProductID] = [t0].[ProductID]

    ) AS [count], [t2].[test], [t2].[CategoryID] AS [CategoryID2], [t2].[CategoryName], [t2].[Description], [t2].[Picture]

FROM [dbo].[Products] AS [t0]

LEFT OUTER JOIN (

    SELECT 1 AS [test], [t1].[CategoryID], [t1].[CategoryName], [t1].[Description], [t1].[Picture]

    FROM [dbo].[Categories] AS [t1]

    ) AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]

LEFT OUTER JOIN ([dbo].[Order Details] AS [t3]

    INNER JOIN [dbo].[Orders] AS [t4] ON [t4].[OrderID] = [t3].[OrderID]) ON [t3].[ProductID] = [t0].[ProductID]

ORDER BY [t0].[ProductID], [t2].[CategoryID], [t3].[OrderID]

 

0
0

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻