LINQ学习笔记(二)--感受篇二

通过前面MikeTaulty的讲解,我对LINQ的使用有个大概的感观认识,下面再来看看Scottgu在asp.net中的例子.(其实LINQ只和后台有关,很表现层没有关系的)

 

Demo1:

 

1.       同样地我们在创建网站后,添加一个dbml文件NorthwindDemo1.dbml(dbml是Database Markup Language,是一个标准的XML文件,其实可以把他看作是一个生成映射的中间文件,因为我们生成的实体类时候,有的是VB,是C#,或者其他的)

 

用VS自带的可视化的O/R Mapping工具,可以通过简单的拖拉,生成dbml.,demo1的结构如下:

 

 

dbml文件源码如下:

 

<? xml version="1.0" encoding="utf-16" ?>
< Database  Name ="E:VS2008LINQ学习TESTCODELINQTEST2_WEB_SCOTTGUAPP_DATANORTHWND.MDF"  Class ="NorthwindDataContext"  xmlns ="http://schemas.microsoft.com/linqtosql/dbml/2007" >
   < Connection  Mode ="WebSettings"  ConnectionString ="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"  SettingsObjectName ="System.Configuration.ConfigurationManager.ConnectionStrings"  SettingsPropertyName ="NORTHWNDConnectionString"  Provider ="System.Data.SqlClient"   />
   < Table  Name ="dbo.Categories"  Member ="Categories" >
     < Type  Name ="Categories" >
       < Column  Name ="CategoryID"  Type ="System.Int32"  DbType ="Int NOT NULL IDENTITY"  IsPrimaryKey ="true"  IsDbGenerated ="true"  CanBeNull ="false"   />
       < Column  Name ="CategoryName"  Type ="System.String"  DbType ="NVarChar(15) NOT NULL"  CanBeNull ="false"   />
       < Column  Name ="Description"  Type ="System.String"  DbType ="NText"  CanBeNull ="true"  UpdateCheck ="Never"   />
       < Column  Name ="Picture"  Type ="System.Byte[]"  DbType ="Image"  CanBeNull ="true"  UpdateCheck ="Never"   />
       < Association  Name ="Categories_Products"  Member ="Products"  OtherKey ="CategoryID"  Type ="Products"   />
     </ Type >
   </ Table >
   < Table  Name ="dbo.Products"  Member ="Products" >
     < Type  Name ="Products" >
       < Column  Name ="ProductID"  Type ="System.Int32"  DbType ="Int NOT NULL IDENTITY"  IsPrimaryKey ="true"  IsDbGenerated ="true"  CanBeNull ="false"   />
       < Column  Name ="ProductName"  Type ="System.String"  DbType ="NVarChar(40) NOT NULL"  CanBeNull ="false"   />
       < Column  Name ="SupplierID"  Type ="System.Int32"  DbType ="Int"  CanBeNull ="true"   />
       < Column  Name ="CategoryID"  Type ="System.Int32"  DbType ="Int"  CanBeNull ="true"   />
       < Column  Name ="QuantityPerUnit"  Type ="System.String"  DbType ="NVarChar(20)"  CanBeNull ="true"   />
       < Column  Name ="UnitPrice"  Type ="System.Decimal"  DbType ="Money"  CanBeNull ="true"   />
       < Column  Name ="UnitsInStock"  Type ="System.Int16"  DbType ="SmallInt"  CanBeNull ="true"   />
       < Column  Name ="UnitsOnOrder"  Type ="System.Int16"  DbType ="SmallInt"  CanBeNull ="true"   />
       < Column  Name ="ReorderLevel"  Type ="System.Int16"  DbType ="SmallInt"  CanBeNull ="true"   />
       < Column  Name ="Discontinued"  Type ="System.Boolean"  DbType ="Bit NOT NULL"  CanBeNull ="false"   />
       < Association  Name ="Categories_Products"  Member ="Categories"  ThisKey ="CategoryID"  Type ="Categories"  IsForeignKey ="true"   />
     </ Type >
   </ Table >
</ Database >

 

我们可以看到,里面的内容也很好理解,主要标记了要生成的表和字段的一些属性和关系,现在主要是感受使用,就先不考究他了.

 

2.       我们在页面中添加一个GridView做数据输出,后台我们只是做一个简单的select,然后绑定输出,在Page_Load中添加如下代码:

NorthwindDataContext NwCtx  =   new  NorthwindDataContext();

var query  =  from p  in  NwCtx.Products
                  select p;

// 在这里我们可以看出,其实这个query返回的是一个数据集,可以作为数据源绑定输出
GridViewDemo1.DataSource  =  query;
GridViewDemo1.DataBind();

 

 

结果输出:

 

 

后台执行的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 ]

 

当然我可以加上查询的条件:

如:

var query  =  from p  in  NwCtx.Products
                     where  p.Categories.CategoryName.StartsWith( " C " )
                    select p;

又如:

var query  =  from p  in  NwCtx.Products
                     where  p.Categories.Products.Count  >   5
                    select p;

 当然我们还可以重编输出的内容和加上一些运算输出,如:   

var query  =  from p  in  NwCtx.Products
                     where  p.Categories.Products.Count  >   5
                    select  new
                     ... {
                        p.ProductID,
                        p.ProductName
                    } ;

 

又如:

var query  =  from p  in  NwCtx.Products
                     where  p.Categories.Products.Count  >   5
                    select  new
                     ... {
                        产品ID = p.ProductID,
                        产品名字 = p.ProductName,
                        总数 = p.Order_Details.Sum(o => o.Quantity),
                        总收入 = p.Order_Details.Sum(o => o.Quantity * o.UnitPrice)
                    } ;

 

query运行的SQL:

SELECT   [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] , (
     SELECT   SUM ( CONVERT ( Int , [ t3 ] . [ Quantity ] ))
     FROM   [ dbo ] . [ Order Details ]   AS   [ t3 ]
     WHERE   [ t3 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
    )  AS   [ value ] , (
     SELECT   SUM (( CONVERT ( Decimal ( 29 , 4 ), [ t4 ] . [ Quantity ] ))  *   [ t4 ] . [ UnitPrice ] )
     FROM   [ dbo ] . [ Order Details ]   AS   [ t4 ]
     WHERE   [ t4 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
    )  AS   [ value2 ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
WHERE  ((
     SELECT   COUNT ( * )
     FROM   [ dbo ] . [ Categories ]   AS   [ t1 ] ,  [ dbo ] . [ Products ]   AS   [ t2 ]
     WHERE  ( [ t1 ] . [ CategoryID ]   =   [ t0 ] . [ CategoryID ] )  AND  ( [ t2 ] . [ CategoryID ]   =   [ t1 ] . [ CategoryID ] )
    ))  >   5

 

注意,此时要在dbml里面加入两张表,不然会出错哦:

 

写着这些语句,刚开始我有些奇怪,为什么不直接按着T-SQL的习惯,把Select语句写到前面呢?查了一下资料,据说是MS为了IDE的智能感应而作出的变化,因为当我们没有写from语句前,是很难知道要select的到底是什么类型,那么就很难能做到代码提醒了,呵呵.

 

刚才说到query返回的是一个数据集,那么我们就可以对他进行进一步的抽取数据,例如我们可以利用LINQ提供的匿名类对它进行一些操作:

如支取前面十条数据:

GridViewDemo1.DataSource  =  query.Take( 10 );

 

结果:

又如取11到20的数据:

只要简单的加上Skip就可以了,当中有个原则,每次返回都是一个数据集,而再下一层操作都是在上一层数据集里面再抽取一个数据集.

GridViewDemo1.DataSource  =  query.Skip( 10 ).Take( 10 );

 

结果:

 

看看使用SQL的变化,就更容易理解这一点:

单纯的query:

SELECT   [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] , (
     SELECT   SUM ( CONVERT ( Int , [ t3 ] . [ Quantity ] ))
     FROM   [ dbo ] . [ Order Details ]   AS   [ t3 ]
     WHERE   [ t3 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
    )  AS   [ value ] , (
     SELECT   SUM (( CONVERT ( Decimal ( 29 , 4 ), [ t4 ] . [ Quantity ] ))  *   [ t4 ] . [ UnitPrice ] )
     FROM   [ dbo ] . [ Order Details ]   AS   [ t4 ]
     WHERE   [ t4 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
    )  AS   [ value2 ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
WHERE  ((
     SELECT   COUNT ( * )
     FROM   [ dbo ] . [ Categories ]   AS   [ t1 ] ,  [ dbo ] . [ Products ]   AS   [ t2 ]
     WHERE  ( [ t1 ] . [ CategoryID ]   =   [ t0 ] . [ CategoryID ] )  AND  ( [ t2 ] . [ CategoryID ]   =   [ t1 ] . [ CategoryID ] )
    ))  >   5

 

加上skip和take:

SELECT   TOP   10   [ t6 ] . [ 产品ID ] ,  [ t6 ] . [ 产品名字 ] ,  [ t6 ] . [ 总数 ] ,  [ t6 ] . [ 总收入 ]
FROM  (
     SELECT  ROW_NUMBER()  OVER  ( ORDER   BY   [ t3 ] . [ ProductID ] ,  [ t3 ] . [ ProductName ] ,  [ t3 ] . [ value ] ,  [ t3 ] . [ value2 ] )  AS   [ ROW_NUMBER ] ,  [ t3 ] . [ ProductID ]   AS   [ 产品ID ] ,  [ t3 ] . [ ProductName ]   AS   [ 产品名字 ] ,  [ t3 ] . [ value ]   AS   [ 总数 ] ,  [ t3 ] . [ value2 ]   AS   [ 总收入 ]
     FROM  (
         SELECT   [ t0 ] . [ ProductID ] ,  [ t0 ] . [ ProductName ] , (
             SELECT   SUM ( CONVERT ( Int , [ t1 ] . [ Quantity ] ))
             FROM   [ dbo ] . [ Order Details ]   AS   [ t1 ]
             WHERE   [ t1 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
            )  AS   [ value ] , (
             SELECT   SUM (( CONVERT ( Decimal ( 29 , 4 ), [ t2 ] . [ Quantity ] ))  *   [ t2 ] . [ UnitPrice ] )
             FROM   [ dbo ] . [ Order Details ]   AS   [ t2 ]
             WHERE   [ t2 ] . [ ProductID ]   =   [ t0 ] . [ ProductID ]
            )  AS   [ value2 ] ,  [ t0 ] . [ CategoryID ]
         FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
        )  AS   [ t3 ]
     WHERE  ((
         SELECT   COUNT ( * )
         FROM   [ dbo ] . [ Categories ]   AS   [ t4 ] ,  [ dbo ] . [ Products ]   AS   [ t5 ]
         WHERE  ( [ t4 ] . [ CategoryID ]   =   [ t3 ] . [ CategoryID ] )  AND  ( [ t5 ] . [ CategoryID ]   =   [ t4 ] . [ CategoryID ] )
        ))  >   5
    )  AS   [ t6 ]
WHERE   [ t6 ] . [ ROW_NUMBER ]   >   10