vainnetwork的专栏

共同学习进步

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

本系列课程均转自

LoveCherry

技术无极限http://www.cnblogs.com/lovecherry/archive/2007/08/14/855681.html

版权归LoveCherry所有

 

延迟执行

 

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

 

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]

 

主键缓存

 

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

      

今天就到这里,下次讲并发与事务问题。

阅读更多
个人分类: LINQ系列学习
想对作者说点什么? 我来说一句

LINQ中文教程

2013年02月19日 876KB 下载

LINQ中文教程word格式

2010年12月22日 855KB 下载

LINQ详细教程

2014年01月28日 865KB 下载

VS2008 LINQ基础教程

2011年03月07日 858KB 下载

LINQ中文教程LINQ中文教程

2011年03月22日 833KB 下载

一步一步Linq to sql

2010年01月29日 850KB 下载

LINQ中文教程(WORD格式)

2010年08月07日 786KB 下载

一步一步LINQ to sql

2009年07月27日 2.64MB 下载

LINQ中文系列教程

2010年10月16日 406KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭