Post By: Hanan Mannan
Contact Number: Pak (+92)-321-59-95-634
-------------------------------------------------------

ASP.NET - LINQ

Most applications are data-centric and most of these data repositories are however, relational databases. Over the years designers and developers have designed applications based on object models.

These objects were responsible for connecting to the data access components - called the data access layer (DAL). There are however, two things to consider:

• All the data needed in an application are not stored in the same source. The source could be a relation database, some business objects, XML file, or on web services.
• Accessing in-memory object is simpler and less expensive than accessing data from a database or XML file.
• The data accessed are not used directly, but needs to be sorted, ordered, grouped, altered etc.

So if there is one tool that makes all kinds of data access easy, that allows joining data from such disparate data sources and perform standard data processing operations, in few lines of codes, it would be of great help.

LINQ or Language-Integrated Query is such a tool. LINQ is set of extensions to the .Net Framework 3.5 and its managed languages that sets the query as an object. It defines a common syntax and a programming model to query different types of data using a common language.

The relational operators like Select, Project, Join, Group, Partition, Set operations etc., are implemented in LINQ and the C# and VB compilers in the .Net framework 3.5, which support the LINQ syntax makes it possible to work with a configured data store without resorting to ADO.Net.

For example querying the Customers table in the Northwind database, using LINQ query in C#, the code would be:

var data = from c in dataContext.Customers
where c.Country == "Spain"
select c;

Where:

• The 'from' keyword logically loops through the contents of the collection.
• The expression with the 'where' keyword is evaluated for each object in the collection.
• The 'select' statement selects the evaluated object to add to the list being returned.
• The 'var' keyword is for variable declaration. Since the exact type of the returned object is not known, it indicates that the information will be inferred dynamically.

LINQ query can be applied to any data-bearing class that inherits from IEnumerable<T>, here T is any data type, for example List<Book>.

Let us look at an example to understand the concept. The example uses the following class: Books.cs

public class Books
{
   public string ID {get; set;}
   public string Title { get; set; }
   public decimal Price { get; set; }
   public DateTime DateOfRelease { get; set; }

   public static List<Books> GetBooks()
  {
     List<Books> list = new List<Books>();
     list.Add(new Books { ID = "001", 
     Title = "Programming in C#", 
     Price = 634.76m, 
     DateOfRelease = Convert.ToDateTime("2010-02-05") });
     
     list.Add(new Books { ID = "002", 
     Title = "Learn Jave in 30 days", 
     Price = 250.76m, 
     DateOfRelease = Convert.ToDateTime("2011-08-15") });
     
     list.Add(new Books { ID = "003", 
     Title = "Programming in ASP.Net 4.0", 
     Price = 700.00m, 
     DateOfRelease = Convert.ToDateTime("2011-02-05") });
     
     list.Add(new Books { ID = "004", 
     Title = "VB.Net Made Easy", 
     Price = 500.99m, 
     DateOfRelease = Convert.ToDateTime("2011-12-31") });
     
     list.Add(new Books { ID = "005", 
     Title = "Programming in C", 
     Price = 314.76m, 
     DateOfRelease = Convert.ToDateTime("2010-02-05") });
     
     list.Add(new Books { ID = "006", 
     Title = "Programming in C++", 
     Price = 456.76m, 
     DateOfRelease = Convert.ToDateTime("2010-02-05") });
     
     list.Add(new Books { ID = "007", 
     Title = "Datebase Developement", 
     Price = 1000.76m, 
     DateOfRelease = Convert.ToDateTime("2010-02-05") });
     return list;
  }

}

The web page using this class has a simple label control, which will display the titles of the books. The Page_Load event creates a list of books and returns the titles by using LINQ query:

public partial class simplequery : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      List<Books> books = Books.GetBooks();
      var booktitles = from b in books select b.Title;

      foreach (var title in booktitles)
         lblbooks.Text += String.Format("{0} <br />", title);
   }
}

When the page is run, the label will display the results of the query:

The above LINQ expression:

var booktitles = 
from b in books 
select b.Title;

Is equivalent to the following SQL query:

SELECT Title from Books

LINQ Operators:

Apart from the operators used so far, there are several other operators, which implement all query clauses. Let us look at some of the operators and clauses.

The Join clause:

The 'join clause' in SQL is used for joining two data tables and displays a data set containing columns from both the tables. LINQ is also capable of that. To check this, add another class named Saledetails.cs in the previous project:

public class Salesdetails
{
   public int sales { get; set; }
   public int pages { get; set; }
   public string ID {get; set;}

   public static IEnumerable<Salesdetails> getsalesdetails()
   { 
      Salesdetails[] sd = 
      {
      new Salesdetails { ID = "001", pages=678, sales = 110000},
      new Salesdetails { ID = "002", pages=789, sales = 60000},
      new Salesdetails { ID = "003", pages=456, sales = 40000},
      new Salesdetails { ID = "004", pages=900, sales = 80000},
      new Salesdetails { ID = "005", pages=456, sales = 90000},
      new Salesdetails { ID = "006", pages=870, sales = 50000},
      new Salesdetails { ID = "007", pages=675, sales = 40000},
      };
      return sd.OfType<Salesdetails>();
   }
}

Add the codes in the Page_Load event handler to query on both the tables using the join clause:

protected void Page_Load(object sender, EventArgs e)
{
   IEnumerable<Books> books = Books.GetBooks();
   IEnumerable<Salesdetails> sales = 
                       Salesdetails.getsalesdetails();
   var booktitles = from b in books
             join s in sales
             on b.ID equals s.ID
             select new { Name = b.Title, Pages = s.pages };
   foreach (var title in booktitles)
      lblbooks.Text += String.Format("{0} <br />", title);
}

The resulted Page:

The Where clause:

The 'where clause' allows adding some conditional filters to the query. For example, if you want to see the books, where the number of pages are more than 500, change the Page_Load event handler to:

var booktitles = from b in books
          join s in sales
          on b.ID equals s.ID
          where s.pages > 500
          select new { Name = b.Title, Pages = s.pages };

The query returns only those rows, where the number of pages is more than 500:

The Orderby and Orderbydescending clauses:

These clauses allow sorting the query results. To query the titles, number of pages and price of the book, sorted by the price, write the following code in the Page_Load event handler:

var booktitles = from b in books
                 join s in sales
                 on b.ID equals s.ID
                 orderby b.Price
                 select new { Name = b.Title, 
                 Pages = s.pages, Price = b.Price};

The returned tuples are:

The Let clause:

The let clause allows defining a variable and assigning it a value calculated from the data values. For example, to calculate the total sale from the above two sales, you need to calculate:

TotalSale = Price of the Book * Sales

To achieve this, add the following code snippets in the Page_Load event handler:

The let clause allows defining a variable and assigning it a value calculated from the data values. For example, to calculate the total sale from the above two sales, you need to calculate:

var booktitles = from b in books
     join s in sales
     on b.ID equals s.ID
     let totalprofit = (b.Price * s.sales)
     select new { Name = b.Title, TotalSale = totalprofit};

The resultant query page looks like: