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:

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:

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: