LINQ Programming: Language Integrated Query

Article Overview

• Introduction
• Traditional Queries
• Role of LINQ
• LINQ Basic
• Query Operators
• Summary

Introduction

Language-Integrated Query (LINQ) is one of the most attractive innovations of .NET 3.5 versions. It can be used equally well in a range of .NET applications, for instance console applications to rich Windows clients. Although you can combine LINQ to anywhere in an ASP.NET application you are most likely to use LINQ as a part of database components. LINQ basically replaces the hassle of ADO.NET. This chapter introduces you to the LINQ model and its role in the .NET platform. Here you learn the role of query operations and query operators. You will see how to create LINQ query expressions that can select, sort and group, alter and transform data. Apart from LINQ, you will also learn other significant concepts related to LINQ, like extension methods, anonymous methods and lambda expressions.

Traditional Queries

Before delving into the features of LINQ, let's have a glimpse at how queries are done todays. What kind of ADO.Net business logic is implemented in the database access layer to manipulate data from a database. The following sample performs some database related operations such as extracting data from a table over the front-end Windows form. The database Master contains a table tbl_Emp with some columns as shown below.


                              Figure 1.1: tbl_Emp design

To make it easier to shuffle information from the Master database back and forth, it makes sense to create an empDetails class that provides all the database fields and properties. Here the full code of that class is as in the following.

Project: LINQsample

Public class empDetails
{
   public empDetails() { }

   public string FirstName { get; set; }
   public string LastName { get; set; }
   public string Country { get; set; }

   public empDetails(string firstName, string lastName,
   string country)
   {

      this.FirstName = firstName;
      this.LastName = lastName;
      this.Country = country;
   }
}

The following empOperation class performs the actual operation to extract data from the database table tbl_Emp. It initializes the database query string into its constructor to establish a connection with the database. To access the database table, various classes from the namespace System.Data.SqlClient are needed. First of all a connection is opened with the SqlConnection class. The SqlCommand class defines the SQL query statements, after successfully opening the connection. The data is read using the SqlDataReader class, that reads records row by row. Finally every selected column is stored into a generic class empDetails of type object employees.

Project: LINQsample

public class empOperation
{
   private string connectionString;

   public empOperation()
   {
      // Get connection string from web.config.
      connectionString = @"Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI";
   }

   public List<empDetails> GetEmployees()
   {
      SqlConnection con = new SqlConnection(connectionString);
      string query = @"select * from tbl_Emp";
      SqlCommand cmd = new SqlCommand(query, con);


      // Create a collection for all the employee records.
      List<empDetails> employees = new List<empDetails>();

      try
      {
         con.Open();
         SqlDataReader reader = cmd.ExecuteReader();

         while (reader.Read())
         {
            empDetails emp = new empDetails((string)reader["FirstName"],
            (string)reader["LastName"], (string)reader["Country"]);
            employees.Add(emp);
         }
         reader.Close();

         return employees;
      }
      catch (SqlException err)
      {
         throw new ApplicationException("Data error.");
      }
      finally
      {
         con.Close();
      }
   }

}

Finally in the presentation layer (Windows forms) we place a DataGridView control to display the entire extracted row from the database. Here we call the helper class empOperation method and assign its reference to the generic type of an empDetails class object. Therefore we added all the returned rows into the generic object and pass this reference to the datagrid control to populate the rows.

Project: LINQsample

private void btnGetData_Click(object sender, EventArgs e)
{
   empOperation obj = new empOperation();

   List<empDetails> employees = obj.GetEmployees();

   List<empDetails> lEmp = new List<empDetails>();
   foreach (empDetails emp in employees)
   {
      lEmp.Add(emp);
   }

   dbvData.DataSource = lEmp;
}

Finally after successfully compiling the program, the moment the user clicks the button, the data grid populates all the rows of the table as in the following:


                                             Figure 1.2: Output

So the objective of the previously mentioned sample is to show the complexities behind the traditional database related operations. The extra overhead of implementing ADO.NET functionality is to perform query executions. You need to choose 2 ways to access the diverse kind of data sources such as XML, list of database in traditional database operations. But with LINQ, you get one way to access all these data sources.

Role of LINQ

Data-acquisition languages such as SQL are functional in nature, meaning that the emphasis is placed on the operation and there is no immutable data used during the process. LINQ bridges the gap between the imperative programming style and the functional programming style. LINQ lightens the programmer's job allowing them to focus on the business logic while spending less time coding associated with data access code. If you have noticed earlier during building the sample program, think about how often you have found yourself coding up the same type of boilerplate code over and over again. LINQ removes some of that burden.

ADO.NET provides an API to get access to relational databases and to represent relational data in memory. This API consists of classes like SqlConnection, SqlDataAdapter, DataSet and so on. The problem with these classes is that they force the developer to work explicitly with tables, records and columns whereas C# uses OOP.

As a programmer, it is hard to deny that the vast majority of our programming time is spent obtaining and manipulating data from numerous places. Prior to .NET 3.5, interacting with a specific flavor of data required programmers to use diverse APIs. Consider, for example, the following table that illustrates several common APIs used to access various types of data.



There is nothing wrong with these approaches to data manipulation. In fact, when programming with .NET 3.5, you can certainly make direct use of ADO.NET, the XML namespaces and the various collection types. However, the basic problem is that each of these APIs are complicated that offer very little in the way of integration.

LINQ Basic

LINQ query expressions look like SQL expressions. The LINQ query expression (unlike a traditional SQL statement) is strongly typed. Therefore, the C# compiler ensures that these expressions are syntactically well formed. Query expressions have metadata representations within the assembly that makes use of them. Tools such as Visual Studio 2010 IDE can use this metadata for useful features such as IntelliSense, auto completion and so forth.

LINQ Sample

To begin examining the LINQ programming model, let's build simple query expressions to manipulate data contained within various arrays. Create a .NET 4.0 Console Application and define a string array containing six or so items of your choice. When you have any array of data, it is very common to extract a subset of items based on certain requirements. You could certainly perform such tasks using members of the System.Array type that is a bit cumbersome but LINQ query expressions can greatly simplify the process. In this sample, we wish to obtain a subset from the array that contains items with names consisting of more than eight characters.

Project: LinqTest 

using System;
using System.Collections.Generic;
using System.Linq;

namespace linqTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // array of strings.
            string[] arVar = {"Database", "Networking",
                               "Programming", "Amazon",
                                "box", "System"};

            // Build a query expression to represent the items in the array
            // that have more than 8 letters.
            IEnumerable<string> obj = from a in arVar
                                         where a.Length > 8
                                         orderby a
                                         select a;
            // Print out the results.
            foreach (string x in obj)
                Console.WriteLine("Item: {0}", x);

            Console.ReadLine();
        }
    }
}

Notice that the query expression created here, makes use of the from, in, where, orderby and select query operators. We will dig into the formalities of query expression syntax later. Here each item that matches the search criteria has been given the name “a” and the results are stored in the object that implements the generic version of IEnumerable<T>. Once we obtain the result set, we then simply print out each item using a standard foreach construct. The output of this sample program is as in the following:


                                                                  Figure 1.3: output

LINQ Queries

If we took the reference of our earlier project LINQsample, we can also implement the LINQ functionality there. Suppose we are retrieving a result set from the database table tbl_Emp where the employees have last names starting with the letter "Y". You can carry on this operation in a loop through the full collection of employees with a condition check as in the following:

foreach (empDetails emp in employees)
{
   if (emp.LastName.StartsWith("Y"))
   {
      lEmp.Add(emp);
   }
}

You can do the same task using a LINQ expression. The following modification shows how to rewrite the code, replacing the foreach block with a LINQ query. The following program also shows the filtering sorting over a specific data set. You will see how a where clause can filter the results to include only those that match a specific condition.

empOperation obj = new empOperation();
List<empDetails> emp = obj.GetEmployees();

IEnumerable<empDetails> data = from x in emp
where x.LastName.StartsWith("Y")
select x;

dataGrid1.DataSource = data.ToList();

Note: LINQ queries are fully name and type-checked at compile-time, reducing runtime error surprises.

Before proceding with LINQ, you need to understand how a LINQ expression is composed. All LINQ expressions must have the from clause that indicates the data source and a select clause that indicates the data that you want to retrieve the from clause, as in the following:

from x in employees

The from clause identifiers has two parts of information. The word immediately after from assigns an alias that represents individual items in the data source and the word after in identifies the data source. In this case employees that hold the empDetails instance. LINQ expressions work and return on objects that implement IEnumerable<T>. Thus, you can pass the result from one LINQ expression into other expressions.

You can retrieve subsets of results by implementing the projections. For example, you could pull out a list of First Name and Last Name in concatenated form as in the following:

empOperation obj = new empOperation();
List<empDetails> emp = obj.GetEmployees();

var data = from x in emp
select x.FirstName + x.LastName;

foreach (var a in data)
{
   textBox1.AppendText(a + "\n");
}

Note: The var keyword is resolved at compile time and can't be used as a class member variable.

You'll also need to use the var keyword whenever you want to reference an individual object. The previously mentioned sample produces the following result:


                  Figure 1.4: concatenation

Traditionally, working with collections of objects meant writing lots of looping code using for or foreach loop to iterate through a collection, carrying out filtering using if statements. LINQ frees you from having to write looping code; it allows you to write queries that filter a list or calculate aggregate functions on elements. The following example shows the basic query syntax to understand it in a more granular fashion.

int [] data = new int[] {5,3,6,8,4,9,0,1,2};

var res = from x in data
          where x < 6
          orderby x
          select x;

foreach (int a in res)
{
    textBox1.AppendText(a + "\n");
}

Query Operators

LINQ is built upon the use of standard query operators, that are methods that operate on sequences such as collections that implement IEnumerable or IQueryable. The LINQ query defines a declarative syntax for most common operators as in the following tables.



Summary

This article posed the contents for beginners. Here, you have learned the core features of LINQ. You also saw that LINQ has a wide range of potential applications that provide a declarative model for retrieving and processing data that allows you to use the same syntax with a wide range of various types of data. This article illustrated why LINQ is important and examined the LINQ common query operators such as Select, Where, Join and so on. A forthcoming article of this series shows advance features of LINQ such as Extension Methods, Lambda Expressions and LINQ to XML and so on.