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

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

  • Comments 10

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

 

 

 

SKIP WHILE

 

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

 

Results:

        4        7

        5        8

        6        9

 

 

TAKE WHILE

 

Dim TakeUntilThree = From int In digits _

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

 

Results:

        0        2

        1

 

 

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.

 

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

 

 

Also note that because a query expression returns an IEnumerable, you can also query over query results:

 

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

 

 

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

Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
Page 1 of 1 (10 items)