Language Integrated Query – Beginners Level

Introduction

LINQ is one of the most attractive innovations of the .NET 3.5 version. 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 ASP.NET applications, you are most likely to use LINQ as a part of a database component. LINQ is a replacement to spare the hassle of ADO.NET. This chapter introduces you to the LINQ model and its role in .NET platform. You will become aware 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 today. What kind of ADO.net business logic is implemented at the database access layer to manipulate data from the database?

The following sample performs some database-related operations such extracting data from tables 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:

Project: LINQsample

PublicclassempDetails
   {
public empDetails() { }

publicstring FirstName { get; set; }
publicstring LastName { get; set; }
publicstring 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 tabletbl_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 SqlConnection class. The SqlCommand class defines the sql query statements. After it has successfully opened the connection, the data is read with help of the SqlDataReader class, which reads records row by row. Finally, every selected column is stored into generic class empDetailstype objectemployees.

Project: LINQsample

publicclassempOperation
   {
privatestring connectionString;

public empOperation()
        {
            // Get connection string from web.config.
       connectionString = @"Data Source=localhost;Initial Catalog=Master;Integrated Security=SSPI";
        }
publicList<empDetails> GetEmployees()
   {
SqlConnection con = newSqlConnection(connectionString);
string query = @"select * from tbl_Emp";
SqlCommand cmd = newSqlCommand(query, con);

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

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

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

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

Finally, on 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 empDetails class object. Therefore we added all the returned rows into generic object and pass this reference to datagrid control to populate the rows.

Project: LINQsample

privatevoid btnGetData_Click(object sender, EventArgs e)
   {
empOperation obj = newempOperation();

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

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

     dbvData.DataSource = lEmp;
   }

Finally after successfully compiling the program, the user clicks the button, and the data grid populates entire rowsof the table as following;


Figure 1.2: Output

So the objective of the aforementioned sample is to highlight the complexities behind the traditional database related operations And the extra overhead of implementing ADO.NET functionality to perform queries executions. You have to choose two different ways to access diverse kinds of data sources such as XML, in a list of databases in traditional database operations. But with LINQ, you get one ways 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 eases the programmer’s job by focusing 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 many times you have found yourself coding the same type of boilerplate code. LINQ removes some of that burden.

ADO.NET provides an API to get access to the relational database and to represent relational data in memory. This API consists of classes like SqlConnection, SqlDataAdapter,DataSet, etc.The problem with these classes is that they force the developer to work explicitly with tables, records and columns while C# use OOP’s paradigm.

As the 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 particular flavor of data required programmers to make use of diverse APIs. Consider, for example, following table which illustrates several common APIs used to access various types of data.

Type of Data Data Source
Collection of objects System.Array or System.Collection
Relational Data System.Data or System.Data.SqlClient
Xml Data System.Xml

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 having huge complicacy itself, which offers very little in the way of integration.

LINQ Basic

Want to learn more?? The InfoSec Institute Ethical Hacking course goes in-depth into the techniques used by malicious, black hat hackers with attention getting lectures and hands-on lab exercises. While these hacking skills can be used for malicious purposes, this class teaches you how to use the same hacking techniques to perform a white-hat, ethical hack, on your organization. You leave with the ability to quantitatively assess and measure threats to information assets; and discover where your organization is most vulnerable to black hat hackers. Some features of this course include:

  • Dual Certification - CEH and CPT
  • 5 days of Intensive Hands-On Labs
  • Expert Instruction
  • CTF exercises in the evening
  • Most up-to-date proprietary courseware available

LINQ query expressions look like SQL expressions. LINQ query expressions (unlike a traditional SQL statement)are strongly typed. Therefore, the C# compiler makes sure that these expressions are syntactically well formed. Query expressions have metadata representation 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, autocompletion, 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.Arraytype, which is 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
{
classProgram
   {
staticvoid 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 inobj)
Console.WriteLine("Item: {0}", x);

Console.ReadLine();
      }
   }
}

Notice that the query expression created here, makes use of thefrom, 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 results is 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 standardforeach construct. The output of this sample program as following;


Figure 1.3: output

LINQ Queries

If we took the reference of our earlier project LINQsample, we can also implements LINQ functionality there. Suppose we are retrieving a result set from the database table tbl_Emp where the employees whose last name start withthe ‘Y’ letter. You can carry on this operation by loop through the full collection of employees with a condition check as following;

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

You can perform the same task using a LINQ expression. The following modification shows how you can rewrite the code, replacing the for each block with a LINQ query. The following program also demonstrates 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 = newempOperation();
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 surprise.

Before you go further 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 the clause.

from x in employees

The from clause identifier has two parts. 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 employee 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 following;

empOperation obj = newempOperation();
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 aforementioned sample produces the following result:


Figure 1.4: concatenation

Traditionally, working with collections of objects meant writing a lots of looping code using for or for each 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 demonstrates the basic query syntax in a more granular fashion.

int [] data = newint[] {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, which 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.

Operators Description
Select Define a projection to select values.
Where Defines a restriction to filter the collection.
OrderBy Sort values in ascending or descending order.
Join Join the two collections.
Reverse Reverse the elements of collections.
GroupBy Group elements with a common key.
Contains Check whether a specific element is in the collection.
Distinct Remove duplicates from the collections.
All Determine if all elements in the collection satisfy a predicate.
Except Return elements that appear in just one collection.
Union Return a unique element that appears in either of the two collections.
Count Count the total number of values
Sum Get Sum of all values
Average Get Average of all values
Range Returns a sequence of numbers
Ascending, Descending Determine the sort order.

Summary

Here, you have learned the core features of LINQ for beginners. You also have seen that LINQ has 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 different types of data. This article illustrates why LINQ is important and examines the LINQ common query operators such as Select, Where, Join and so on. In the forthcoming article of this series, we will demonstrate advanced features of LINQ such as Extension Method, Lambda Expressions, and LINQ to XML etc…

REFERENCE:

Professional LINQ by Scott Klein