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:

Sorry for this comment. I understand that you want me understand this. But, Unfortunatly is excellent example of minimal code that is hard to understand! (Or not understandable at all for me.)

I just cant understand minimal variiations of names like cust Customer Customers when the variations doen’t make sense. (Customers is pluralis of Customer for me, then it should be an array or collction of Customers. Not different entities. It goes around in my head.

Should:

"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."

Maybe instead be:

The SQL code examples I’ll be using assume a table called CustomerSQTable (singularis) and a table called OrderSQLTable (singularis). The VB code examples assume some object named anCustomer (singularis?) with type IEnumerable(Of CustomerClass) and another object named anOrder with type IEnumerable(Of OrderClass). I’ll also use two classes, CustomerClass and OrderClass, which are defined below."

And then:

"From cust In Customers _

Select CustomerName = cust.Name, ID = cust.CustomerID _

Order By ID"

Should be rewritten as:

Ehh, well what???

I don’t know, someone who has a clear understanding how to name it so it is understandable for me?

I agree the example is not very good. I get tired of people posting code that demonstrates some technique but that ends up teaching other bad programming techniques.

For example, both classes have been modeled after a relational database and this is teaching poor design. The Order class should not contain a customerId. Foreign keys are a concept of a relational database not object oriented design. Instead the example should have included a property on customers defined as a collection of Order objects.

First, regarding RDB vs OOP concepts, this series of posts is intended to show how someone with existing SQL code could migrate to LINQ easily. Someone actually performing this sort of task would probably be using a database and our LINQ-to-SQL feature, but I made my code examples to use "In-Memory" objects for simplicity. I tried to model my objects after the Northwind database, since this is something a lot of people would already be familiar with.

Csaba, I want to make sure this is understandable to you. It may start to make more sense after my next post. If not, please let me know and I’ll try to adjust the names to be more clear.

Right here is the right site for anyone who wants to understand thiis topic.
You know so much its almost tough to argue with you (not that
I personally would want to…HaHa). You definitely put a fresh spin on a subject that has been written about for
years. Excellent stuff, just excellent!