Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators (Bill Horst)

Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators (Bill Horst)

  • Comments 19

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

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 all duplicate records in the query result to be removed.  In a LINQ expression, Distinct is its own individual clause, rather than a specifier on the Select clause.  This means that Distinct can appear between any two other clauses.  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.  To two code examples below accomplish the same results:

 

SQL

SELECT DISTINCT Name, Address

FROM CustomerTable

 

 

VB

From Contact In CustomerTable _

Select Contact.Name, Contact.Address _

Distinct

 

 

WHERE

 

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

 

SQL

SELECT * FROM CustomerTable

WHERE State = “WA”

 

 

VB

From Contact In CustomerTable _

Where Contact.State = “WA”

 

 

Operators

 

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

 

SQL

SELECT * FROM CustomerTable

WHERE City = “Seattle” AND Zip = “98122”

 

 

VB

From Contact In CustomerTable _

Where Contact.City = “Seattle” And Contact.Zip = “98122”

 

 

Even if there is no equivalent VB operator, it should be possible to represent nearly any SQL WHERE expression as a VB expression.  BETWEEN is an example of a SQL keyword that doesn’t have an analogous VB keyword, but which can be easily represented in VB.

 

SQL

SELECT * FROM OrderTable

WHERE OrderDate BETWEEN ‘Sept-22-2007’ AND ‘Sept-29-2007’

 

 

VB

From Shipment In OrderTable _

Where (Shipment.OrderDate > #9/22/2007#) _

    And (Shipment.OrderDate < #9/29/2007#)

 

 

ORDER BY

 

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

 

SQL

SELECT * FROM CustomerTable

ORDER BY Phone

 

 

VB

From Contact In CustomerTable _

Order By Contact.Phone

 

 

ASC/DESC

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.  VB uses Ascending and Descending keywords for the same purpose, with the same syntax.  If neither specifier is present, ascending order is the default.

 

SQL

SELECT * FROM CustomerTable

ORDER BY Phone ASC, Name DESC

 

 

VB

From Contact In CustomerTable _

Order By Contact.Phone Ascending, Contact.Name Descending

 

 

With this and my two previous posts, it should be possible to convert basic SQL queries to VB LINQ code.  Next week, I’ll talk about Functions, both Scalar and Aggregate.

-      Bill Horst, VB IDE Test

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • PingBack from http://www.absolutely-people-search.info/?p=3007

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • Hi,

    I was wondering what would be the VB statement if I would like to select, where the OrderDate is, say in the last 3 months ? If I do not want to put the dates in the statement.

    jackie

  • Hi Jackie,

    You can use the Now keyword to get the current date/time.  There are probably several ways to code up this query, but the way I would do it is like this:

    Dim ThreeMonthSpan As New TimeSpan(90,0,0,0) '90 days

    Dim OrdersInLastThreeMonths = From Shipment In OrderTable _

    Where Shipment.OrderDate >= Now.Subtract(ThreeMonthSpan)

    Hope that helps,

     - Bill

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • 引用: 从SQL到LINQ,Part3:DISTINCT,WHERE,ORDERBYandOperators(BillHorst) [原文作者]:BillHorst...

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • Bill Horst of the VIsual Basic team at Microsoft has been writing a great series on Converting SQL to

  • ここでは、このシリーズの前の投稿をお読みになっていることを前提としています。 Converting SQL to LINQ, Part 1: The Basics ( 英語 ) Converting SQL

  • Is it possible, given the VB lambda iossues, to do an order by on a distinct query?  (select x order by y).distinct will override the order by

Page 1 of 2 (19 items) 12