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 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
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.
|
SQL |
|
SELECT TOP 10 * FROM CustomerTable ORDER BY CustomerID |
|
VB |
|
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:
|
VB |
|
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 |
|
Results: 2 5 8 3 6 9 4 7 |
|
TAKE Dim TakeFirstTwo = From int In digits Take 2 |
|
Results: 0 1 |
|
SKIP and TAKE together Dim SkipTwoTakeFive = From int In digits Skip 2 Take 5 |
|
Results: 2 5 3 6 4 |
0 comments