Bean Software Logo
ASP.NET Database Search Control
 Home
    Shop
    Advertise
    Write For Us
    Affiliate
    Newsletter
    Contact

LINQ with C# Objects, Collections and Arrays

Language integrated query (LINQ) in .NET 3.5 provides querying capabilities to Object Oriented programming languages like C# or VB.NET. This integration of querying capabilities allows compile time checking of query expressions as well as intellisence support from IDE's using the reflection.

 

Linq allows the developer to write query expressions in his own choice of programming language with out having the necessity to learn different databases and query languages. Linq defines a set of standard query operators to perform SQL operations over the different data sources.

LINQ Architecture

Linq architecture is extensible. Linq includes extensions that provides querying capabilities over different data sources.

These extensions are:

Linq to Objects: This provides the ability to query IEnumerable<T>-based information sources which include arrays, collections, list of objects.

Linq to XML: This provides efficient, easy-to-use, in-memory XML manipulation capabilities to provide XPath/XQuery functionality to the programming languages using simple query operators.

Linq to SQL: This gives the ability to access relational data base objects as normal .Net objects. LINQ to SQL integrates SQL based schema definitions to the common language runtime (CLR) type system. This provides strong typing, syntax checking, intellisense over SQL objects, while retaining the expressive power of the relational model and the performance of query evaluation directly in the underlying database.

Linq to DataSet: Provides the ability to query ADO.NET Dataset.

Linq to Entities: Provides the querying capabilities over ADO.NET entities.

A simple Linq query looks like this:

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var lowNums = from n in numbers
  where n < 5
  select n;

In the above query from operator used like foreach keyword, where operator used for conditional selection (restriction) and select is used as projection operator to select the fields from the array. These are called Standard Query Operators.

The query returns an IEnumerable<T> which references to an in-memory object whose CLR type contains an array of integers, whose values are less than 5. These CLR types are called anonymous types because they don't have a type name.

The lownums variable declared by var keyword. The var keyword is used to declare implicitly typed local variables. These variables type is implicitly derived from the object types these variables assigned to.

The same syntax is used when we are querying over objects. A simple query which used to retrieve objects based on a condition is shown below.

List<Product> products = GetProductList();
var expensiveInStockProducts =
from p in products
where p.UnitsInStock > 0 && p.UnitPrice > 20.00M
select p ;
ArrayList list = new ArrayList();
foreach (var i in expensiveInStockProducts)
list.Add(i);
ProductsGrid.AutoGenerateColumns = true;
ProductsGrid.DataSource = list;
ProductsGrid.DataBind();

The above query uses where keyword to find all products that are in stock and cost more than 20.00 per unit.

The variable products represents strongly typed list of product objects which are initialized from GetProductList() method.

The variable expensiveInStockProducts is the type of IEnumerable<product> which references to collection of objects of an anonymous type which is type equivalent to Product type. Because the Select operator projecting the complete product object itself.

The products in the expensiveInStockProducts are added to an ArrayList object list, which is used as the datasource for the GridView object on the aspx page. The AutoGenerateColumns property of the GridView set to True, because of this this the GridView can dynamically generate the column names obtained as the result of the query.

Now the GridView showing all the fields of the Product type this is because the select operator projected complete Product object itself.

Using Select Operator to Project individual fields.

var expensiveInStockProducts = from p in products
   where p.UnitsInStock > 0 && p.UnitPrice > 20.00M
   select new { p.ProductID, p.ProductName };

In the above query, the variable expensiveInStockProducts is the type of IEnumerable<anonymous type> references to objects of an anonymous type which contains string ProductID, string ProductName as part of the type definition. Because the Select operator projecting only

ProductID, ProductName fields of product object. These two fields are represented as string fields because both are declared as strings as part of the Product type definition. Here we are explicitly using the new keyword to dynamically create an anonymous type. This is mandatory because the select operator is projecting a new type definition.

Now the GridView looks like below with only two columns.

Some times we can give a new name to the projected field like below:

var productInfos =
from p in products
select new { p.ProductName, p.Category, Price = p.UnitPrice };

In the above query the UnitPrice field is renamed with Price. And the generated type will have Price as the field name instead of UnitPrice as below.

Sorting and Ordering with LINQ

The sorting functionality is achieved by using the orderby opertaor. The below query outputs the products ordered by the product name field.

var sortedProducts =
from p in products
orderby p.ProductName
select p;

The sorting can be ascending or descending. By default the sorting will be ascending on the field name specified by orderby operator. If we want to order them in descending order we need to explicitly use the descending keyword followed by the field name on which we want to apply the ordering. We can apply a compound sort means the sort will be applied on multiple field from left to right. A simple query using compound sorting is shown below.

var sortedProducts =
from p in products
orderby p.Category, p.UnitPrice descending
select p;

The above query orders the products by category first, after that the results will be sorted by UnitPrice in descending order like below.

Grouping in LINQ

As like sql we can group the objects using the 'group by' operator in Linq. A simple query which demonstrates grouping is shown below.

var orderGroups =from p in products
                    group p by p.Category;
ArrayList list = new ArrayList();
foreach (var i in orderGroups)
{
 
list.Add(i.Key);
/*
foreach ( var g in i.Products )
the above statement is used to iterate through the Product collection in each group g  in the orderGroups
*/
}

The output of the query orderGroups becomes an enumeration of groups (IEnumerable<IGrouping<string, Product>>). Each item of this enumeration defines a group (IGrouping<string, Product>). As in its definition, IGrouping is defined as the combination of the key of the group (p.Category) and the products grouped by this common key. To retrieve these items, we have to enumerate through the group as shown above in the comments.

Normally we will use grouping to get the group count or group max, min of the items. We can get the group count as below. But if we want to use Max, Min operators on the grouped objects the grouped objects must implement the IComparable inorder to do comparision.

var categoryCounts = from p in
    (from c in products
    group c by c.Category)
    select new { p.Key, ProductCount = p.Count() };

For simplicity the same query can be written using into operator with out using the nested query as shown below.

var categoryCounts =
from p in products
group p by p.Category into g
select new { Category = g.Key, ProductCount = g.Count() };

The output of the above query looks like below.

Set Operators (Distinct, Union, Intersect, Except)

The Distinct operator is used to eliminate duplicate elements from a sequence.

var categoryNames = (
from p in products
select p.Category)
.Distinct();

The output of the query gives an enumerable object and which can be enumerated to get unique categories.

The Union operator is used to combine the objects of two collections, but the output contains only distinct objects from both the collections.

The Intersect operator is used to find the objects which are common in both the collections and outputs the same.

The Except operator is used to find the objects that are present in the first collection but not present in the second collection. An example which shows these three operators usage is shown below.

var productFirstChars =
from p in products
select p.ProductName[0];
var categoriesFirstChars =
from c in products2
select c.Category[0];
var uniqueFirstChars = productFirstChars.Union(categoriesFirstChars);
var IntersectFirstChars = productFirstChars.Intersect(categoriesFirstChars);
var ExceptFirstChars = productFirstChars.Except(categoriesFirstChars);

Join in LINQ

Joins are the most important function of SQL Operators. Linq supports join operations using the joinoperator. The join operator performs an inner join of two collections based on matching keys extracted from the elements. An equijoin can be implemented as below:

string[] categories = new string[]{
"Beverages",
"Condiments",
"Vegetables",
"Dairy Products",
"Seafood" };
var q =
from c in categories
join p in products on c equals p.Category
select new { Category = c, p.ProductName };

The above query joining the categories array with the products collection and the condition for joining is equals means if any category string is matched with category field of the Product object then that pair will be added to join output. And the select operator projecting only the category string and ProductName field of Product. The output will look like

This tutorial is written by RELIANCE CONSULTING.


Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   


comments powered by Disqus

Related articles:

1. Converting Color Photo To Black & White Photo
2. Long Time Operations in ASP.NET
3. How To Clear Selected Date In Calendar Control?