Taking a crash course in LINQ - Part 1 (LINQ to SQL)

Hi All,

Last week one of my colleagues, showed me LINQ to SQL in our project. I was bit interested that how he had managed the data access layer so easily. Oh boy! after experimenting with LINQ to SQL classes and objects has just blew my mind. LINQ is probably the most revolutionary change in the .NET 3.5 framework besides CLR enhancements, ASP.NET three new controls, WPF (with XAML designer in VS 2008, VS 2010), WCF etc. Check out more on the .NET 3.5 framework. Any developer who wants to learn LINQ for ASP.NET should start off with Scott Guthrie.  

What is LINQ??

Language INtegrated Query (LINQ) a technology inside of ADO.NET built to query relational databases that could be collections, SQL Server databases, DataSets, XML files etc. C# and VB.NET has integration with LINQ and following benefits: 

1. Full intellisense
2. Strongly typed
3. Compile-time checking
4. Debugging support
5. Dramatically ease data access and portability!  

For e.g. LINQ to SQL (this blog post), LINQ to DataSet, LINQ to XML, LINQ to SharePoint.
 

LINQ is highly extensible and therefore, we can query data sources beyond what Microsoft ships such as LINQ to Flicker, LINQ to Amazon (via Web Services), LINQ to Google etc.  

Visual Studio 2008/2010 provides "LINQ to SQL" template that provides an object relational model (O/RM) and we can use .NET classes to develop the data access layer. Besides querying the database, we can also CRUD (Create - Insert, Read - Select, Update , Delete) operations as well.

Getting Started with LINQ syntax

1) Standard Query Operator:

To start off our LINQ query, the first thing to be an acquaintance with C# 3.0 and add "System.LINQ", "System.Collections.Generic" in our C# program. Furthermore, IEnumerable interface has to be used which supports a simple iteration over a non-generic collection. Lets take an sample from MSDN that displays names where length of names array is exactly 5. 

---------------------------------------------------------------- 

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

class app {
  static void Main() {
    string[] names = { "Burke", "Connor", "Frank", 
                       "Everett", "Albert", "George", 
                       "Harris", "David" };

    IEnumerable<string> query = from s in names 
                               where s.Length == 5
                               orderby s
                               select s.ToUpper();

    foreach (string item in query)
      Console.WriteLine(item);
  }
}

-----------------------------------------------------------------------------

Output

BURKE
DAVID
FRANK

The "query" variable is in fact a called "query expression" that can have one or more data sources. It has similar to SQL. This expression uses three of the standard query operators: 

• Where  
• OrderBy
• Select 

The arguments to the Where, OrderBy and Select operators are called "Lambda Expressions". 

Lets dissect lambda expressions further. When we develop a data layer via LINQ syntax, we will get a data context (will be discussed shortly in this blog).

In this data context, we can query a table such as "Products" where select ProductName equal to "Chai"

If we notice that parameter "predicate" is of type Func<Product,bool> and involves delegate which is old and tedious way of specification this function. Instead "Lambda Expression" leverage succinct way of achieving the same result follows:

"=>" operator is divided in two sections:
a) The left hand side("c" in above example) defines the parameters and types are usually implied.
b) The right hand side uses the parameters and evaluates to the  defined return type.

Lambda expression is strongly typed and VS 2008/2010 intellisense in great deal.

2) More Standard Query Operator:

There an ensemble of operators that provide useful ways of manipulating sequences and composing queries. These are the most used operators:

a) Sorting and Grouping (OrderBy, OrderByDescending, ThenBy, ThenByDescending)
b) Aggregation operators (Aggregate):
They are defined for aggregating a sequence of values into a single value
c) Count operator (Count)
d) Four numeric aggregation operators (Max, Min, Sum and Average) 

Let's work on an LINQ to SQL project in ASP.NET. There are pre-requisites:

• Visual Studio 2008/2010 or download the free Visual Studio Express edition. 
• SQL Server 2005/2008 or  download the free SQL Server Express edition.
• .NET Framework 3.5 SP1 full package
• Download Northwind/Pubs sample databases

Sample Project:

Step1:  VS 2008/2010 project

Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "ASP.NET Web Application"

 



Step2: Add a GridView 
Once the project has been added on to VS 2008/2010, we open the "Toolbox" and drag/drop "GridView" control on the "Default.aspx"

Step3: Create a data access layer via LINQ

a) To connect to database, we go to "Tools" >> Connect to Database >>  Connection to Database and fill in the server name and the database nameas illustrated below:

We can view the database connections via "Server Explorer" and it will display tables, stored procedure, View etc. 

b) Right-click on "LINQtoSQLWebApp" project >> Add New Item >> Select "LINQ to SQL Classes" and in the name, type in "Northwind"

 
Obviously, there will be not be any class in the DBML designer surface.

We drag Product, Category, Order and Order_Detail tables to the DBML designer surface and we are done with data access layer/Data model. Each of the table has been generated as .NET object with associated properties such as ProductID, ProductName etc. When we drag the Tables from Northwind database, object mapping is done automatically.
 

Step4: Query the data via LINQ

Just for the demonstration purpose, we will query all the rows the product class via LINQ:

-------------------
NorthwindDataContext db = new NorthwindDataContext();

        var query = from p in db.Products
                         select p;

-------------------

Output:

 
What happened is that when we use LINQ query syntax against data model, LINQ to SQL automatically take this query syntax and transform into regular relational SQL expressions, query run against the database, database returns the content and LINQ to SQL constructs classes such as Product class that returns the data. The best part of LINQ syntax is we get expressive and readable constructs. LINQ is strongly typed as well. 

We can use "Where" operator to query just the "Beverages".


-------------------

NorthwindDataContext db = new NorthwindDataContext();

        var query = from p in db.Products
                         where p.Category.CategoryName == "Beverages"
                         select p;


-------------------

Output: 

We can do some further querying using powerful LINQ constructs. For e.g. in the where clause "CategoryName", we can filter category name by using LINQ operators such as count, Max, Min, Average, Spilt, ToLower, ToUpper, Sum, Remove, Replace, StartsWith etc.


-------------------
NorthwindDataContext db = new NorthwindDataContext();

var query = from p in db.Products
                    where p.Category.CategoryName.StartsWith("c")
                    select p;
--------------------

Output:

Many times, we do not need all the columns to be returned in the GridView. It can be done in LINQ as well:

-------------------
NorthwindDataContext db = new NorthwindDataContext();

var query = from p in db.Products
                    where p.Category.Products.Count > 5
                    select new
                        {
                            p.ProductID,
                            p.ProductName

                        };
-------------------

Output: 

We can use sum, count, average of LINQ operators to generate more powerful query. 


-------------------
NorthwindDataContext db = new NorthwindDataContext();

 var query = from p in db.Products
                    where p.Category.Products.Count > 5
                    select new
                        {
                            p.ProductID,
                            p.ProductName,
                            TotalUnits = p.Order_Details.Sum(o => o.Quantity),
                            Revenue = p.Order_Details.Sum(o => o.Quantity * o.UnitPrice)

                        };
---------------------


Output:

Creating Server side paging of our Query Results
One of the common needs in web scenarios is to be able to efficiently build data paging UI.  LINQ provides built-in support for two extension methods that make this both easy and efficient - the Skip() and Take() methods.

We can use the Skip() and Take() methods below to indicate that we only want to return 10 product objects - starting at an initial product row that we specify as a parameter argument. 


-------------------
NorthwindDataContext db = new NorthwindDataContext();

 var query = from p in db.Products
                    where p.Category.Products.Count > 5
                    select new
                        {
                            p.ProductID,
                            p.ProductName,
                            TotalUnits = p.Order_Details.Sum(o => o.Quantity),
                            Revenue = p.Order_Details.Sum(o => o.Quantity * o.UnitPrice)

                        };
GridView1.DataSource = query.Skip(20).Take(10);
 GridView1.DataBind();
---------------------

Output:

We have just developed a mapping objects to relational data with the Visual Studio designer tool that automated code generators which help us to create the O/RM map objects.


Besides Visual Studio designer tool that are two more options that generate O/RM map objects. 

• SQLMetal.exe - A command line tool that generate both .DBML files and O/RM code for a given database. Its excellent for larger databases. 
• Code Editor - Connecting classes to database objects using code.
  

   
Download the source here. 

References: 

1) Video by Scott Guthrie - (Part 1, Part 2 , Part 3)

2) Complete tutorials series of LINQ to SQL by Scott Guthrie

3) MSDN on LINQ

4) LINQ to SQL: Lambda Expressions