Blog Post by Murat Yaşar


Posted on Friday, October 08, 2010 2:20:15 PM and it has been read 3360 times since then.


LINQ

LINQ stands for Language Integrated Query. MSDN describes LINQ as follows;

"LINQ is a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities."

In order for LINQ to work, some new features have been added to C#. I will be talking about these new features ahead of this writing. LINQ can be used for querying any kind of data from within the language which compatible with .NET.

We can query relational database system, XML, object, dataset and any type of data if they meet some basic requirements for LINQ to work against them. I will also be talking about what these basic requirements are, as much as I can.

LINQ always uses consistent and stable syntax while reaching data and querying them.

We can use LINQ to query completely different data source, read them, join them and create a new data source from those different data sources.

As I told you before some new features have been added into C#. These features are;
- Query Expressions
- Anonymous Types
- Extension Methods
- Lambda Expressions
- Implicitly typed local variables
- Object Initializers

LINQ to Object:
First and foremost requirement for LINQ to work is that the datasource (object) we are querying against must implement IEnumerable interface. This is crucial. If the object did not implement this interface, we would not be able to work with LINQ against this data source (object).

Array, List, Dictionary some of the classes which implement this interface automatically by its nature. We can then query this collection which consists of this type of object. These objects stay at ram (random access memory) and can be accessible by LINQ to Object provider very quickly.

LINQ to Dataset:
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. We can do all sorts of query against ADO.NET dataset. Normally there is no query engine built-in with datasets. For this reason, once we load the data and bind it to dataset, we can not query this data. When the data is loaded, there is often a need to perform additional queries on the loaded data. This is where LINQ to DataSet comes in. We can work with both strongly typed and untyped datasets. While working with strongly typed dataset is easy, it needs a little bit more effort for working with untyped dataset when it comes to LINQ. Because we also know the schema while working with strongly typed dataset, LINQ uses this schema and writing query becomes easier. When we work with untyped dataset, writing LINQ query needs a little bit more work but as long as we use correct field names inside our LINQ query it will also work smoothly.

LINQ to Entities:
By using this provider, we can query against Entity Data Model. Entity Data Model makes possible application to communicate with data source by enabling data source to acts like collection of objects. We can think this provider like LINQ to SQL but the data source here is not limited to be only a SQL Server. Every kind of data can work with this provider as long as they meet the certain requirements. Apart from this, LINQ to Entities does not do one-to-one mapping between data and its representative classes like the LINQ to SQL does. Instead, it uses a middle layer between data and the application. It gives more flexibility. Of course to be able to get this benefit from that, we need some extra code to write.

LINQ to SQL:
This provider gives us the ability to work with data stored in relational database system by using LINQ queries. It provides shortcuts and some advantages. It controls the communication between LINQ query and data.

System.Data.Linq.DataContext enables us to do pretty much everything on database such as retrieving, inserting and modifying data.

LINQ to SQL can be thought like LINQ to Object. Here, we do querying using classes which represents data on SQL Server. We can take advantage of ORM (Object Relational Mapping) tools for associating SQL Server data with simulated classes. It gives us objects which can be queried against. While ORM can create these classes which represents data on SQL Server, we can also create by ourselves by writing again a little bit more code.

I will try to explain what I meant by writing a simple example. In this example I will be using Northwind database in my local SQL Server instance.

First, I am writing a class which will act like Customer table in Northwind database. I will be doing the necessary mapping by using appropriate .NET class. Make sure you have added System.Data.Linq.Mapping with using statement. We are going to use this Customer class as a parameter when we retrieve data from database. It is going to enable us to treat them as Customer object collection.

namespace LINQ_Samples.Class
{
    [Table(Name="Customers")]
    public class Customer
    {
        [Column(IsPrimaryKey = true)]
        public string CustomerID { get; set; }
        [Column(Name="CompanyName")]
        public string SirketAdi { get; set; }
        [Column(Name="ContactName")]
        public string YetkiliKisi { get; set; }
        [Column(Name = "Country")]
        public string Ulke { get; set; }
        public override string ToString()
        {
            return SirketAdi; //CompanyName in Turkish
        }
    }
}

This step has been done manually by us. We could have used ORM tools and create this without writing single line of code.

Above we create class which represents table, its properties represent columns inside table. We actually did a mapping.

private void Form_Load(object sender, EventArgs e)
{
    DataContext datacontext = 
        new DataContext("Data Source=.;Initial Catalog=Northwind;User ID=demo;Password=demo;");
    
    var customerTable = datacontext.GetTable'<'Class.Customer'>'();
    var customerArray = from customer in customerTable
                        where customer.Ulke == "Canada"
                        select customer;
    dataGridView1.DataSource = customerArray;
}

"Please remove single quote around Class.Customer when you copy and paste the code."

When we execute above code block, we will get the following output.

/BlogEntryImages/69/linqtosql_1.jpg

Let us say that we also want to see the T-SQL statement which has been sent down to SQL Server from LINQ query in our code. In this case we can simply add a line to our code and will be able to see exact T-SQL sent down to SQL Server.

MessageBox.Show(customerArray.ToString(),"The query sent down to SQL Server");

The following shows us the T-SQL sent down to SQL Server.

/BlogEntryImages/69/linqtosql_2.jpg

If we change the above query with below query, we will get not all columns but only the ones we care about.

var customerArray = from customer in customerTable
                    where customer.Ulke == "Canada"
                    select new
                    {
                        customer.SirketAdi
                    };

The output will be the following.
/BlogEntryImages/69/linqtosql_3.jpg
 

LINQ to XML:
This provider gives us a lot of flexibility when we work with XML data. Provides better XML Document Object Model operation. We can update, query and create new XML document by using this provider. Some new classes added into .NET framework in order to work with this provider and make things easier.

System.Xml.Linq.XObject, System.Xml.Linq.XElement, System.Xml.Linq.XAttribute, System.Xml.Linq.XComment are only few of them which added newly into .NET Framework. These are added especially for working with LINQ to XML.

We are able to do all sorts of things about XML by using XMLDocument class but System.XML.Linq namespace gave us new classes which are very easy to use and makes it easy to read code. We use "from" and "select" to create XML Document and insert data into XML document. Let us take a look at the following example to understand clearly what we can do.

private void Form5_Load(object sender, EventArgs e)
{
	DataContext datacontext =
		new DataContext("Data Source=.;Initial Catalog=Northwind;User ID=demo;Password=demo;");
	var customerTable = datacontext.GetTable<class.customer>();
	System.Xml.Linq.XDocument doc = new System.Xml.Linq.XDocument(
		new System.Xml.Linq.XDeclaration("1.0","utf-8","true"),
		new System.Xml.Linq.XElement("Customers",
			from customer in customerTable                    
			orderby customer.SirketAdi
			where customer.Ulke == "Canada"
			select new System.Xml.Linq.XElement("Customer",
				new System.Xml.Linq.XAttribute("CustomerID", customer.CustomerID),
				new System.Xml.Linq.XElement("CompanyName", customer.SirketAdi),
				new System.Xml.Linq.XElement("ContactName", customer.YetkiliKisi),
				new System.Xml.Linq.XElement("Country", customer.Ulke)
				)
			)
		);
}

Above code block retrieves data from Northwind database's Customer table only if the country name is Canada for that current row inside Customer object collections. Then we create an XML document with these data by specifying the format we want. The output is shown below. These document has not been written on disk as a file yet. We can use this XML as we wish.

<customers>
  <customer customerid="BOTTM">
    <companyname>Bottom-Dollar Markets</companyname>
    <contactname>Elizabeth Lincoln</contactname>
    <country>Canada</country>
  </customer>
  <customer customerid="LAUGB">
    <companyname>Laughing Bacchus Wine Cellars</companyname>
    <contactname>Yoshi Tannamuri</contactname>
    <country>Canada</country>
  </customer>
  <customer customerid="MEREP">
    <companyname>Mère Paillarde</companyname>
    <contactname>Jean Fresnière</contactname>
    <country>Canada</country>
  </customer>
</customers>

/BlogEntryImages/69/linqtosql_4.jpg

You can see basic graphical explanation for Language Integrated Query (LINQ)


Syntax for LINQ Queries:
Everything we do in T-SQL is possible to do by using LINQ syntax from within code. We must use "from" keyword in every LINQ query. By using this keyword, we specify where data comes from. Other than this "from" keyword, we can either use some other keywords or not use any other keyword in our query depends on what we are trying to achieve.

A Linq query consists of this 3 following stages:
1- Getting datasource
2- Writing query
3- Executing query


Let us get started with this simple example.

namespace LINQ_Samples
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string[] names = { "Murat", "Yasar", "Dogan", "Mehmet", "Demir", "Yasin" };
        private void Form1_Load(object sender, EventArgs e)
        {
            listboxSource.Items.AddRange(names);
        }
        private void buttonExec_Click(object sender, EventArgs e)
        {
            listboxResult.Items.Clear();
            var resultNames = from name in names
                              where name.StartsWith(textBox1.Text)                              
                              orderby name ascending
                              select name;
            
            foreach (string resultName in resultNames)
            {
                listboxResult.Items.Add(resultName.ToString());
            }
        }   
        
    }
}

We press the F5 in Visual Studio. Write "M" and press the button on the form. By doing this, we send a parameter to the query and gets result inside listbox which is right side on the form. In the beginning, we have an array which contains string type names. By pressing "M" we specify that we want to get only those of which starts with "M" letter among names. As a result, we got only those which starts with "M".

We declared names in an array list. Since array implements IEnumerable, we were able to use LINQ query against this data source without implementing anything. Array implements IEnumerable by its nature. You can see it from reflector.

/BlogEntryImages/69/linqtosql_5.jpg

/BlogEntryImages/69/linqtosql_6.jpg

If we had not used a data source which did not implement IEnumerable interface, we had to implement this interface manually.

/BlogEntryImages/69/linqtosql_7.jpg
Above image shows the output when we execute the code.

Another important thing is to keep in mind is when you write a LINQ query actually you are just defining your needs. The query does not get executed until you need the result set which will come from your query. We define our needs and wait until we really need that result set. Once we start using that result set which will be coming from our query, we then start executing our query.

For instance, the code block above, the query inside that code block does not get executed until the program reaches the foreach line. Once foreach line gets executed, program goes to query and retrieves result from that and process the result set inside foreach loop.

If we use aggregate function (count, sum, average, min, max) inside our LINQ query then .NET runtime will execute the query instantly in order to calculate result set.

If we want to get query result set immediately, we can use ToList() or ToArray() methods, assign retrieved result set into variables and use it as we would like to.

If we want to retrieve multiple result from a single LINQ query, we can use one of the following methods.
1- We can create a class which will include our desired output values as property. Then we can create a new instance from that class and use it in our select statement inside the query to be able to get our values from this class's properties.
2- Let the compiler do its job and retrieve our output values by using anonymous type inside the query. Anonymous type is a new feature added .NET framework.

Let me explain this with a simple example. We will try to list some of the attributes of files inside a folder in our computer.

private class OurFileInfo
{
    public string name;
    public Int64 size;
    public DateTime createTime;
}
private void buttonExecQuery_Click(object sender, EventArgs e)
{
    var ourFiles = new DirectoryInfo(@"C:\LinqSamplesFolder").GetFiles();
    var result = from file in ourFiles
                 where file.Length > 10240 //(10 KB in size)
                 orderby file.Name
                 select new OurFileInfo
                 {
                     name = file.Name,
                     size = file.Length,
                     createTime = file.CreationTime
                 };
    string formatName = string.Empty;
    foreach (var currentFile in result)
    {
        listbosFilesInfo.Items.Add(
            string.Format("[{0}] --- [{1}] --- [{2}]",
            "File Name: " + currentFile.name, 
            "File Size: " + currentFile.size.ToString(),
            "Creation Date: " + currentFile.createTime.ToString()));
    }
}

After clicking button, the relevant code will get executed and it will give us the following output.

/BlogEntryImages/69/linqtosql_8.jpg

Inside our LINQ query, we create a new instance of OurFileInfo class for each file which meets the criteria specified in "where" statement. We assign the appropriate file properties to class property fields for the current file we are operating on inside query. By doing so, we get collection of OurFileInfo object. Finally we use foreach loop in order to get each object's properties and do what we want to.

Now, we will try to get the same output by not using manually created class which we did in the above example. For doing this, we are going to use anonymous types. Anonymous type is a new feature added .NET framework. It is all about dynamically created by compiler at runtime. We do not need to know much about this anonymous type because .NET takes care of everything at runtime. Compiler automatically creates a class based on types specified in select clause and gives us cleaner syntax to work with. Everything occurs behind the scenes but if you want to delve into it, you can use IL DASM (http://en.wikipedia.org/wiki/.NET_metadata) which is given by .NET to inspect things further.

private void buttonExecQuery_Click(object sender, EventArgs e)
{
    var ourFiles = new DirectoryInfo(@"C:\LinqSamplesFolder").GetFiles();
    var result = from file in ourFiles
                 where file.Length > 10240 //(10 KB in size)
                 orderby file.Name
                 select new
                 {
                     name = file.Name,
                     size = file.Length,
                     createTime = file.CreationTime
                 };
    string formatName = string.Empty;
    foreach (var currentFile in result)
    {
        listbosFilesInfo.Items.Add(
            string.Format("[{0}] --- [{1}] --- [{2}]",
            "File Name: " + currentFile.name, 
            "File Size: " + currentFile.size.ToString(),
            "Creation Date: " + currentFile.createTime.ToString()));
    }
}

The only difference between above code block and the previous code block is that I did not specify a type after "select new" statement. C# compiler automatically creates anonymous type when it sees there is not any type specified and gives me the same output as before.

/BlogEntryImages/69/linqtosql_9.jpg

Extension Methods:

Let us write an example which will give us odd numbers from within series of number list.

private void buttonGetOdd_Click(object sender, EventArgs e)
{
    int[] numbers = { 1, 5, 6, 9, 11, 12, 15, 20};
    var odd = from number in numbers
              where number % 2 != 0
              orderby number ascending
              select number;
    StringBuilder result = new StringBuilder();
    foreach (int number in odd)
    {
        result.Append(number.ToString());
        result.Append(",");
    }
    textboxOdd.Text = result.ToString().TrimEnd(',');
    
}

Above code block gives us only odd numbers inside series of numbers. We get the following output when we execute the code block above.

/BlogEntryImages/69/linqtosql_10.jpg

Let us get the same output by using slightly different code block.

private void buttonGetOdd_Click(object sender, EventArgs e)
{
	int[] numbers = { 1, 5, 6, 9, 11, 12, 15, 20};
	List<int> odd = new List<int>();
	StringBuilder result = new StringBuilder();
	foreach (int number in numbers)
	{
		if (number % 2 != 0)
		{<br>
			result.Append(number.ToString());
			result.Append(",");
		}
	}
	textboxOdd.Text = result.ToString().TrimEnd(',');	
}

Previous two code blocks does the same job and gives the same output. The only difference between them is the first one uses LINQ query but the second one does not. In the first code block, the query will get executed when we reach foreach line and we will get the result set from that query which meets the criteria specified in where clause. The control about the number whether it is odd or not will be done once in query. The second code block does the control whether the number is odd or not for every loop so that it will create may be not important but some minor overhead. It is not important for this kind of code block but it will complexify things as project gets bigger.

Extension method means, adding a method into already existed class without modifying nor changing its structure. By doing so, class gets a new method. I have already said that the data source we are querying against, has to consist of objects which implements IEnumerable interface in order LINQ to work with. Because IEnumerable exists since very early stage of .NET framework, we somehow needed to implement some methods into this interface in order for us to work with LINQ queries and use them inside our query syntax. Extension methods came to the rescue at this point. Extension methods made possible LINQ to work but they are not for only LINQ. We can use extension method technology anywhere in our code if it suits.

If we give to extension method a name which already exists inside the class we are extending to, compiler will not use extension method instead it is going to use original method inside class.

There are some rules to create extension methods that we must be aware of:
- We must define the method as public.
- We must use this keyword as the first parameter for our extension method definition. "this" keyword must be followed by the type name we are extending to.
- We must define the method inside a static class.
- We must define the method as static method.

You can also check the MSDN web site specific to Extension Methods.
(http://msdn.microsoft.com/en-us/library/bb383977.aspx)

With these pre-requisites, let us create our first extension method.

namespace LINQ_Samples
{
    public static class Extensions
    {
        public static bool CheckIfItIsOdNumber(this int value)
        {
            return value % 2 != 0;
        }
    }
}

Because we are extending integer class, we use "int" right after "this" keyword. If you take a look at the following screen output, you will get some idea how extension method we have just created becomes available to our code.
/BlogEntryImages/69/linqtosql_11.jpg
Writing our code block once again.

private void buttonGetOdd_Click(object sender, EventArgs e)
{
    int[] numbers = { 1, 5, 6, 9, 11, 12, 15, 20 };
    var odd = from number in numbers
              where number.CheckIfItIsOdNumber()
              orderby number ascending
              select number;
    StringBuilder result = new StringBuilder();
    foreach (int number in odd)
    {
        result.Append(number.ToString());
        result.Append(",");
    }
    textboxOdd.Text = result.ToString().TrimEnd(',');    
}

When we execute the above code block, we will get the same result as we did previously but we used extension method in our LINQ query.

At this point, we are able to say that the methods, works with LINQ, are all implemented for IEnumerable interface by using this technique. For instance;

var odd = from number in numbers
          where number.CheckIfItIsOdNumber()
          orderby number ascending
          select number;

CheckIfItIsOdNumber() implemented by using extension method.

Anonymous Methods:
If we do not need to use a method once again, it is better to use anonymous methods. It is basis for lambda expression. We can send a procedure block as parameter and compiler takes care of the rest.
MSDN describes anonymous method as follows:
" Creating anonymous methods is essentially a way to pass a code block as a delegate parameter. By using anonymous methods, you reduce the coding overhead in instantiating delegates by eliminating the need to create a separate method."

I encourage you to also take a look at the following web site.
http://weblogs.asp.net/dwahlin/archive/2007/04/23/The-Power-of-Anonymous-Methods-in-C_2300_.aspx


Lambda Expression:
It makes possible for us to pass where clause as parameter. It is possible to use delegate in this situation but lambda expression provides us shorter and easy to read code.

private void LambdExpression_Load(object sender, EventArgs e)
{
    var listOfFiles = new System.IO.DirectoryInfo(@"C:\LinqSamplesFolder").GetFiles();
    var resultFiles = listOfFiles.Where(res => res.Length > 10000);
    listBox1.Items.AddRange(listOfFiles);
    foreach (System.IO.FileInfo file in resultFiles)
    {
        listBox2.Items.Add(file.Name);
    }          
}

When we execute this block of code, we will get the following output. In this code, lambda expression is being used with "Where" extension method.

/BlogEntryImages/69/linqtosql_12.jpg

The work we can do with this new technology is only limited to our imagination.

There are many things to write about for LINQ. It is of course not possible list them all in a single article. I just wanted to give you my point of view. I usually get benefit from similar kind of articles on the internet. I wanted to write about this subject, thinking may be I can give some basic idea to somebody to get started.

I do not claim that the things I wrote in here is 100% correct. That does not mean I wrote wrong information either but giving you just some example and let you do your own research.

Have a great day.


(In order to use this feature, you have to register.)

Tag Related Blog Entries

Creating Connection and Making CRUD Operations From Your .NET Project to Oracle DB

Thursday, December 29, 2022 0   1425  

Scratching Beneath the Surface

Friday, March 11, 2022 0   1940  

Calculate Time Difference Between Two Dates Excluding Weekend and Special Holidays

Monday, March 07, 2022 0   1726  

SQL Server Database table and ready script with color name information in English and Turkish

Wednesday, June 26, 2019 0   4367   1

Creating a Count Widget that uses Hana DB as a data source and displaying it in SAP Business One Cockpit

Thursday, April 11, 2019 1   3271  

Creating a Crystal Report that uses Hana as a data source and importing this report into SAP Business One

Saturday, January 19, 2019 1   3560  

Countries Android Mobile Application

Saturday, April 23, 2016 0   3687  

Compare Tables Row By Row in PostgreSQL

Sunday, June 21, 2015 2   14000  

Successfully Going Live With The New yasarmurat.com

Tuesday, May 26, 2015 0   3527  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   8022  

File Is Being Used By Another Process

Monday, August 29, 2011 1   3917  

Populate Nested TreeView In Asp.Net Using Common Table Expression In SQL Server

Sunday, May 01, 2011 0   8566   5

Converting String From Turkish To English

Sunday, December 19, 2010 0   2277  

DataBinding GridView In Asp.NET Application By Using PostgreSQL as DataSource

Sunday, October 10, 2010 0   12196  

Indicative Exchange Rates Announced by the Central Bank of Turkey For Daily Basis

Saturday, May 22, 2010 0   2401   1

Debugging in .NET

Saturday, February 13, 2010 0   2023  

VS 2010 / .NET 4 Release Candidate

Tuesday, February 09, 2010 0   2266  

Asp.Net Application Warm Up By Using Windows Service

Thursday, February 04, 2010 6   15211   6

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part III

Saturday, January 23, 2010 0   4207  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part II

Saturday, January 23, 2010 0   7969  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part I

Saturday, January 23, 2010 1   6502  

Syntax not understood error for robots.txt file in Google Webmaster Tools

Tuesday, December 29, 2009 2   5059