A group blog from members of the VB team
This post assumes you’ve read the previous posts in this series:
Converting SQL to LINQ, Part 1: The Basics
Converting SQL to LINQ, Part 2: FROM and SELECT
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
This post will discuss UNION, TOP and Subqueries. Next week, I plan to cover LEFT, RIGHT and FULL OUTER JOIN more fully. If there are additional topics you’d like to see discussed related to converting SQL to LINQ, please add a comment to this post.
UNION
In SQL, a UNION clause joins the results of two SELECT queries into one set of data. With VB LINQ, the Union method can be called on a query, and passed a second query to produce the same result. The Intersect method is also available, and returns the common elements of the two query results. The Except method returns all the results from the first query that don’t appear as results from the second query.
SQL
SELECT CustomerID ID FROM CustomerTable
SELECT OrderID ID From OrderTable
VB
(From Contact In CustomerTable _
Select ID = Contact.CustomerID).Union(From Shipment In OrderTable _
Select ID = Shipment.OrderID)
TOP
The SQL TOP operator returns the first n results of a query. A Take clause can accomplish the same thing in a VB LINQ expression. Take is described in more detail below, along with some related clauses.
SELECT TOP 10 * FROM CustomerTable ORDER BY CustomerID
From Contact In CustomerTable Order By Contact.CustomerID Take 10
Take/Skip/While
The Take clause is applied to the results of the clause that precedes it, and specifies a number of results to “take”, or return. All additional results are disregarded.
The Skip clause specifies a number of results to be ignored at the “top” of a query result. The results of the preceding clause are passed in, and all but the first n results are returned.
The Take While clause specifies a condition, and takes results from the start of a query result until the condition evaluates to false.
The Skip While clause specifies a condition, and skips results from the start of a query result until the condition evaluates to be false.
To give concrete examples, the following queries returns the following result:
Dim digits = New Integer() {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
Dim AllDigits = From int In digits
Results:
0 3 6 9
1 4 7
2 5 8
SKIP
Dim SkipFirstTwo = From int In digits Skip 2
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
5 8
6 9
TAKE WHILE
Dim TakeUntilThree = From int In digits _
Take While int = 0 OrElse int Mod 3 <> 0
0 2
Subqueries
SQL SELECT statements can also have subqueries, where a query uses the results of another query. A VB LINQ expression can contain a subquery anywhere an expression is allowed, and like a SQL subquery, you will want to use parentheses to avoid ambiguities in the syntax.
SELECT OrderID, OrderDate
FROM OrderTable
WHERE CustomerID = (SELECT CustomerID
FROM CustomerTable
WHERE City = “Seattle”)
From Shipment In OrderTable _
Where (From Contact In CustomerTable _
Where Contact.City = “Seattle” _
Select Contact.CustomerID).Contains(Shipment.CustomerID) _
Select Shipment.OrderID, Shipment.OrderDate
Also note that because a query expression returns an IEnumerable, you can also query over query results:
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 _
I’m interested to hear your topic ideas. Next time, I’ll talk more about LEFT/RIGHT/FULL OUTER JOIN.
- Bill Horst, VB IDE Test
PingBack from http://geeklectures.info/2008/01/08/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst/
Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el
ASP.NET Web Development Toolbox [Via: Chris Brandsma ] WPF WPF/Xaml Web News - 2008/01/08 [Via: rrelyea...
Thank you for showing not only how to write a LINQ statement with a particular clause, but also the results. That helps with understanding what to expect and what it is doing.
Pingback from http://oakleafblog.blogspot.com/2008/01/linq-and-entity-framework-posts-for.html
ここでは、このシリーズの前の投稿をお読みになっていることを前提としています。 Converting SQL to LINQ, Part 1: The Basics ( 英語 ) Converting SQL
Nice Articles..
Is there any keyword for Bottom 10 records
The best thing to do would be to sort in the opposite direction and still use the Take operator:
dim q = from row in db.customers order by row.companyid descending take 10
Hope that helps!
Jonathan