LINQ For Beginners

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.

class Employee    
{    
    public int EmployeeId { get; set; }    
    public string EmployeeName { get; set; }    
    public int ProjectId { get; set; }    
}

class Project  
{  
    public int ProjectId { get; set; }  
    public string ProjectName { get; set; }  
}

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

public static List<Employee> employees = new List<Employee>();  
public static List<Project> projects = new List<Project>();  
  
static void Main(string[] args)  
{  
    InitializeEmployees();  
    InitializeProjects();  
}  
public static void InitializeEmployees()  
{  
    employees.Add(new Employee  
    {  
        EmployeeId = 1,  
        EmployeeName = "Tuba",  
        ProjectId = 100  
    });  

    employees.Add(new Employee  
    {  
        EmployeeId = 2,  
        EmployeeName = "Atul",  
        ProjectId = 101  
    });  

    employees.Add(new Employee  
    {  
        EmployeeId = 3,  
        EmployeeName = "Theran",  
        ProjectId = 101  
    });  
} 

public static void InitializeProjects()    
{    
    projects.Add(new Project    
    {    
        ProjectId = 100,    
        ProjectName = "ABC"    

    });    

    projects.Add(new Project    
    {    
        ProjectId = 101,    
        ProjectName = "PQR"    

    });      
}

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

LINQ 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.

//WHERE  
var querySyntax1 = from employee in employees  
                  where employee.EmployeeName.StartsWith("T")  
                  select employee.EmployeeName;              

Console.WriteLine("Where in querySyntax------");  
foreach (var item in querySyntax1)  
{  
    Console.WriteLine(item);  
}  

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

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

var methodSyntax1 = employees.Where(e => e.EmployeeName.StartsWith("T"));  
Console.WriteLine("Where in methodSyntax-----");  
foreach (var item in methodSyntax1)  
{  
    Console.WriteLine(item.EmployeeName);  
}  

Console.WriteLine('\n');  

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

LINQ ORDER

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

//ORDER BY ASCENDING  
var querySyntax2 = from employee in employees  
                   orderby employee.EmployeeName  
                   select employee.EmployeeName;  
  
  
var methodSyntax2 = employees.OrderBy(e => e.EmployeeName);  
  
Console.WriteLine("Order by ascending in querySyntax------");  
foreach (var item in querySyntax2)  
{  
    Console.WriteLine(item);  
}  
  
Console.WriteLine("Order by ascending in methodSyntax------");  
foreach (var item in methodSyntax2)  
{  
    Console.WriteLine(item.EmployeeName);  
}  
  
Console.WriteLine('\n'); 

The output will be as below.

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

//ORDER BY DESCENDING  
var querySyntax3 = from employee in employees  
                  orderby employee.EmployeeName descending  
                  select employee.EmployeeName;  


var methodSyntax3 = employees.OrderByDescending(e => e.EmployeeName);  

Console.WriteLine("Order by descending in querySyntax------");  
foreach (var item in querySyntax3)  
{  
    Console.WriteLine(item);  
}  

Console.WriteLine("Order by descending in methodSyntax------");  
foreach (var item in methodSyntax3)  
{  
    Console.WriteLine(item.EmployeeName);  
}  

Console.WriteLine('\n');

The output will be as below.

LINQ Then By

Let’s order all the entries by projectid ascending.

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.

//THEN BY          
var querySyntax4 = from employee in employees  
                   orderby employee.ProjectId, employee.EmployeeName descending  
                   select employee;  
  
var methodSyntax4 = employees.OrderBy(e => e.ProjectId).ThenByDescending(e => e.EmployeeName);  
  
Console.WriteLine("Then by in querySyntax------");  
foreach (var item in querySyntax4)  
{  
    Console.WriteLine(item.EmployeeName + ":" + item.ProjectId);  
}  
  
Console.WriteLine("Then by in methodSyntax------");  
foreach (var item in methodSyntax4)  
{  
    Console.WriteLine(item.EmployeeName + ":" + item.ProjectId);  
}  
  
Console.WriteLine('\n');

LINQ 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.

//TAKE  
var querySyntax5 = (from employee in employees  
                   select employee).Take(2);  
  
  
var methodSyntax5 = employees.Take(2);  
  
  
Console.WriteLine("Take in querySyntax------");  
foreach (var item in querySyntax5)  
{  
    Console.WriteLine(item.EmployeeName);  
}  
  
Console.WriteLine("Take in methodSyntax------");  
foreach (var item in methodSyntax5)  
{  
    Console.WriteLine(item.EmployeeName);  
}  
  
Console.WriteLine('\n');

LINQ 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.

//SKIP  
var querySyntax6 = (from employee in employees  
                    select employee).Skip(2);  
  
var methodSyntax6 = employees.Skip(2);  
  
Console.WriteLine("Skip in querySyntax------");  
foreach (var item in querySyntax6)  
{  
    Console.WriteLine(item.EmployeeName);  
}  
  
Console.WriteLine("Skip in methodSyntax------");  
foreach (var item in methodSyntax6)  
{  
    Console.WriteLine(item.EmployeeName);  
}  
  
Console.WriteLine('\n');

The result will be as follows:

LINQ GROUP

Let’s group our data according to project id.

//GROUP  
var querySyntax7 = from employee in employees  
                  group employee by employee.ProjectId;  


var methodSyntax7 = employees.GroupBy(e => e.ProjectId);  

Console.WriteLine("Group in querySyntax------");  
foreach (var item in querySyntax7)  
{  
    Console.WriteLine(item.Key + ":" + item.Count());  
}  

Console.WriteLine("Group in methodSyntax------");  
foreach (var item in methodSyntax7)  
{  
    Console.WriteLine(item.Key + ":" + item.Count());  
}  

Console.WriteLine('\n'); 

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

//FIRST  
var querySyntax8 = (from employee in employees  
                    //where employee.EmployeeName.StartsWith("Q")  
                    select employee).First();  
  
var methodSyntax8 = employees  
                    //.Where(e => e.EmployeeName.StartsWith("Q"))                   
                    .First();  
  
Console.WriteLine("First in querySyntax------");  
if (querySyntax8 != null)  
{  
    Console.WriteLine(querySyntax8.EmployeeName);  
}  
  
Console.WriteLine("First in methodSyntax------");  
if (methodSyntax8 != null)  
{  
    Console.WriteLine(methodSyntax8.EmployeeName);  
}  
  
Console.WriteLine('\n');

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

//FIRST OR DEFAULT  
var querySyntax9 = (from employee in employees  
                    //where employee.EmployeeName.StartsWith("Q")  
                    select employee).FirstOrDefault();  

var methodSyntax9 = employees  
                   //.Where(e => e.EmployeeName.StartsWith("Q"))  
                    .FirstOrDefault();  

Console.WriteLine("First or default in querySyntax------");  
if (querySyntax9 != null)  
{  
    Console.WriteLine(querySyntax9.EmployeeName);  
}  

Console.WriteLine("First or default in methodSyntax------");  
if (methodSyntax9 != null)  
{  
    Console.WriteLine(methodSyntax9.EmployeeName);  
}  

Console.WriteLine('\n');

The result is as below:

LINQ JOIN

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

//JOIN  
var querySyntax10 = from employee in employees  
                  join project in projects on employee.ProjectId equals project.ProjectId  
                  select new { employee.EmployeeName, project.ProjectName };  

var methodSyntax10 = employees.Join(projects,  
                                  e => e.ProjectId,  
                                  p => p.ProjectId,  
                                  (e, p) => new { e.EmployeeName, p.ProjectName });  

Console.WriteLine("Join in querySyntax------");  
foreach (var item in querySyntax10)  
{  
    Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
}  
Console.WriteLine("Join in methodSyntax------");  
foreach (var item in methodSyntax10)  
{  
    Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
}  

Console.WriteLine('\n'); 

Employees and their projects will be displayed as below:

LINQ 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.

//LEFT JOIN  
var querySyntax11 = from employee in employees  
                  join project in projects on employee.ProjectId equals project.ProjectId into group1  
                  from project in group1.DefaultIfEmpty()  
                  select new { employee.EmployeeName, ProjectName = project?.ProjectName ?? "NULL" };             
  
Console.WriteLine("Left Join in querySyntax------");  
foreach (var item in querySyntax11)  
{  
    Console.WriteLine(item.EmployeeName + ":" + item.ProjectName);  
}  
  
Console.WriteLine('\n');

The output will be displayed as below:

LINQ 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 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.

LINQ 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.