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 (