LINQ For Beginners

LINQ stands for Language Integrated Query. In this article, we will dive deep into the basics of LINQ.

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 example, Lists already implement the 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.

The code used in this article is available here on GitHub

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.

  1. class Employee    
  2.     {    
  3.         public int EmployeeId { get; set; }    
  4.         public string EmployeeName { get; set; }    
  5.         public int ProjectId { get; set; }    
  6.     }    

LINQ For Beginners

  1. class Project  
  2.     {  
  3.         public int ProjectId { get; set; }  
  4.         public string ProjectName { get; set; }  
  5.     }  

LINQ For Beginners

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

  1. public static List<Employee> employees = new List<Employee>();  
  2. public static List<Project> projects = new List<Project>();  
  3.   
  4.         static void Main(string[] args)  
  5.         {  
  6.             InitializeEmployees();  
  7.             InitializeProjects();  
  8.          }  
  9. public static void InitializeEmployees()  
  10.         {  
  11.             employees.Add(new Employee  
  12.             {  
  13.                 EmployeeId = 1,  
  14.                 EmployeeName = "Tuba",  
  15.                 ProjectId = 100  
  16.             });  
  17.   
  18.             employees.Add(new Employee  
  19.             {  
  20.                 EmployeeId = 2,  
  21.                 EmployeeName = "Atul",  
  22.                 ProjectId = 101  
  23.             });  
  24.   
  25.             employees.Add(new Employee  
  26.             {  
  27.                 EmployeeId = 3,  
  28.                 EmployeeName = "Theran",  
  29.                 ProjectId = 101  
  30.             });  
  31.         }  

LINQ For Beginners

  1. public static void InitializeProjects()    
  2.         {    
  3.             projects.Add(new Project    
  4.             {    
  5.                 ProjectId = 100,    
  6.                 ProjectName = "ABC"    
  7.     
  8.             });    
  9.     
  10.             projects.Add(new Project    
  11.             {    
  12.                 ProjectId = 101,    
  13.                 ProjectName = "PQR"    
  14.     
  15.             });      
  16.         }    
LINQ For Beginners

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.

  1. //WHERE  
  2.             var querySyntax1 = from employee in employees  
  3.                               where employee.EmployeeName.StartsWith("T")  
  4.                               select employee.EmployeeName;              
  5.   
  6.             Console.WriteLine("Where in querySyntax------");  
  7.             foreach (var item in querySyntax1)  
  8.             {  
  9.                 Console.WriteLine(item);  
  10.             }  
LINQ For Beginners

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

LINQ For Beginners 

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

  1. var methodSyntax1 = employees.Where(e => e.EmployeeName.StartsWith("T"));  
  2.             Console.WriteLine("Where in methodSyntax-----");  
  3.             foreach (var item in methodSyntax1)  
  4.             {  
  5.                 Console.WriteLine(item.EmployeeName);  
  6.             }  
  7.   
  8.             Console.WriteLine('\n');  

LINQ For Beginners

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.

LINQ For Beginners 

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 up to 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.

  1. //ORDER BY ASCENDING  
  2. var querySyntax2 = from employee in employees  
  3.                    orderby employee.EmployeeName  
  4.                    select employee.EmployeeName;  
  5.   
  6.   
  7. var methodSyntax2 = employees.OrderBy(e => e.EmployeeName);  
  8.   
  9. Console.WriteLine("Order by ascending in querySyntax------");  
  10. foreach (var item in querySyntax2)  
  11. {  
  12.     Console.WriteLine(item);  
  13. }  
  14.   
  15. Console.WriteLine("Order by ascending in methodSyntax------");  
  16. foreach (var item in methodSyntax2)  
  17. {  
  18.     Console.WriteLine(item.EmployeeName);  
  19. }  
  20.   
  21. Console.WriteLine('\n');  

LINQ For Beginners

The output will be as below.

LINQ For Beginners 

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

  1. //ORDER BY DESCENDING  
  2.             var querySyntax3 = from employee in employees  
  3.                               orderby employee.EmployeeName descending  
  4.                               select employee.EmployeeName;  
  5.   
  6.   
  7.             var methodSyntax3 = employees.OrderByDescending(e => e.EmployeeName);  
  8.   
  9.             Console.WriteLine("Order by descending in querySyntax------");  
  10.             foreach (var item in querySyntax3)  
  11.             {  
  12.                 Console.WriteLine(item);  
  13.             }  
  14.   
  15.             Console.WriteLine("Order by descending in methodSyntax------");  
  16.             foreach (var item in methodSyntax3)  
  17.             {  
  18.                 Console.WriteLine(item.EmployeeName);  
  19.             }  
  20.   
  21.             Console.WriteLine('\n');  

LINQ For Beginners

The output will be as below.

LINQ For Beginners 

Then By

Let’s order all the entries by projectid ascending.

LINQ For Beginners
 
LINQ For Beginners 

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

  1. //THEN BY          
  2. var querySyntax4 = from employee in employees  
  3.                    orderby employee.ProjectId, employee.EmployeeName                   descending  
  4.                    select employee;  
  5.   
  6. var methodSyntax4 = employees.OrderBy(e => e.ProjectId).ThenByDescending(e => e.EmployeeName);  
  7.   
  8. Console.WriteLine("Then by in querySyntax------");  
  9. foreach (var item in querySyntax4)  
  10. {  
  11.     Console.WriteLine(item.EmployeeName + ":" + item.ProjectId);  
  12. }  
  13.   
  14. Console.WriteLine("Then by in methodSyntax------");  
  15. foreach (var item in methodSyntax4)  
  16. {  
  17.     Console.WriteLine(item.EmployeeName + ":" + item.ProjectId);  
  18. }  
  19.   
  20. Console.WriteLine('\n');  

LINQ For Beginners 

LINQ For Beginners

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.

  1. //TAKE  
  2. var querySyntax5 = (from employee in employees  
  3.                    select employee).Take(2);  
  4.   
  5.   
  6. var methodSyntax5 = employees.Take(2);  
  7.   
  8.   
  9. Console.WriteLine("Take in querySyntax------");  
  10. foreach (var item in querySyntax5)  
  11. {  
  12.     Console.WriteLine(item.EmployeeName);  
  13. }  
  14.   
  15. Console.WriteLine("Take in methodSyntax------");  
  16. foreach (var item in methodSyntax5)  
  17. {  
  18.     Console.WriteLine(item.EmployeeName);  
  19. }  
  20.   
  21. Console.WriteLine('\n');  

LINQ For Beginners

 
LINQ For Beginners 

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.

  1. //SKIP  
  2. var querySyntax6 = (from employee in employees  
  3.                     select employee).Skip(2);  
  4.   
  5. var methodSyntax6 = employees.Skip(2);  
  6.   
  7. Console.WriteLine("Skip in querySyntax------");  
  8. foreach (var item in querySyntax6)  
  9. {  
  10.     Console.WriteLine(item.EmployeeName);  
  11. }  
  12.   
  13. Console.WriteLine("Skip in methodSyntax------");  
  14. foreach (var item in methodSyntax6)  
  15. {  
  16.     Console.WriteLine(item.EmployeeName);  
  17. }  
  18.   
  19. Console.WriteLine('\n');  

LINQ For Beginners

The result will be as follows:

LINQ For Beginners 

GROUP

Let’s group our data according to project id.

  1. //GROUP  
  2.             var querySyntax7 = from employee in employees  
  3.                               group employee by employee.ProjectId;  
  4.   
  5.   
  6.             var methodSyntax7 = employees.GroupBy(e => e.ProjectId);  
  7.   
  8.             Console.WriteLine("Group in querySyntax------");  
  9.             foreach (var item in querySyntax7)  
  10.             {  
  11.                 Console.WriteLine(item.Key + ":" + item.Count());  
  12.             }  
  13.   
  14.             Console.WriteLine("Group in methodSyntax------");  
  15.             foreach (var item in methodSyntax7)  
  16.             {  
  17.                 Console.WriteLine(item.Key + ":" + item.Count());  
  18.             }  
  19.   
  20.             Console.WriteLine('\n');  

LINQ For Beginners

We need to use the above query to filter by group. Notice how in the 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.

LINQ For Beginners 

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.

  1. //FIRST  
  2. var querySyntax8 = (from employee in employees  
  3.                     //where employee.EmployeeName.StartsWith("Q")  
  4.                     select employee).First();  
  5.   
  6. var methodSyntax8 = employees  
  7.                     //.Where(e => e.EmployeeName.StartsWith("Q"))                   
  8.                     .First();  
  9.   
  10. Console.WriteLine("First in querySyntax------");  
  11. if (querySyntax8 != null)  
  12. {  
  13.     Console.WriteLine(querySyntax8.EmployeeName);  
  14. }  
  15.   
  16. Console.WriteLine("First in methodSyntax------");  
  17. if (methodSyntax8 != null)  
  18. {  
  19.     Console.WriteLine(methodSyntax8.EmployeeName);  
  20. }  
  21.   
  22. Console.WriteLine('\n');  

LINQ For Beginners

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:

LINQ For Beginners 

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.

  1. //FIRST OR DEFAULT  
  2.          var querySyntax9 = (from employee in employees  
  3.                              //where employee.EmployeeName.StartsWith("Q")  
  4.                              select employee).FirstOrDefault();  
  5.   
  6.          var methodSyntax9 = employees  
  7.                             //.Where(e => e.EmployeeName.StartsWith("Q"))  
  8.                              .FirstOrDefault();  
  9.   
  10.          Console.WriteLine("First or default in querySyntax------");  
  11.          if (querySyntax9 != null)  
  12.          {  
  13.              Console.WriteLine(querySyntax9.EmployeeName);  
  14.          }  
  15.   
  16.          Console.WriteLine("First or default in methodSyntax------");  
  17.          if (methodSyntax9 != null)  
  18.          {  
  19.              Console.WriteLine(methodSyntax9.EmployeeName);  
  20.          }  
  21.   
  22.          Console.WriteLine('\n');  

LINQ For Beginners

Result is as below:

LINQ For Beginners 

JOIN

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

  1. //JOIN  
  2.           var querySyntax10 = from employee in employees  
  3.                             join project in projects on employee.ProjectId equals project.ProjectId  
  4.                             select new { employee.EmployeeName, project.ProjectName };  
  5.   
  6.           var methodSyntax10 = employees.Join(projects,  
  7.                                             e => e.ProjectId,  
  8.                                             p => p.ProjectId,  
  9.                                             (e, p) => new { e.EmployeeName, p.ProjectName });  
  10.   
  11.           Console.WriteLine("Join in querySyntax------");  
  12.           foreach (var item in querySyntax10)  
  13.           {  
  14.               Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
  15.           }  
  16.           Console.WriteLine("Join in methodSyntax------");  
  17.           foreach (var item in methodSyntax10)  
  18.           {  
  19.               Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
  20.           }  
  21.   
  22.           Console.WriteLine('\n');  

LINQ For Beginners

Employees and their projects will be displayed as below:

LINQ For Beginners 

LEFT JOIN

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

LINQ For Beginners 

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.

  1. //LEFT JOIN  
  2. var querySyntax11 = from employee in employees  
  3.                   join project in projects on employee.ProjectId equals project.ProjectId into group1  
  4.                   from project in group1.DefaultIfEmpty()  
  5.                   select new { employee.EmployeeName, ProjectName = project?.ProjectName ?? "NULL" };             
  6.   
  7. Console.WriteLine("Left Join in querySyntax------");  
  8. foreach (var item in querySyntax11)  
  9. {  
  10.     Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
  11. }  
  12.   
  13. Console.WriteLine('\n');  

LINQ For Beginners

The output will be displayed as below:

LINQ For Beginners 

Deferred Execution

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

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

LINQ For Beginners 

The query is actually executed when the foreach statement is executed. Therefore, many 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. 

The code used in this article is available here on GitHub.