{"id":4843,"date":"2008-01-08T14:31:00","date_gmt":"2008-01-08T14:31:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2008\/01\/08\/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst\/"},"modified":"2024-07-05T14:35:00","modified_gmt":"2024-07-05T21:35:00","slug":"converting-sql-to-linq-part-7-union-top-subqueries-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 7: UNION, TOP, Subqueries (Bill Horst)"},"content":{"rendered":"

This post assumes you\u2019ve read the previous posts in this series:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 5: GROUP BY and HAVING<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 6: Joins<\/a><\/p>\n

This post will discuss UNION, TOP and Subqueries.\u00a0 Next week, I plan to cover LEFT, RIGHT and FULL OUTER JOIN more fully.\u00a0 If there are additional topics you\u2019d like to see discussed related to converting SQL to LINQ, please add a comment to this post.<\/p>\n

UNION<\/b><\/p>\n

In SQL, a UNION clause joins the results of two SELECT queries into one set of data.\u00a0 With VB LINQ, the Union method can be called on a query, and passed a second query to produce the same result.\u00a0 The Intersect<\/i><\/b> method is also available, and returns the common elements of the two query results.\u00a0 The Except<\/i><\/b> method returns all the results from the first query that don\u2019t appear as results from the second query.<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT CustomerID ID FROM CustomerTable<\/p>\n

UNION<\/p>\n

SELECT OrderID ID From OrderTable<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

(From Contact In CustomerTable _<\/p>\n

\u00a0Select ID = Contact.CustomerID).Union(From Shipment In OrderTable _<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0Select ID = Shipment.OrderID)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

TOP<\/b><\/p>\n

The SQL TOP operator returns the first n results of a query.\u00a0 A Take clause can accomplish the same thing in a VB LINQ expression.\u00a0 Take is described in more detail below, along with some related clauses.<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT TOP 10 * FROM CustomerTable ORDER BY CustomerID<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From Contact In CustomerTable Order By Contact.CustomerID Take 10<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Take\/Skip\/While<\/i><\/b><\/b><\/p>\n

The Take clause is applied to the results of the clause that precedes it, and specifies a number of results to \u201ctake\u201d, or return.\u00a0 All additional results are disregarded.<\/p>\n

The Skip clause specifies a number of results to be ignored at the \u201ctop\u201d of a query result.\u00a0 The results of the preceding clause are passed in, and all but the first n results are returned.<\/p>\n

The Take While clause specifies a condition, and takes results from the start of a query result until the condition evaluates to false.<\/p>\n

The Skip While clause specifies a condition, and skips results from the start of a query result until the condition evaluates to be false.<\/p>\n

To give concrete examples, the following queries returns the following result:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Dim digits = New Integer() {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}<\/p>\n

Dim AllDigits = From int In digits<\/p>\n<\/td>\n<\/tr>\n

\n

Results:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8<\/p>\n<\/td>\n<\/tr>\n

\n

SKIP<\/b><\/p>\n

Dim SkipFirstTwo = From int In digits Skip 2<\/p>\n<\/td>\n<\/tr>\n

\n

Results:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7<\/p>\n<\/td>\n<\/tr>\n

\n

TAKE<\/b><\/p>\n

Dim TakeFirstTwo = From int In digits Take 2<\/p>\n<\/td>\n<\/tr>\n

\n

Results:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/p>\n<\/td>\n<\/tr>\n

\n

SKIP and TAKE together<\/b><\/p>\n

Dim SkipTwoTakeFive = From int In digits Skip 2 Take 5<\/p>\n<\/td>\n<\/tr>\n

\n

Results:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4<\/p>\n<\/td>\n<\/tr>\n

<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

This post assumes you\u2019ve read the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20,195],"tags":[41,49,94,166],"class_list":["post-4843","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converting-sql-to-linq","category-visual-basic","tag-bill-horst","tag-converting-sql-to-linq","tag-linqvb9","tag-vb2008"],"acf":[],"blog_post_summary":"

This post assumes you\u2019ve read the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 4: Functions \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4843"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4843\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}