Converting SQL to LINQ, Part 1: The Basics (Bill Horst)

Converting SQL to LINQ, Part 1: The Basics (Bill Horst)

  • Comments 20

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

Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
  • PingBack from http://msdnrss.thecoderblogs.com/2007/11/19/converting-sql-to-linq-part-1-the-basics-bill-horst-2/

  • Hi,

    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?

    Regards

    Csaba

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

    Please use good examples when trying to teach new concepts.

  • Sorry for any confusion over the code I provided.

    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.

  • 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 Converting SQL to LINQ, Part 1: The Basics . I’ve made the following name

  • 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

  • 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

  • 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

  • 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,Part1:基础(BillHorst) [原文作者]:BillHorst

    [原文链接]:ConvertingSQLtoLINQ,Part1...

Page 1 of 2 (20 items) 12