{"id":5063,"date":"2007-12-04T13:31:00","date_gmt":"2007-12-04T13:31:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/12\/04\/converting-sql-to-linq-part-3-distinct-where-order-by-and-operators-bill-horst\/"},"modified":"2024-07-05T14:37:22","modified_gmt":"2024-07-05T21:37:22","slug":"converting-sql-to-linq-part-3-distinct-where-order-by-and-operators-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-3-distinct-where-order-by-and-operators-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators (Bill Horst)"},"content":{"rendered":"

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

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

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

Continuing with specific query clauses, I will cover topics related to DISTINCT, WHERE and ORDER BY.<\/p>\n

DISTINCT<\/b><\/p>\n

SQL SELECT statements can include the DISTINCT specifier, which causes all duplicate records in the query result to be removed.\u00a0 In a LINQ expression, Distinct is its own individual clause, rather than a specifier on the Select clause.\u00a0 This means that Distinct can appear between any two other clauses.\u00a0 The Distinct clause takes whatever result is returned by the preceding clause (Select, in the case below) and returns a filtered result with duplicates removed.\u00a0 To two code examples below accomplish the same results:<\/p>\n\n\n\n\n
\n

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

\n

SELECT DISTINCT Name, Address<\/p>\n

FROM CustomerTable<\/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

Select Contact.Name, Contact.Address _<\/p>\n

Distinct<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

WHERE<\/b><\/p>\n

Much like a SQL query, a LINQ expression allows you to add a Where clause to filter the results based on a certain condition.\u00a0 You can use any valid VB Boolean expression.<\/p>\n\n\n\n\n
\n

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

\n

SELECT * FROM CustomerTable<\/p>\n

WHERE State = \u201cWA\u201d<\/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

Where Contact.State = \u201cWA\u201d<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Operators<\/i><\/b><\/p>\n

SQL WHERE clauses can often include other operators like AND.\u00a0 There will usually be a similar operator in VB that can be used in the Where clause expression to accomplish the same result.<\/p>\n\n\n\n\n
\n

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

\n

SELECT * FROM CustomerTable<\/p>\n

WHERE City = \u201cSeattle\u201d AND Zip = \u201c98122\u201d<\/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

Where Contact.City = \u201cSeattle\u201d And Contact.Zip = \u201c98122\u201d<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Even if there is no equivalent VB operator, it should be possible to represent nearly any SQL WHERE expression as a VB expression.\u00a0 BETWEEN is an example of a SQL keyword that doesn\u2019t have an analogous VB keyword, but which can be easily represented in VB.<\/p>\n\n\n\n\n
\n

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

\n

SELECT * FROM OrderTable<\/p>\n

WHERE OrderDate BETWEEN \u2018Sept-22-2007\u2019 AND \u2018Sept-29-2007\u2019<\/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 Shipment In OrderTable _<\/p>\n

Where (Shipment.OrderDate > #9\/22\/2007#) _<\/p>\n

\u00a0\u00a0\u00a0 And (Shipment.OrderDate < #9\/29\/2007#)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

ORDER BY<\/b><\/p>\n

The SQL ORDER BY clause can also be represented in a LINQ expression.\u00a0 A LINQ Order By clause allows for a comma-delimited list of expressions to specify how results should be sorted.\u00a0 Any valid VB expression can be used, so these expressions don\u2019t necessarily have to be the names of field that were selected.<\/p>\n\n\n\n\n
\n

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

\n

SELECT * FROM CustomerTable<\/p>\n

ORDER BY Phone<\/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

Order By Contact.Phone<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

ASC\/DESC<\/i><\/b><\/p>\n

A SQL ORDER BY clause can also include ASC and DESC keywords, to specify that the sort should be in ascending or descending order, respectively.\u00a0 VB uses Ascending and Descending keywords for the same purpose, with the same syntax.\u00a0 If neither specifier is present, ascending order is the default.<\/p>\n\n\n\n\n
\n

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

\n

SELECT * FROM CustomerTable<\/p>\n

ORDER BY Phone ASC, Name DESC<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

This post assumes you\u2019ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The Basics Converting SQL to LINQ, Part 2: FROM and SELECT Continuing with specific query clauses, I will cover topics related to DISTINCT, WHERE and ORDER BY. DISTINCT SQL SELECT statements can include the DISTINCT specifier, which causes […]<\/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-5063","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: Converting SQL to LINQ, Part 1: The Basics Converting SQL to LINQ, Part 2: FROM and SELECT Continuing with specific query clauses, I will cover topics related to DISTINCT, WHERE and ORDER BY. DISTINCT SQL SELECT statements can include the DISTINCT specifier, which causes […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5063","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=5063"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5063\/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=5063"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=5063"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=5063"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}