Converting SQL to LINQ, Part 1: The Basics (Bill Horst)
As you may already know, VB LINQ statements enable SQL-like syntax for queries in the VB language. LINQ syntax doesn't match SQL syntax exactly, so if you are already working with SQL or familiar with SQL queries, you may find yourself wanting to convert an existing SQL query to LINQ.
This will be the first in a series of posts about converting SQL to LINQ. In this post, I want to establish a basic understanding of how SQL differs from LINQ, and then get into specific language constructs next time.
Assumptions
The SQL code examples I'll be using assume a table called Customers and a table called Orders. The VB code examples assume some object named Customers with type IEnumerable(Of Customer) and another object named Orders with type IEnumerable(Of Order). I'll also use two classes, Customer and Order, which are defined below.
|
Class Customer
Public CustomerID As Integer
Public Name As String
Public Phone As String
Public Address As String
Public City As String
Public State As String
Public Zip As String
End Class
Class Order
Public OrderID As Integer
Public CustomerID As Integer
Public Cost As Single
Public Phone As String
Public OrderDate As DateTime
Public ShippingZip As String
Public ItemName As String
End Class
|
Basic Syntax
LINQ supports SQL SELECT statements, but not other types of SQL statements, such as CREATE, INSERT, UPDATE and DELETE. The basic syntax for a SQL SELECT statement can be considered as a series of "clauses", where the first clause is a SELECT clause.
|
sqlSelectClause [ sqlClause1 [ sqlClause2 [ ... ] ] ]
|
SQL syntax can differe a bit among different versions, but here's an example for instance:
|
SQL |
|
SELECT Name CustomerName, CustomerID ID
FROM Customers
ORDER BY ID
|
The basic syntax for a VB LINQ expression is also a series of "clauses", of which the first is a From Clause (or Aggregate Clause, but we'll get to that later).
|
linqFromClause [ linqClause1 [ linqClause2 [ ... ] ] ]
|
For instance:
|
VB |
|
From cust In Customers _
Select CustomerName = cust.Name, ID = cust.CustomerID _
Order By ID
|
I say "VB LINQ expression" above because LINQ Queries are not full statements. Though a SQL statement may appear on its own, LINQ Queries are syntactically the same as an expression like 3 * 4. This isn't a whole statement, so something must be "done" with it. A LINQ Query might appear in VB code like this:
|
VB |
|
Dim SortedCustomers = From cust In Customers _
Select CustomerName = cust.Name, ID = cust.CustomerID _
Order By ID
|
Conceptually, each clause in a LINQ query is called on an object with type IEnumerable(Of T) and returns another IEnumerable(Of T) where T in the return type is not necessarily the same as the initial type. Query clauses are generally analogous to SQL clauses (e.g. SELECT, ORDER BY), so you can usually convert your SQL query to LINQ in a clause-by-clause fashion. In the above examples, the clauses appear in a slightly different order and have different syntax, but as you can see, they are quite similar.
Also notice that SQL syntax usually allows line breaks to be made between clauses. In VB this requires an underscore at the end of each line to show that the expression is continued on the next line.
This is a broad explanation, but I'll get into the specifics next week. My intention is to include the following installments:
-
FROM and SELECT
-
DISTINCT, WHERE, ORDER BY, Operators
-
Functions (Scalar and Aggregate)
-
GROUP By and HAVING
-
Joins
-
UNION, TOP and Subqueries
- Bill Horst, VB IDE Test
