A group blog from members of the VB team
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.
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:
SELECT DISTINCT Name, Address
From Contact In CustomerTable _
Select Contact.Name, Contact.Address _
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.
SELECT * FROM CustomerTable
WHERE State = “WA”
Where Contact.State = “WA”
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.
WHERE City = “Seattle” AND Zip = “98122”
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.
SELECT * FROM OrderTable
WHERE OrderDate BETWEEN ‘Sept-22-2007’ AND ‘Sept-29-2007’
From Shipment In OrderTable _
Where (Shipment.OrderDate > #9/22/2007#) _
And (Shipment.OrderDate < #9/29/2007#)
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.
ORDER BY Phone
Order By Contact.Phone
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.
ORDER BY Phone ASC, Name DESC
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
Las sentencias SQL llevan comillas simples para los valors que son cadenas de texto:
WHERE City = 'Seattle' AND Zip = '98122'
For more samples go to:
Your BETWEEN example might need "<=" and ">=" rather than "<" and ">". It does an *inclusive* range check in T-SQL.