延迟执行
IQueryable query = from c in ctx.Customers select c; |
这样的查询句法不会导致语句立即执行,它仅仅是一个描述,对应一个 SQL 。仅仅在需要使用的时候才会执行语句,比如:
IQueryable query = from c in ctx.Customers select c; foreach (Customer c in query) Response.Write(c.CustomerID); |
如果你执行两次 foreach 操作,将会捕获到两次 SQL 语句的执行:
IQueryable query = from c in ctx.Customers select c; foreach (Customer c in query) Response.Write(c.CustomerID); foreach (Customer c in query) Response.Write(c.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]
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] |
对于这样的需求,建议你先使用 ToList() 等方法把查询结果先进行保存,然后再对集合进行查询:
IEnumerable <Customer > customers = (from c in ctx.Customers select c).ToList(); foreach (Customer c in customers) Response.Write(c.CustomerID); foreach (Customer c in customers) Response.Write(c.ContactName); |
延迟执行的优点在于我们可以像拼接 SQL 那样拼接查询句法,然后再执行:
var query = from c in ctx.Customers select c; var newquery = (from c in query select c).OrderBy(c => c.CustomerID); |
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 次。这样的查询不是很合理,我们可以通过设置 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 >(); |
这样的语句执行后会导致下面的 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] |
由于没有提交修改,所以数据库中的记录还是没有更新。由于这个特性,我们在使用存储过程作为实体更新方法的时候就要当心了,存储过程书写错误,即使你提交了修改也很可能导致缓存中的数据和数据库中的数据不一致,引起不必要的麻烦。
DataContext 隔离
有的时候我们会把对象从外部传入 DataContext ,要求它更新,由于不同的 DataContext 是相对独立的。由于新的 DataContext 中还没有获取实体,我们只能通过附加方式更新数据。
首先把 Customer 表的主键字段加上 IsVersion 标识:
[Column (Storage="_CustomerID" , DbType="NChar(5) NOT NULL" , CanBeNull=false , IsPrimaryKey=true , IsVersion = true )] |
运行下面的测试代码:
Customer c = new Customer { CustomerID = "ALFKI" , ContactName = "zhuye" , CompanyName = "1111" }; ctx.Customers.Attach(c, true ); ctx.SubmitChanges(); |
会捕捉到下面的 SQL 语句:
UPDATE [dbo].[Customers] SET [CompanyName] = @p2, [ContactName] = @p3, [ContactTitle] = @p4, [Address] = @p5, [City] = @p6, [Region] = @p7, [PostalCode] = @p8, [Country] = @p9, [Phone] = @p10, [Fax] = @p11 WHERE ([CustomerID] = @p0) AND ([CustomerID] = @p1) -- @p0: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [ALFKI] -- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [ALFKI] -- @p2: Input String (Size = 4; Prec = 0; Scale = 0) [1111] -- @p3: Input String (Size = 5; Prec = 0; Scale = 0) [zhuye] -- @p4: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p5: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p6: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p7: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p8: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p9: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p10: Input String (Size = 0; Prec = 0; Scale = 0) [] -- @p11: Input String (Size = 0; Prec = 0; Scale = 0) [] |
今天就到这里,下次讲并发与事务问题。