How to write LINQ (Language Integrated Query) queries with example in c#?

This blog will be covering some of the most important LINQ functions & queries that are being used in our day-to-day development of applications.

Aggregate Function

An aggregate function is a function where the values of multiple collections are grouped together as input on certain criteria to form a single value of greater significance or measurement such as a set.

Common Aggregate Functions are:

1.SUM() :- This function Returns the sum of column values.

For Example:-

 EmployeeDataContext employee = new EmployeeDataContext();

/*Total Salaries for all employee */

 var empSalarySum = (from emp in employee.EMPLOYEES

                         select emp.SALARY).Sum();

2.AVERAGE() :-This function Returns the average of column values.

For Example:-

EmployeeDataContext employee = new EmployeeDataContext ();

/*Averge of whole salary in Employee table*/

   var empSalaryAvg = (from emp in employee.EMPLOYEES

                         select emp.SALARY).Average();

 

3.COUNT() :-This function Returns the total number of rows in a table.

For Example:-

EmployeeDataContext employee = new EmployeeDataContext ();

var empCount = (from emp in employee.EMPLOYEES

                         select emp.Id).Count();

4.MAX() :-This function Returns the maximum value in the column.

For Example:-

EmployeeDataContext employee = new EmployeeDataContext ();

var empSalaryMax = (from emp in employee.EMPLOYEES

                         select emp.SALARY).Max();

5.MIN() :-This function Returns the minimum value in the column.

For Example:-

EmployeeDataContext employee = new EmployeeDataContext ();

var empSalaryMin = (from emp in employee.EMPLOYEES

                         select emp.SALARY).Min();

Projection Operators 

There are two important operators that belong to Projection Operator which are as follows:-

1) Select

2) SelectMany

These Projection Operators (Select & SelectMany) are used to transform the results into a query.

Select clause:- In SQL Select clause allows us to specify what or which columns we want to retrieve like this   Language integrated query(LINQ) using SELECT  operator we can specify the properties that we want to retrieve and It also allows us to perform calculations on Data.

For Example-1:- Let’s  take an example of student information and fetch information by Select operator and see how Select operator works.

For example, see below code:-

var result = Student.GetAllStudents().Select(stu => new

  {
                                FirstName = stu.FirstName,
                               Gender = stu.Gender
    });
 foreach (var v in result)
 {
   Console.WriteLine(v.FirstName + " - " + v.Gender);
 }

OUTPUT

Amit      : Male

Deepak : Male

Neeraj  : Male

Seema  : FeMale

For Example-2:-In This example we see how to work  SelectMany operator.

Here the list of students and all subject strings of a given a student to an IEnumerable<string>. In this we have four students, there will be four IEnumerable<string> sequences, which are then flattened to form a single sequence i.e a single IEnumerable<string> sequence.

For example, see below code:-

IEnumerable<string> allSubjects = Student.GetAllStudetns().SelectMany(s => s.Subjects);

foreach (string subject in allSubjects)

{

    Console.WriteLine(subject);

}

Ordering Operators in LINQ

In this topic we will learn What is Ordering Operators , Type of Ordering Operator and How to work Ordering Operators.

The following 5 important LINQ query operators that belong to Ordering Operators category:-

1.OrderBy
2.OrderByDescending
3.ThenBy
4.ThenByDescending
5.Reverse

Now let’s understand this by above-mentioned Ordering operators with an example.

OrderBy:-OrderBy  operator are used to sort Data in ascending or descending order.

For Example:- Let’s take an example of students and We will use the following Student class in this Example.

public class Student

{

    public int StuID { get; set; }

    public string StudentName { get; set; }

    public int TotalMarksofStudents { get; set; }

    public static List<Student> GetAllStudentsInfo()

    {

        List<Student> listOfStudents = new List<Student>

        {

            new Student

            {

                StuID= 101,

                StudentName = "Tom",

                TotalMarksofStudents = 800

            },

            new Student

            {

                StuID= 102,

                StudentName = "Mary",

                TotalMarksofStudents = 900

            },

            new Student

            {

                StuID= 103,

                StudentName = "Valarie",

                TotalMarksofStudents = 800

            },

            new Student

            {

                StuID= 104,

                StudentName = "John",

                TotalMarksofStudents = 800

            },

        };

        return listofStudents;

    }

}

Sort Students by Name in ascending order:-

for example see below code:-

IEnumerable<Student> resultofStudent = Student.GetAllStudentsInfoo().OrderBy(s => s.StudentName);

foreach (Student student in resultofStudent)

{

Console.WriteLine(student.StudentName);

}

Output

Let’s Sort Students by Name in descending order

For example, see below code:-

IEnumerable<Student> resultofStudent = Student.GetAllStudentsinfo().OrderByDescending(s => s.StudentName);

foreach (Student student in resultofStudent)

{

Console.WriteLine(student.StudentName);

}

Output

ThenBy:-ThenBy  operator are used to sort Data.

Example:- Lets take a example of students and We will use the above Student class in this Example.

OrderBy and OrderByDescending it is work fine when we want to sort a Data just by one value or expression.

If we want to sort by more than one Data or collection, that’s when we use ThenBy or ThenByDescending along with OrderBy or OrderByDescending.

OrderBy or OrderByDescending is widely  used for the primary sort and ThenBy or ThenByDescending is used for performing secondary sort in LINQ query as well as We can use Secondary Sort operators (ThenBy or ThenByDescending )  more than once in the same LINQ query.

For Example :- 

a) Sorts Students first by TotalMarksofStudent in ascending order(using Primary Sort)
b) The four Students with TotalMarksofStudent of 800,  then we will  sort students by its Name in ascending order (using First Secondary Sort)
c) The Two Students with Name of John, then we will  sort students  by StuID in ascending order (using Second Secondary Sort)

for example see below code:-

IEnumerable<Student> resultofStudent = Student.GetAllStudetnsinfo()

.OrderBy(s => s.TotalMarksofStudents).ThenBy(s => s.StudentName).ThenBy(s => s.StuID);

foreach (Student student in resultofStudent)

{

Console.WriteLine(student.TotalMarksofStudent + "\t" + student.StudentName + "\t" + student.StuID);

}

 

Output

Reverse:-Reverse operator are used to simply reverses the items in a given collection.

For Example:- Lets take a example of students and We will use the above Student class in this Example.

For example see below code:-

IEnumerable<Student> studentResult = Student.GetAllStudetnsinfo();

Console.WriteLine("Before calling Reverse");

foreach (Student s in studentResult)

{

Console.WriteLine(s.StuID + "\t" + s.StudentName + "\t" + s.TotalMarksofStudent);

}

Console.WriteLine();

IEnumerable<Student> studentResult = studentResult.Reverse();

Console.WriteLine("After calling Reverse");

foreach (Student s in studentResult)

{

Console.WriteLine(s.StuID + "\t" + s.StudentName + "\t" + s.TotalMarksofStudent);

}

Output

Partitioning Operators in LINQ

In this topic we will learn What is Partitioning Operators in LINQ ,Partitioning Operators and How to work Partitioning Operators.

The following four important LINQ query operators That is belong to Partitioning Operators:-
1.Take
2.Skip
3.TakeWhile
4.SkipWhile

Now lets understand one by one all above mentioned Partitioning Operators with example.

Take:-Take method is used to returns a specified number of elements given by he/she from the starting of the collection. The number of items  to return is specified using the count parameter this method expects.

Lets understand it by a example.

For Example:-

In this example we have a array of countries and we will take only the first 3 countries of the array by using Take operator.

For example see below code:-

string[]  listCountries = { "Australia", "Canada", "Germany", "US", "India", "UK", "Italy" };

IEnumerable<string> conResult = listCountries.Take(3);

foreach (string country in conResult)
 {
 Console.WriteLine(country);
 }

Output

Skip:-This method skips a specified number of elements in a collection and after that returns the remaining elements and The number of items that will skip is specified by the count parameter which is given by us to method expects.

For Example:-

In this example we have a array of countries and we will skips only the first 3 countries of the array by using skip operator.

For example see below code:-

string[] listCountries = { "Australia", "Canada", "Germany", "US", "India", "UK", "Italy" };

IEnumerable<string> countryResult = listCountries.Skip(3);

foreach (string country in countryResult)
 {
 Console.WriteLine(country);
 }

Output

TakeWhile:-This method returns a specified number of elements in a collection as long as the specified condition will true.

For Example:-

In this example we have a array of countries and we will get specific number of elements by using TakeWhile operator.

For example see below code:-

string[] listCountries = { "Australia", "Canada", "Germany", "US", "India", "UK", "Italy" };

IEnumerable<string> countryResult = listCountries.TakeWhile(s => s.Length > 2);

foreach (string country in countryResult)
 {
 Console.WriteLine(country);
 }

Output

Group-By Operator

GroupBy operator is work like group by operator in SQL that means it is use for get group of collection according to user condition like groupby name,id etc.

In other words GroupBy operator  creates and returns a sequence of IGrouping<K,V>.

For Example:-Lets take a example of EmployeeInfo class for understand how to GroupBy operator work.

public class EmployeeInfo 
 {
     public int EmpID { get; set; }

    public string EmpEmpName { get; set; }

    public string EmpGender { get; set; }

    public string EmpEmpDepartment { get; set; }

    public int EmpEmpSalary { get; set; }

public static List<EmployeeInfo > GetAllEmployeesInfo()
     {
         return new List<EmployeeInfo >()
         {
             new EmployeeInfo  { EmpID = 1, EmpName = "Mark", EmpGender = "Male",
                                          EmpDepartment = "IT", EmpSalary = 45000 },

            new EmployeeInfo  { EmpID = 2, EmpName = "Steve", EmpGender = "Male",
                                          EmpDepartment = "HR", EmpSalary = 55000 },

            new EmployeeInfo  { EmpID = 3, EmpName = "Ben", EmpGender = "Male",
                                          EmpDepartment = "IT", EmpSalary = 65000 },

            new EmployeeInfo  { EmpID = 4, EmpName = "Philip", EmpGender = "Male",
                                          EmpDepartment = "IT", EmpSalary = 55000 },

            new EmployeeInfo  { EmpID = 5, EmpName = "Mary", EmpGender = "Female",
                                          EmpDepartment = "HR", EmpSalary = 48000 },

            new EmployeeInfo  { EmpID = 6, EmpName = "Valarie", EmpGender = "Female",
                                          EmpDepartment = "HR", EmpSalary = 70000 },



            new EmployeeInfo  { EmpID = 7, EmpName = "John", EmpGender = "Male",
                                          EmpDepartment = "IT", EmpSalary = 64000 },

            new EmployeeInfo  { EmpID = 8, EmpName = "Pam", EmpGender = "Female",
                                          EmpDepartment = "IT", EmpSalary = 54000 },

            new EmployeeInfo  { EmpID = 9, EmpName = "Stacey", EmpGender = "Female",
                                          EmpDepartment = "HR", EmpSalary = 84000 },

            new EmployeeInfo  { EmpID = 10, EmpName = "Andy", EmpGender = "Male",
                                          EmpDepartment = "IT", EmpSalary = 36000 }

        };
     }
 }

1)Get Employee Count By EmpDepartment wize.

for example see below code:-

var empGroup = from employee in EmployeeInfo.GetAllEmployeesInfo()
 group employee by employee.EmpDepartment;

foreach (var group in empGroup)
 {
 Console.WriteLine("{0} - {1}", group.Key, group.Count());
 }

Output

2)Get all Employee Count By EmpDepartment and we can  also get each employee and department name.

var empGroup = from employee in Employee.GetAllEmployeesInfo()
 group employee by employee.EmpDepartment;

For example see the below code:-
foreach (var group in empGroup)
 {
 Console.WriteLine("{0} - {1}", group.Key, group.Count());
 Console.WriteLine("----------");
 foreach (var employee in group)
 {
 Console.WriteLine(employee.EmpName + "\t" + employee.EmpDepartment);
 }
 Console.WriteLine(); Console.WriteLine();
 }

Output

JOINS

Joins is the most important part of Language integrated query(LINQ) so lets discuss about joins and see how it works with the help of example. For now lets first look into its definition that is as follow:-

Join:-Basically Join  widely  useful for associating elements from different collections that have no direct relationship in the object class or model.

The only purpose is that the elements in each collection share some value that can be compared for equal values.

There are several types of joins. Lets understand one by one.

1.INNER JOIN :-Basically Inner join is used to take only matching elements from two or more collections and non-matching elements are not come in result set.

Let’s understand Inner Join with an example  lets take  the following DepartmentInfo and EmployeeInfo classes and focus that,
Employee Andy is not assigned to any department and when we do An inner join it will not include his record in the result set.

public class DepartmentInfo
 {
     public int DeptID { get; set; }
     public string Name { get; set; }

    public static List<DepartmentInfo> GetAllDepartmentsInfo()
     {
         return new List<DepartmentInfo>()
         {
             new DepartmentInfo { DeptID = 1, Name = "IT"},

            new DepartmentInfo { DeptID = 2, Name = "HR"},

            new DepartmentInfo { DeptID = 3, Name = "Payroll"},
         };
     }
 }

public class EmployeeInfo
 {
     public int EmpID { get; set; }

    public string EmpFName { get; set; }

    public int DepartmentID { get; set; }

    public static List<EmployeeInfo> GetAllEmployeesInfo()
     {
         return new List<EmployeeInfo>()
         {
             new EmployeeInfo { EmpID = 1, EmpFName= "Mark", DepartmentID = 1 },

            new EmployeeInfo { EmpID = 2, EmpFName= "Steve", DepartmentID = 2 },

            new EmployeeInfo { EmpID = 3, EmpFName= "Ben", DepartmentID = 1 },

            new EmployeeInfo { EmpID = 4, EmpFName= "Philip", DepartmentID = 1 },

            new EmployeeInfo { EmpID = 5, EmpFName= "Mary", DepartmentID = 2 },

            new EmployeeInfo { EmpID = 6, EmpFName= "Valarie", DepartmentID = 2 },

            new EmployeeInfo { EmpID = 7, EmpFName = "John", DepartmentID = 1 },

            new EmployeeInfo { EmpID = 8, EmpFName= "Pam", DepartmentID = 1 },

            new EmployeeInfo { EmpID = 9, EmpFName = "Stacey", DepartmentID = 2 },

            new EmployeeInfo { EmpID = 10, EmpFName= "Andy"}

        };
     }
 }
For Example:- Basically we are Join the EmployeesInfo and DepartmentInfo collections and print all the Employees and their corresponding department names.
 var resultSet = EmployeeInfo.GetAllEmployeesInfo().Join(DepartmentInfo.GetAllDepartmentsInfo(),
 e => e.DepartmentID,
 d => d.DeptID, (employee, department) => new
 {
 EmployeeName = employee.EmpFName,
 DepartmentName = department.Name
 });
 foreach (var employee in resultSet)
 {
 Console.WriteLine(employee.EmployeeName + "\t" + employee.DepartmentName);
 }

Output:- See that, in the resultSet we don’t have Andy record  and  This is happaning  because, Andy does not have a matching department in Department collection.
So this is effectively an inner join.

 

2.Left Outer Jon:- Basically LEFT OUTER JOIN is widely used to take the matching elements plus all the non-matching elements from the left collection which are included in the result set.

For Example:- If you want to understand this in detail so let’s do Left Outer Join in  EmployeesInfo and DepartmentInfo collections and print all the Employees and their corresponding department names.

public class DepartmentInfo
 {
     public int DeptID { get; set; }
     public string Name { get; set; }

    public static List<DepartmentInfo> GetAllDepartmentsInfo()
     {
         return new List<DepartmentInfo>()
         {
             new DepartmentInfo { DeptID = 1, Name = "IT"},
             new DepartmentInfo { DeptID = 2, Name = "HR"},
             new DepartmentInfo { DeptID = 3, Name = "Payroll"},
         };
     }
 }

public class EmployeeInfo
 {
     public int EmpID { get; set; }
     public string EmpFName{ get; set; }
     public int DepartmentID { get; set; }

    public static List<EmployeeInfo> GetAllEmployeesInfo()
     {
         return new List<EmployeeInfo>()
         {
             new EmployeeInfo { EmpID = 1, EmpFName= "Mark", DepartmentID = 1 },
             new EmployeeInfo { EmpID = 2, EmpFName= "Steve", DepartmentID = 2 },
             new EmployeeInfo { EmpID = 3, EmpFName= "Ben", DepartmentID = 1 },
             new EmployeeInfo { EmpID = 4, EmpFName= "Philip", DepartmentID = 1 },
             new EmployeeInfo { EmpID = 5, EmpFName= "Mary", DepartmentID = 2 },
             new EmployeeInfo { EmpID = 6, EmpFName= "Valarie", DepartmentID = 2 },
             new EmployeeInfo { EmpID = 7, EmpFName= "John", DepartmentID = 1 },
             new EmployeeInfo { EmpID = 8, EmpFName= "Pam", DepartmentID = 1 },
             new EmployeeInfo { EmpID = 9, EmpFName= "Stacey", DepartmentID = 2 },
             new EmployeeInfo { EmpID = 10, EmpFName = "Andy"}
         };
     }
 }

For example, see below code:-

var resultSet = from e in EmployeeInfo.GetAllEmployeesInfo()
 join d in DepartmentInfo.GetAllDepartmentsInfo()
 on e.DepartmentID equals d.DeptID into eGroup
 from d in eGroup.DefaultIfEmpty()
 select new
 {
 EmployeeName = e.EmpFName,
 DepartmentName = d == null ? "No Department" : d.Name
 };

foreach (var v in resultSet)
 {
 Console.WriteLine(v.EmployeeName + "\t" + v.DepartmentName);
 }

Output

As we all know that every language has a wide scope but I have tried to cover few of the important topics of LINQ in this blog. For further queries, feel free to place your valuable comments in the comments section below!

1 thought on “How to write LINQ (Language Integrated Query) queries with example in c#?”

Leave a Comment