C# LINQ Tutorial with Examples

Code used in this article is available here on GitHub.

Introduction

LINQ stands for ‘Language Integrated Query’. As the name suggests, using LINQ, one can directly query data using C# language. The advantage of using LINQ is, that it is data independent. LINQ can be used to query data from SQL databases, XML documents etc. It can also be used to query in memory data like lists etc.

LINQ can be used against any data set that implements IEnumerable interface. For eg. Lists already implement IEnumerable interface. Hence we can write LINQ queries on them. Now why does LINQ need IEnumerable to work with? This is because the LINQ methods like ‘OrderBy’, ‘Where’ etc. are extension methods implemented on IEnumerable.

LINQ queries can be written in two ways: Query syntax and Method syntax.

 Query syntax begins with ‘from’ and ends with ‘select’. Query syntax is closer to sql queries. But some operators like ‘Take’, ‘Count’, ‘Max’ etc. cannot be implemented in Query syntax.

Let’s have a look at how we can implement queries in the above syntaxes. For these purposes I will be creating a console application. The code can be reached from GitHub here too.

We will be creating two classes as below: One for Employee and one for Project.

I will be creating two lists for employees and projects and initializing them as follows:

Now that we have our data ready, let’s start with our queries.

WHERE

Let’s start with a simple query to filter out employees whose names start with the letter ‘T’.

The code in the query syntax will be as below:

Running the above query in console application will fetch you the below results.

Now let’s write the same query in method syntax.

The above syntax ‘ e => e.EmployeeName.StartsWith(“T”)’ inside the where clause is called a lambda expression and heavily simplifies querying.

Run the console application and you will find the same results displayed as earlier.

Notice the difference in the syntax between the two types. Performance wise both queries are equal and choosing a particular type depends on the user’s preference.

Now let’s analyze the where extension method here.

The where method has been made available to us, but we need to specify the parameters on which we want the ‘where’ to work. For eg, in the above example we have used ‘StartsWith”T”’.

If we look at the above screenshot, we see that the ‘Where’ method takes a Func. Func is nothing but an encapsulated delegate.  A delegate in turn is a function pointer. This Func takes in Employee and returns a bool. A func can take upto 16 parameters, the last one of which will always be a return type.

So what does this all mean? To sum up, the func will take in an employee object and return a true or false based on the condition that the user specifies. So in our case each employee object in our collection will be passed to the func, which will in turn evaluate the StartsWith(“T”) condition and return true or false.

But why use Func instead of a delegate? If we use a delegate, the syntax will be much more complicated and also we will need an extra line to define the delegate signature. Using funcs simplifies our querying.

Now let’s analyze a few other extension methods that LINQ provides.

ORDER

If we want to order employee name by ascending, we write it as below:

The output will be as below:

In case you want to order employee name in the descending order, we will use the following lines:

The output will be as below:

Then By

Lets order all the entries by projectid ascending:

Now we want that for projectid 101, the employees should get ordered by name descending ie. Atul should come above Theran. Let’s check out the syntax in both the types for the same.

TAKE

If we want to select a particular number of rows, we use the ‘Take’ method. Notice how the query syntax does not support the ‘Take’ method. To use it we can, put the query syntax in a bracket and then use Take.

This will select two rows.

SKIP

Similarly to the ‘TAKE’ operator we have the ‘SKIP’ operator. When we use skip(2), the query will skip the first two records from the result set and display the results.

The result will be as follows:

GROUP

Lets group our data according to project id.

We need to use the above query to filter by group. Notice how in console.WriteLine we are using ‘Key’. This key will refer to the attribute by which the grouping has occurred, in this case the projectid. The above query will list the number of employees in each project.

FIRST

If we want to get the first record, we use FIRST. If the query returns null, then FIRST will throw an exception even before any of its property is accessed.

If the where clause is uncommented, then an exception will be thrown at the statement enveloped in red.

If the ‘Where’ clause is commented, the output will be as below:

FIRST OR DEFAULT

An alternative to ‘First’ is to use ‘FirstOrDefault’. In case the query returns nothing, FirstOrDefault will show the default values. e.g.0 for an int value. But when we try to access some property of the query we will get an exception. For example in the below screenshot, if the where clause is uncommented, the variable querySyntax9 will be null. But it will throw an error , if we try to access querySyntax9.EmployeeName and not where the variable is defined.

So it is better to use FirstOrDefault and then check whether our query result is null before accessing any property of our result set.

Result is as below:

JOIN

Now suppose we want to display employees with their project name. This can be done with a join.

Employees and their projects will be displayed as below:

LEFT JOIN

To test for left join I will be modifying the data as follows:

Now Tuba has no project.

Let’s rewrite the query to display all the employees, and to display “NULL”, if they do not have a project mapped against them.

The output will be displayed as below:

Deferred Execution:

In LINQ, the concept of deferred execution is an important one.

Let’s go back to one of our original queries:

The query is actually executed when the foreach statement is executed. Therefore, many a times, the exception will not occur where the variable is declared, but during foreach when the query is actually executed. So, methods like ‘where’, implement deferred execution, also known as lazy loading. It will not execute unless it enumerates. So, how to do we force queries to execute immediately? We can use toList() for the same. Count() also leads to immediate execution of the query.

Streaming & NonStreaming operators:

Deferred execution operators can further be classified as Streaming and Non-streaming. Streaming operators will not operate on all the rows before producing a result. For eg, when in the earlier example when we were filtering records based on ‘StartsWith(“T”)’, it will evaluate the first row and then if it the employee name starts with T, it will go to Console.Writeline and execute the code and come back and evaluate the second row and so on.

For non-streaming operators like ‘group’, all the rows will be evaluated first and then the result will be produced.

This concludes our introduction to LINQ. I hope that you will now have an idea about how LINQ operates. 

Code used in this article is available here on GitHub.