从 SQL 到 LINQ, 第7部分: 合并, TOP, 子查询 (Bill Horst)

[原文作者]Bill Horst
[原文地址]Converting SQL to LINQ, Part 7: UNION, TOP, Subqueries (Bill Horst)

    本文假设您已阅读了本系列中此前发表的文章:

          SQL LINQ, Part 1: 基础(Bill Horst)

          SQL LINQ, Part 2: FROM SELECT(Bill Horst)

          Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators

          Converting SQL to LINQ, Part 4: Functions

Converting SQL to LINQ, Part 5: GROUP BY and HAVING

Converting SQL to LINQ, Part 6: Joins

 

    本文中我们将讨论一下合并, TOP 和子查询. 下个星期, 我打算更深入地介绍一下LEFT, RIGHT FULL OUTER JOIN. 如果你想了解关于转换SQLLINQ的其他问题, 请回复本文.

 

合并(UNION)

 

SQL, 一个UNION子句合并两个SELECT查询结果为一个数据集. VB LINQ, 可以通过在一个查询中调用Union方法, 并把第二个查询传递给这个方法可以得到相同的结果. Intersect方法也一样可以这样调用, 它返回的是两个查询的交集. Except方法返回的结果只包括那些在第一个查询中出现但不在第二个查询中出现记录.

 

SQL

SELECT CustomerID ID FROM CustomerTable

UNION

SELECT OrderID ID From OrderTable

 

 

VB

(From Contact In CustomerTable _

 Select ID = Contact.CustomerID).Union(From Shipment In OrderTable _

                                       Select ID = Shipment.OrderID)

 

 

TOP

 

SQLTOP运算符返回一个查询结果的前n. VB LINQ中的Take子句可以达到同样效果. 下面的例子详细描述了Take的功能, 同时使用了一些其他相关的子句.

 

SQL

SELECT TOP 10 * FROM CustomerTable ORDER BY CustomerID

 

 

VB

From Contact In CustomerTable Order By Contact.CustomerID Take 10

 

 

Take/Skip/While

 

    Take子句作用于位于它前面的子句, 并指定一个表示要 或返回多少条结果的数字. 查询结果中其他的记录会被丢弃.

 

    Skip子句指定了一个数字, 表示要忽略查询结果中 多少条记录. 当前面子句的结果被传递给Skip子句后, 它只返回除了前n条记录外的其他结果.

 

    Take While子句指定一个条件, 并从一个查询结果的开头获取记录, 直到这个条件为False. 

 

    Skip While子句指定一个条件, 并从一个查询结果的开头获取记录, 它会忽略前面的记录, 直到这个条件为False.

 

   举几个具体的例子, 下面的几个查询返回如下的结果:

 

VB

 

Dim digits = New Integer() {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}

 

Dim AllDigits = From int In digits

 

返回结果:

        0        3        6        9

        1        4        7

        2        5        8

 

 

SKIP

 

Dim SkipFirstTwo = From int In digits Skip 2

 

返回结果:

        2        5        8

        3        6        9

        4        7

 

 

TAKE

 

Dim TakeFirstTwo = From int In digits Take 2

 

返回结果:

        0

        1

 

 

SKIP and TAKE together

 

Dim SkipTwoTakeFive = From int In digits Skip 2 Take 5

 

返回结果:

        2        5

        3        6

        4

 

 

 

SKIP WHILE

 

Dim SkipUntilFour = From int In digits Skip While int Mod 5 <> 4

 

返回结果:

        4        7

        5        8

        6        9

 

 

TAKE WHILE

 

Dim TakeUntilThree = From int In digits _

                     Take While int = 0 OrElse int Mod 3 <> 0

 

返回结果:

        0        2

        1

 

 

子查询

 

    SQLSELECT语句可以包含子查询, 就是指一个查询使用了另一个查询的结果. VB LINQ, 子查询可以出现在任何允许使用LINQ表达式的地方, SQL子查询一样, 你可以使用括号来避免语法中的歧义.

 

SQL

SELECT OrderID, OrderDate

FROM OrderTable

WHERE CustomerID = (SELECT CustomerID

                    FROM CustomerTable

                    WHERE City = “Seattle”)

 

 

VB

From Shipment In OrderTable _

Where (From Contact In CustomerTable _

       Where Contact.City = “Seattle” _

       Select Contact.CustomerID).Contains(Shipment.CustomerID) _

Select Shipment.OrderID, Shipment.OrderDate

 

 

    还要注意的是, 因为一个查询返回的是IEnumerable, 所以你还可以对查询结果进行查询:

 

VB

Dim SeattleOrders = From Contact In CustomerTable _

                    Join Shipment In OrderTable _

                    On Contact.CustomerID Equals Shipment.CustomerID _

                    Where Contact.City = “Seattle”

 

Dim FilteredOrders = From Shipment In SeattleOrders _

                     Select Shipment.OrderID, Shipment.OrderDate

 

 

我很有兴趣知道你们对主题的看法. 下次, 我会仔细地讨论一下LEFT/RIGHT/FULL OUTER JOIN.

      Bill Horst, VB IDE Test

从 SQL 到 LINQ, 第6部分: 联接 (Bill Horst)

[原文作者]Bill Horst

[原文链接]Converting SQL to LINQ, Part 6: Joins (Bill Horst)

 

本文假设您已阅读了本系列中此前发表的文章:

          SQL LINQ, Part 1: 基础(Bill Horst)

          SQL LINQ, Part 2: FROM SELECT(Bill Horst)

          Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators

          Converting SQL to LINQ, Part 4: Functions

          Converting SQL to LINQ, Part 5: GROUP BY and HAVING

 

本文将讨论一下交叉联接Cross Join, 内联接Inner Join, 自然联接Natural Join 和外(/)联接 Outer (Left/Right) Join.

 

联接

 

在一个SQL SELECT语句中查询一个以上的数据集(例如:)是比较常见的. 把多个表中的信息集中在一起就叫做联接, 而在SQLLINQ中有好几种不同的联接.

 

交叉联接

 

最简单的连接是交叉联接, 又叫笛卡尔积, 是针对两个数据集合的多对多的联接, 一个数据集中的每一条记录与另一个数据集中的每条记录都进行连接. 在一个SQL SELECT语句中, 要实现交叉联接只要在FROM子句中指定一个以上的表就可以了. VB LINQ, 可以用相同的方式实现, 如下所示:

 

SQL

SELECT CustomerTable.Name, OrderTable.OrderDate

FROM CustomerTable, OrderTable

 

 

VB

From Contact In CustomerTable, Shipment In OrderTable _

Select Contact.Name, Shipment.OrderDate

 

 

内联接

 

内联接是一对一的联接, 一个数据集中的记录根据两个数据集中特定的相同字段与另一个数据集中的记录做匹配. 在一个SQL SELECT语句中, 第二个数据集在INNER JOIN子句中指定, 匹配条件在ON子句中指定. 与此类似, VB LINQ联接中, 第二个数据集在Join中指定, On子句则用来指定Equals运算符与哪个字段相匹配.

 

SQL

SELECT Contact.Name, Shipment.OrderID

FROM CustomerTable Contact

INNER JOIN OrderTable Shipment

ON Contact.CustomerID = Shipment.CustomerID

AND Contact.Zip = Shipment.ShippingZip

 

 

VB

From Contact In CustomerTable

Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

And Contact.Zip Equals Shipment.ShippingZip _

Select Contact.Name, Shipment.OrderID

 

 

上面的例子是一个对等联接, 意味着用一个相等运算符联接两个表的信息. 这是On子句中唯一允许使用的运算符, 所以要想实现其他的联接运算符(例如:小于), 你需要使用一个带Where子句的交叉联接.

 

SQL

SELECT Contact.Name, Shipment.OrderID

FROM CustomerTable Contact

INNER JOIN OrderTable Shipment

ON Contact.CustomerID < Shipment.CustomerID

 

 

VB

From Contact In CustomerTable, Shipment In OrderTable _

Where Contact.CustomerID < Shipment.CustomerID

Select Contact.Name, Shipment.OrderID

 

 

自然联接

 

自然联接也是一对一的联接, 在自然联接中一个数据集中的记录依据两个数据集中所有的相同字段(具有相同的名称)与另一个表中的记录做匹配. 在一个SQL SELECT语句中, 第二个数据集可以在NATURAL JOIN子句中指定, 而用来联接的匹配条件是隐含的. VB LINQ中没有和自然联接直接对应的语法, 所以要实现它的最好方法是创建一个内联接并在On子句中手动指定所有相同字段的相等关系. SQL的自然联接相比, 这显得有点冗长, 但理解起来比较容易.

 

SQL

SELECT * FROM CustomerTable

NATURAL JOIN OrderTable

 

 

VB

From Contact In CustomerTable _

Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _
And Contact.Phone Equals Shipment.Phone

 

 

(/)联接

 

外联接(也被称为左联接或右联接), 是一种一对多的联接, 一个数据集中的每条记录根据两个数据集的相同字段可以与另一个数据集中的多条记录相匹配. 在一个SQL SELECT语句中, 第二个数据集在LEFT JOINRIGHT JOIN子句中指定, 用来联接两个数据集的匹配条件在ON 子句中指定. LEFT JOIN, 第一个()数据集中的每条记录根据匹配条件与第二个表中所有满足匹配条件的记录相联接. 第一个数据集中的所有记录都会出现在结果集中, 不论第二个数据集中有没有与之匹配的记录. 这与RIGHT JOIN相反, 在外联接中所有第二个()数据集中的记录都会出现在结果集中, 并尽可能与第一个数据集中的任何匹配的记录相联接.

 

VB LINQ, 与外连接最相似的结构是分组联接. 在分组联接中, Group Join子句指定了第二个数据集, 并在On子句中提供匹配条件, 这与本文前面描述的联接(内联接)很相像. VB LINQ中还有一个Into子句, 它可以用来指定对每个分组执行什么样的集合运算, 就像是在一个像前面Group Join那样的子句中.

 

SQL LEFT JOIN类似, 分组连接中第一个数据集的每个成员, 与第二个数据集中所有和它相匹配的记录相联接. 再次提醒一下, 如果第二个数据集中没有任何记录与第一个数据集中的某条记录相匹配, 这条记录仍然会出现在结果集中.

 

SQL

SELECT CustomerTable.Name, SUM(OrderTable.Cost) Sum

FROM CustomerTable

LEFT JOIN OrderTable

ON CustomerTable.CustomerID = OrderTable.CustomerID

GROUP BY CustomerTable.Name

 

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

Into Sum(Shipment.Cost) _

Select Contact.Name, Sum

 

 

实现RIGHT JOIN的最好方式是利用这个Group Join子句, 但需要颠倒其中的表(把后一个表作为第一个).

 

一个比较常见的情况是, 需要使用外联接但不需要进行集合运算. 像上面的示例那样使用Group关键字, 会导致结果被分为一连串大小不等的分组, 下面这个例子可能是VB LINQ中与不需要集合运算的外联接最类似的语法了.

 

SQL

SELECT *

FROM CustomerTable Contact

LEFT JOIN OrderTable Shipment

ON Contact.CustomerID = Shipment.CustomerID

 

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

Into Group

 

 

这些分组联接的例子可能没有涵盖你可能会遇到的LEFT JOIN RIGHT JOIN 的所有情况, 但相信它们会给你一个创建与现有SQL查询相对应的LINQ查询的基本思路.

 

下个星期, 我打算做一个百宝袋”, 主题包括合并(UNION), TOP 和子查询.

 

如果你有任何其他的SQL SELECT语句想要转换到LINQ, 请为本文添加一个关于它回复.

 

      Bill Horst, VB IDE 测试

从SQL 到LINQ, 第5部分: GROUP BY 和 HAVING (Bill Horst)

[原文作者]Bill Horst
[
原文地址]Converting SQL to LINQ, Part 5: GROUP BY and HAVING (Bill Horst)

    本文假定您已阅读过本系列中此前发表的文章:

          SQL LINQ, Part 1: 基础(Bill Horst)

          SQL LINQ, Part 2: FROM SELECT(Bill Horst)

          Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators

          Converting SQL to LINQ, Part 4: Functions

 

    本文将讨论GROUP BYHAVING子句.

 

GROUP BY

 

    一个SQL GROUP BY 子句使你能够依据特定的字段把记录分组, 从而可以一次处理整个分组.   一个LINQ语句也可以包含一个Group By子句, 但语法有所不同. 下面是一个不正规 (也不完整) 的语法表达式:

 

VB Group By

Group [{optional list of fields}] By {list of fields} _

Into {list of aggregate expressions}

 

 

在这个语法中列出来的所有表达式都可以有别名, 并且当一个标识不能被确定时必须使用别名. 一般说来, 子句首先是一个”Group By”, 其后有一个用来分组的字段的列表; 接着是一个 ”Into”, 其后是要进行集合计算的集合表达式. 例如, 下面的查询计算发送到每个邮政编码的总运输费用和平均运输费用:

 

VB

From Shipment In OrderTable _

Group By Shipment.ShippingZip _

Into Total = Sum(Shipment.Cost), Average(Shipment.Cost)

 

    这个查询为数据表中存在的每个邮政编码返回3个值: 邮政编码, 运输费用总计和平均运输费用.

 

    可选地, “Group” “By” 关键字之间可以指定字段来减少某些字段中包含的信息. 这可以被看作是一个内置的, Into子句之前的Select子句:

 

VB

From Shipment In OrderTable _

Group OrderCost = Shipment.Cost By Shipment.ShippingZip _

Into Total = Sum(OrderCost), Average(OrderCost)

 

作为对集合表达式的补充, Into子句也可以包含 “Group” 关键字, 它的作用是使分组中的每个记录成为一个数组成员. 下面这个查询为数据中存在的每个邮政编码和订货日期的组合返回两组值, 邮政编码和订货日期, 加上一个包含分组中所有记录的数组.

 

VB

From Shipment In OrderTable _

Group By Zip = Shipment.ShippingZip, Shipment.OrderDate _

Into Group

 

如果“Group” By之间没有指明字段, 如上所示, 则返回的数组中包含所有字段. 下面这个查询为数据中存在的每个邮政编码和订货日期的组合, 返回分组中每条记录的邮政编码和订货日期, 以及ID和支出两个字段组成的数组.

 

VB

From Shipment In OrderTable _

Group ID = Shipment.OrderID, Shipment.Cost By _

    Zip = Shipment.ShippingZip, Shipment.OrderDate _

Into Group

 

    因为语法确实很复杂, 我已给出了好几个例子, 现在我们开始看一下如何转换各种包含GROUP BY 子句的SQL语句到VB. 下面是一个例子:

 

SQL

SELECT OrderDate Date_Of_Order, SUM(Cost) Daily_Total

FROM OrderTable

GROUP BY Date_Of_Order

 

 

VB

From Shipment In OrderTable _

Group By Date_Of_Order = Shipment.OrderDate _

Into Daily_Total = Sum(Shipment.Cost)

 

 

Having

 

    Having 是另一个可以在查询结果中指定分组条件的SQL子句. VB中没有类似的子句, 所以用VB LINQ实现它的最好的方式是在Group By子句后使用一个Where子句, 如下所示:

 

SQL

SELECT OrderDate Date_Of_Order, SUM(Cost) Total_Cost

FROM OrderTable

GROUP BY Date_Of_Order

HAVING SUM(Cost) > 1000

 

 

VB

From Shipment In OrderTable _

Group By Date_Of_Order = Shipment.OrderDate _

Into Total_Cost = Sum(Shipment.Cost)

Where Total_Cost > 1000

 

 

    在下一篇文章中, 我打算讨论各种各样的联接.

– Bill Horst, VB IDE Test

Visual Basic 2008发布了!

[原文作者]Amanda Silver

[原文链接]Visual Basic 2008 Ships!!! (Amanda Silver)

 

太好了!今天我们终于发布了Visual Studio 2008, 就是以前所说的Orcas,也是作为一个没有ASCII表示ASCII representation)的符号。我们走过了漫长的路,但这是一件伟大的事情!Soma在自己的博客中高屋建瓴地介绍了VS2008的很多足以让每一个开发员和IT主管都很向往的旗舰功能,不过在这篇文章中我们主要讨论Visual Basic

对于各个行业的Visual Basic用户来说,这是一个意义重大的发布,因为我们第一次把查询表达式(query expressions)和XML作为第一类数据类型。这不但使Visual Basic在开发以数据为中心的应用程序时更加有效,而且让VB程序员成为很多喜爱尝试新领域的科技高手(alpha-geek)羡慕的对象。如果你想体验一下,就去下载一个Visual Basic Express, 它拥有以上谈到的所有功能。

因为query,整个.NET Framework变成了一个可查询的数据源。现在你可以查询.NET Framework中任何有效的东西——注册表、文件系统、进程等等。例如:下面的代码查询运行的进程,返回线程数大于10的进程,并按线程数排序:

        Dim query = From proc In Process.GetProcesses _

            Let ThreadCount = proc.Threads.Count _

            Where ThreadCount > 10 _

            Order By ThreadCount _

                        Select proc.ProcessName, proc.Id, ThreadCount

有了LINQSQL的对象关系映射框架,我们就可以把SQL数据库当作由Framework暴露的对象集合来查询。下面的查询找到所有被停止的产品,然后按种类分组,并且找出每种产品的库存总量

        Dim db As New NorthwindDataContext(My.Settings.NORTHWNDConnectionString)

        Dim query = From prod In db.Products _

                    Where prod.Discontinued = True _

                    Group By prod.CategoryID _

                    Into Count(), NumInStock = Sum(prod.UnitsInStock) _

                    Join cat In db.Categories On cat.CategoryID Equals CategoryID _

                                        Select cat.CategoryName, Count, NumInStock

 

我可以从上面这个查询得到即时反馈,再也不用写冗长的SQL查询字符串了。

智能提示:

语法检查:

 结构检查:

我们可以用VB查询关系数据库、XML和对象集合,根本不用学习任何领域专用语言,VB让我们可以访问任何东西!这样一来,我们就很很容易进行跨领域操作。(我会在之后的博客文章中提到这些)

XML作为第一类数据类型的同时,Visual Basic变成了完全的XML转换语言。对我来说,从数据库到XML做项目太容易了

        Dim query = From prod In db.Products _

            Where prod.Discontinued = True _

            Group By prod.CategoryID _

            Into prodGroup = Group, NumInStock = Sum(prod.UnitsInStock) _

            Join cat In db.Categories On cat.CategoryID Equals CategoryID _

            Select <Products>

                       <Category CategoryID=<%= CategoryID %> CategoryName=<%= cat.CategoryName %>>

                           <%= From prod In prodGroup _

                               Select <Product Price=<%= prod.UnitPrice %>><%= prod.ProductName %></Product> %>

                       </Category>

                   </Products>

如果你对LINQ(Language Integrated Query)不感兴趣,没关系,Visual Basic 2008还有其他的优点:强大的智能提示体验。我们的一个MVP说他写代码是只需要输入两个字母,按一下Tab键,两个字母,一下Tab …… 仿佛代码是自己写出来的!

诚然,我们对于这个发布十分激动,并希望它能为你带来更高的生产力!如果你想进一步研究它、了解更多功能,请参考Learning PagesHow-Do-I videos.

从SQL 到 LINQ, Part 4: 函数 (Bill Horst)

[原文作者]: Bill Horst

[原文链接]: Converting SQL to LINQ, Part 4: Functions (Bill Horst)

 

在看这篇文章之前,我假定你已经读过了:

SQLLINQPart 1:基础

SQLLINQPart 2FROMSELECT

SQLLINQPart 3DISTINCT, WHERE, ORDER BY 操作符

 

这篇文章讨论的是标量函数(scalar functions)和聚合函数(aggregate functions)。

 

函数

SQLSELECT子句经常会涉及到函数,包含标量函数和聚合函数。一个聚合函数针对的是select出来的一组记录,而标量函数针对的则是其中的个体。我们可以利用VB LINQ表达式实现这两种操作-但是,方法有所不同。

 

标量函数(Scalar Functions

标量函数不论参数如何操作的都是单个记录。他们可以在SQL查询的不同位置出现,比如SELCET子句。虽然标量函数会根据系统的不同而有所不同,但是我们总可以使用类似的VB方法在LINQ语句中进行模拟。如果在LINQSelect子句中使用标量函数,你可能也需要指定一个别名(FirstThreeLetters, CurrentTime)。

 

SQL

SELECT LEFT(ItemName, 3) FirstThreeLetters, NOW() CurrentTime

FROM OrderTable

 

 

VB

From Shipment In OrderTable _

Select FirstThreeLetters = Left(Shipment.ItemName, 3), CurrentTime = Now

 

 

在上面的例子中,LeftNowVB已有的方法(在Microsoft.VisualBasic.dll里定义)。其实大多数SQL支持的标量方法都可以在VB中找到,即使你想调用的方法在VB里还不存在,你可以自己去定义它。但是,用户自定义的方法不能在数据库查询中使用,在运行时系统会抛出异常。在下面的例子中,MyFunction就是一个自定义的方法。

 

VB

From Shipment In OrderTable _

Select MyFunction(Shipment.Cost, Shipment.ShippingZip)

 

 

聚合函数(Aggregate Functions

聚合函数操作的是整个集合,并返回一个值。在SQL语句中,他们可以出现在SLECT子句里。而在LINQ中,情况有所不同。

 

一个VB LINQ表达式通常由From子句开始,但是,也可以是Aggregate子句。Aggregate子句和From子句语法一样,一个由Aggregate开始的子句必须以Into子句结束。Into子句是一组带别名的,由逗号分割的,集合函数调用的列表(a comma-delimited list of Aggregate function calls, with aliases that can accompany them)。下面的例子展示了在SQLSELECT语句中如何使用集合函数,以及其在LINQ中的等价操作。

 

SQL

SELECT SUM(Cost) TotalCost, COUNT(*)

FROM OrderTable

WHERE OrderDate > “Sept-29-2007”

 

 

VB

Aggregate Shipment In OrderTable _

Where Shipment.OrderDate > #9/29/2007# _

Into TotalCost = Sum(Shipment.Cost), Count()

 

 

下次,我们讨论GROUP BY HAVING