Converting SQL to LINQ, Part 7: UNION, TOP, Subqueries (Bill Horst)

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