Part 1: Learning LINQ from SQL using Visual Basic 2008 – Selecting columns, DISTINCT, ORDER BY

For this sample, I simply created an ASP.NET 3.5 web site project (using Visual Studio 2008 beta 2) and dragged a GridView on to a web page.  Added a class "Employee.vb" to the project as following (code removed for clarity): 

 

Imports Microsoft.VisualBasic

 

Public Class Employee

    …

    Public Sub New(ByVal e As Integer, ByVal en As String, ByVal s As Double, ByVal d As Integer) …

    Public Property Empno() As Integer …

    Public Property Ename() As String …

    Public Property Sal() As Double …

    Public Property Deptno() As Integer …

End Class

 

Added a new method (to generate data) to the web page as following:

 

Private Function GetEmployeeList() As List(Of Employee)

        Dim EmpList As New List(Of Employee)

        EmpList.Add(New Employee(1001, "Jagadish", 3400, 20))

        EmpList.Add(New Employee(1002, "Chatarji", 6700, 30))

        EmpList.Add(New Employee(1003, "Pulakhandam", 3200, 10))

        EmpList.Add(New Employee(1004, "Winner", 7800, 20))

        EmpList.Add(New Employee(1005, "Sunitha Paruchuri", 6500, 30))

        EmpList.Add(New Employee(1006, "Malli", 4200, 20))

        EmpList.Add(New Employee(1007, "Ram Mullangi", 6200, 30))

        EmpList.Add(New Employee(1008, "Veditha Davuluri", 7600, 10))

        EmpList.Add(New Employee(1009, "Jyothi", 8900, 10))

        EmpList.Add(New Employee(1010, "Srinivas", 6900, 20))

        EmpList.Add(New Employee(1011, "Krishna", 2300, 30))

        EmpList.Add(New Employee(1012, "Chandra Kamala", 5400, 30))  

        Return EmpList

End Function

Now, let us consider the following SQL SELECT statement:

SELECT ename FROM emp

The corresponding LINQ statement (along with execution code) for the above SQL is as follows:

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load

        If Not IsPostBack Then

            Dim query = From p In GetEmployeeList() Select p.Ename

            Me.GridView1.DataSource = query

            Me.GridView1.DataBind()

        End If

End Sub

 

The only important statement from the above is the following:

            Dim query = From p In GetEmployeeList() Select p.Ename

which selects only the property (or column)  "Ename" of every successive object available in "p" from the collection returned by "GetEmployeeList()"

Selecting more columns:

Further, the following SQL SELECT

     SELECT ename, sal FROM emp

can be coded in LINQ as following:

     Dim query = From p In GetEmployeeList() Select p.Ename, p.Sal

Columns with Expressions:

The following SQL SELECT

     SELECT ename, sal * 12 AS AnnSal FROM emp

can be coded in LINQ as following:

     Dim query = From p In GetEmployeeList() Select p.Ename, AnnSal = (p.Sal * 12)

Make sure that Column Alias is compulsory in LINQ, if more than one column is available in the list.

 

Working with DISTINCT:

The following SQL SELECT

     SELECT DISTINCT deptno FROM emp

can be coded in LINQ as following:

     Dim query = From p In GetEmployeeList() Select p.Deptno Distinct

 

Working with ORDER BY:

The following SQL SELECT

     SELECT ename, sal FROM emp ORDER BY ename

can be coded in LINQ as following:

     Dim query = From p In GetEmployeeList() Select p.Ename, p.Sal Order By Ename

We can also make use of Alias columns as part of ORDER BY operator as following:

 

Dim query = From p In GetEmployeeList()

Select p.Ename, Annsal = (p.Sal * 12)

Order By Annsal Descending