Linq to sql：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>();

 

这样的语句执行后会导致下面的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的关系，所以也只会有1次SQL（不过这样的操作还是少执行为妙，消耗太大了）：

 

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]

 

主键缓存

 

Linq to sql 对查询过的对象进行缓存，之后的如果只根据主键查询一条记录的话会直接

 

从缓存中读取。比如下面的代码：

 

Customer c1 = ctx.Customers.Single(customer =>customer.CustomerID ==

 

"ANATR");

 

       c1.ContactName = "zhuye";

 

Customer c2 = ctx.Customers.Single(customer =>customer.CustomerID ==

 

"ANATR");

 

       Response.Write(c2.ContactName);

 

执行后只会产生一条SQL：

 

SELECT [t0].[CustomerID],[t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],

 

[t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],

 

[t0].[Fax]

 

FROM [dbo].[Customers] AS [t0]

 

WHERE [t0].[CustomerID] = @p0

 

-- @p0: Input String (Size = 5; Prec = 0;Scale = 0) [ANATR]

 

由于没有提交修改，所以数据库中的记录还是没有更新。由于这个特性，我们在使用存

 

储过程作为实体更新方法的时候就要当心了，存储过程书写错误，即使你提交了修改也很可

 

能导致缓存中的数据和数据库中的数据不一致，引起不必要的麻烦。