Introduction to Linq in C#

Introduction

Linq stands for Language Integrated Query which makes the concept of querying a first class feature of in Microsoft.NET languages. The data to be queried can take different forms such as XML (Linq to XML), database sources (Linq enabled ADO.NET, Linq to SQL, Linq to DataSet and Linq to Entities) and objects (Linq to objects). Linq is feature which can be extended to other data sources and frameworks such as NHibernate and so on. The essence of Linq is to have the same capabilities of querying to high level languages. That is, if you have used SQL to query relational database, you will find the same principles and techniques in Linq.

In this article, I will illustrate the main concepts of Linq by executing query to a list of objects as the main data source.

Getting started with Linq

In order to illustrate the Linq concepts, let’s start by defining an order business entity. We’re going to execute queries to a list of orders and show the results in the application console. Our abstraction regarding the order business entity is for keeping track of the order identifier, the customer identifier, the product identifier, the list price and the related tax amount (see Listing 1).

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace LinqTestConsoleApp

{

public class Order

{

private int m_nOrderId;

private int m_nCustomerId;

private int m_nProductId;

private decimal m_dListPrice;

private decimal m_dTaxAmount;

 

public Order()

{ }

 

public Order(int nOrderId, int nCustomerId, int nProductId, decimal dListPrice, decimal dTaxAmount)

{

this.m_nOrderId = nOrderId;

this.m_nCustomerId = nCustomerId;

this.m_nProductId = nProductId;

this.m_dListPrice = dListPrice;

this.m_dTaxAmount = dTaxAmount;

}

 

public int OrderId

{

get

{

return this.m_nOrderId;

}

set

{

this.m_nOrderId = value;

}

}

 

public int CustomerId

{

get

{

return this.m_nCustomerId;

}

set

{

this.m_nCustomerId = value;

}

}

 

public int ProductId

{

get

{

return this.m_nProductId;

}

set

{

this.m_nProductId = value;

}

}

 

public decimal ListPrice

{

get

{

return this.m_dListPrice;

}

set

{

this.m_dListPrice = value;

}

}

 

public decimal TaxAmount

{

get

{

return this.m_dTaxAmount;

}

set

{

this.m_dTaxAmount = value;

}

}

}

}

Listing 1

Next step is to create and initialize a list of order to be queried later (see Listing 2).

var arrOrderList = new List<Order>

{

new Order{OrderId=1,CustomerId=10, ProductId=200, ListPrice=12.1M, TaxAmount=1.2M},

new Order{OrderId=2,CustomerId=10, ProductId=300, ListPrice=22.1M, TaxAmount=2.2M},

new Order{OrderId=3,CustomerId=20, ProductId=200, ListPrice=12.1M, TaxAmount=1.2M},

new Order{OrderId=4,CustomerId=30, ProductId=400, ListPrice=13.5M, TaxAmount=1.3M},

new Order{OrderId=5,CustomerId=40, ProductId=200, ListPrice=12.1M, TaxAmount=1.2M},

new Order{OrderId=6,CustomerId=50, ProductId=500, ListPrice=15.6M, TaxAmount=1.5M}

};

Listing 2

The first query is to report the list price of every order for the customer identifier = 10 (see Listing 3).

var objQueryResultset = from objOrderQuery in arrOrderList

where objOrderQuery.CustomerId == 10

select objOrderQuery.ListPrice;

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“List price is: {0}”, objResult.ToString());

}

Listing 3

Let me explain the code in the Listing 3. The first step is to declare a result set variable as an inferred type by using the “var” keyword. After that the query comes using Linq notation.

The first step is the “from” clause which is similar to a “foreach” statement in C#. It takes the collection of objects after the “in” keyword and then reference each item in this collection, in this case the temporal entity objOrderQuery.

The second clause is “where” which is a predicate for each item of the collection, so acting as a filter like its counterpart in SQL. Notice that the predicate in “where” clause is written using the C# syntax, not the SQL syntax. In this case, equal operator is written as “==” and not as “=” as SQL syntax.

Finally, the “select” clause enables to format the output for the result set. The “select“ clause enables inferring the type of the result set variable, in this case is List<decimal>.

Let’s execute a more complex query in Linq. Let’s suppose we want to report the total price (total price=list price+tax amount) to pay, the list price and the tax amount for products with the identifier = 200 (see Listing 4).

var objQueryResultset = from objOrderQuery in arrOrderList

where objOrderQuery.ProductId == 200

select new { TotalPrice=objOrderQuery.ListPrice+objOrderQuery.TaxAmount, objOrderQuery.ListPrice, objOrderQuery.TaxAmount };

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“Total price is: {0}, List price is: {1}, Tax amount is: {2}”, objResult.TotalPrice, objResult.ListPrice, objResult.TaxAmount);

}

Listing 4

In this case, we have used one of most powerful and flexible capabilities of Linq, the use of anonymous type to structure the result set without the need to write custom classes for every possible result set.

Another case is to extent the predicate for each item in the collection using C# syntax. For example, we want the report the same information in Listing 4 but not only for products with identifier = 200 but also for products with identifier = 500 (see Listing 5).

var objQueryResultset = from objOrderQuery in arrOrderList

where objOrderQuery.ProductId == 200 || objOrderQuery.ProductId == 500

select new { TotalPrice=objOrderQuery.ListPrice+objOrderQuery.TaxAmount, objOrderQuery.ListPrice, objOrderQuery.TaxAmount };

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“Total price is: {0}, List price is: {1}, Tax amount is: {2}”, objResult.TotalPrice, objResult.ListPrice, objResult.TaxAmount);

}

Listing 5

Let’s enrich the previous query and order the result set by list price values (see Listing 6).

var objQueryResultset = from objOrderQuery in arrOrderList

where objOrderQuery.ProductId == 200 || objOrderQuery.ProductId == 500

orderby objOrderQuery.ListPrice descending

select new { TotalPrice=objOrderQuery.ListPrice+objOrderQuery.TaxAmount, objOrderQuery.ListPrice, objOrderQuery.TaxAmount };

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“Total price is: {0}, List price is: {1}, Tax amount is: {2}”, objResult.TotalPrice, objResult.ListPrice, objResult.TaxAmount);

}

Listing 6

Now I will illustrate a great capability of Linq. That is the possibility of joining two collections the same way as we use the JOIN operation between tables in relational data sources.

Let’s suppose that we have defined the customer business entities as shown in Listing 7.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace LinqTestConsoleApp

{

public class Customer

{

private int m_nCustomerId;

private string m_strFullname;

 

public Customer()

{ }

 

public Customer(int nCustomerId, string strFullname)

{

this.m_nCustomerId = nCustomerId;

this.m_strFullname = strFullname;

}

 

public int CustomerId

{

get

{

return this.m_nCustomerId;

}

set

{

this.m_nCustomerId = value;

}

}

 

public string Fullname

{

get

{

return this.m_strFullname;

}

set

{

this.m_strFullname = value;

}

}

}

}

Listing 7

Next step is to create and initialize a collection of customer (see Listing 8).

var arrCustomerList = new List<Customer>

{

new Customer{CustomerId=10, Fullname=”John Charles”},

new Customer{CustomerId=20, Fullname=”Maribel”},

new Customer{CustomerId=30, Fullname=”Jack”},

new Customer{CustomerId=40, Fullname=”Ada”},

new Customer{CustomerId=50, Fullname=”Mahesh”},

};

Listing 8

Now the great query with join comes. Let’s display the order information and the full name of every customer instead the customer identifier.

var objQueryResultset = from objOrderQuery in arrOrderList

join objCustomerQuery in arrCustomerList on objOrderQuery.CustomerId equals objCustomerQuery.CustomerId

select new { objOrderQuery.OrderId, objCustomerQuery.Fullname, objOrderQuery.ListPrice, objOrderQuery.TaxAmount};

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“Order id is: {0}, Customer fullname is: {1}, List price is: {2}, Tax amount is: {3}”, objResult.OrderId, objResult.Fullname, objResult.ListPrice, objResult.TaxAmount);

}

Listing 9

If you want to execute a Cartesian product or cross product, the only thing to do is to put the “on” clause off and put another “from” clause (see Listing 10).

var objQueryResultset = from objOrderQuery in arrOrderList

from objCustomerQuery in arrCustomerList                        select new { objOrderQuery.OrderId, objCustomerQuery.Fullname, objOrderQuery.ListPrice, objOrderQuery.TaxAmount};

//Display the result set

foreach (var objResult in objQueryResultset)

{

System.Console.WriteLine(“Order id is: {0}, Customer fullname is: {1}, List price is: {2}, Tax amount is: {3}”, objResult.OrderId, objResult.Fullname, objResult.ListPrice, objResult.TaxAmount);

}

Listing 10

Conclusion

Linq is a great feature of languages in Microsoft.NET platform for defining and executing declarative queries for object collections. This language extension is very similar to SQL, although it has some differences.

Advertisements

2 thoughts on “Introduction to Linq in C#

  1. Pingback: MVP Factor

  2. Pingback: Fernando Garcia Lorea

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s