Sample LINQ Queries

In this section, you will learn some complex LINQ queries. We will use the following Student and Standard collection for our queries.

Sample Collections:
IList<Student> studentList = new List<Student>() { 
    new Student() { StudentID = 1, StudentName = "John", Age = 18, StandardID = 1 } ,
    new Student() { StudentID = 2, StudentName = "Steve",  Age = 21, StandardID = 1 } ,
    new Student() { StudentID = 3, StudentName = "Bill",  Age = 18, StandardID = 2 } ,
    new Student() { StudentID = 4, StudentName = "Ram" , Age = 20, StandardID = 2 } ,
    new Student() { StudentID = 5, StudentName = "Ron" , Age = 21 } 
};

IList<Standard> standardList = new List<Standard>() { 
    new Standard(){ StandardID = 1, StandardName="Standard 1"},
    new Standard(){ StandardID = 2, StandardName="Standard 2"},
    new Standard(){ StandardID = 3, StandardName="Standard 3"}
};

Multiple Select and where operator

Example: Multiple Select and where Operator
var studentNames = studentList.Where(s => s.Age > 18)
                              .Select(s => s)
                              .Where(st => st.StandardID > 0)
                              .Select(s => s.StudentName);
Output:
Steve
Ram

The following query returns Enumerable of anonymous object that has only StudentName property:

Example: LINQ Query returns Collection of Anonymous Objects
var teenStudentsName = from s in studentList
                       where s.age > 12 && s.age < 20
                       select new { StudentName = s.StudentName };

teenStudentsName.ToList().ForEach(s => Console.WriteLine(s.StudentName));
Output:
John
Bill

Group By

The following query returns list students group by StandardID:

Example: LINQ GroupBy Query - C#
var studentsGroupByStandard = from s in studentList
                              group s by s.StandardID into sg
                              orderby sg.Key 
                                    select new { sg.Key, sg };


foreach (var group in studentsGroupByStandard)
{
    Console.WriteLine("StandardID {0}:", group.Key);
    
    group.sg.ToList().ForEach(st => Console.WriteLine(st.StudentName ));
}
Output:
StandardID 0:
Ron
StandardID 1:
John
Steve
StandardID 2:
Bill
Ram

The output includes Ron who doesn't have any StandardID. So Ron falls under StandardID 0.

To remove a student who doesn't have a StandardID, use a where operator before the group operator:

Example: LINQ GroupBy Query - C#
var studentsGroupByStandard = from s in studentList
                              where s.StandardID > 0
                              group s by s.StandardID into sg
                              orderby sg.Key 
                                    select new { sg.Key, sg };
Output:
StandardID 1:
John
Steve
StandardID 2:
Bill
Ram
ADVERTISEMENT

Left outer join

Use left outer join to display students under each standard. Display the standard name even if there is no student assigned to that standard.

Example: LINQ Left Outer Join - C#
var studentsGroup = from stad in standardList
                    join s in studentList
                    on stad.StandardID equals s.StandardID
                        into sg
                        select new { 
                                        StandardName = stad.StandardName, 
                                        Students = sg 
                                    };

foreach (var group in studentsGroup)
{
    Console.WriteLine(group.StandardName);
    
    group.Students.ToList().ForEach(st => Console.WriteLine(st.StudentName));
}
Output:
Standard 1:
John
Steve
Standard 2:
Bill
Ram
Standard 3:

In the following example of group by query, we sort the group and select only StudentName:

Example: LINQ Left Outer Join - C#
var studentsWithStandard = from stad in standardList
                           join s in studentList
                           on stad.StandardID equals s.StandardID
                           into sg
                               from std_grp in sg 
                               orderby stad.StandardName, std_grp.StudentName 
                               select new { 
                                                StudentName = std_grp.StudentName, 
                                                StandardName = stad.StandardName 
                                };


foreach (var group in studentsWithStandard)
{
    Console.WriteLine("{0} is in {1}", group.StudentName, group.StandardName);
}
Output:
John is in Standard 1
Steve is in Standard 1
Bill is in Standard 2
Ram is in Standard 2

Sorting

The following query returns list of students by ascending order of StandardID and Age.

Example: Sorting
var sortedStudents = from s in studentList
                        orderby s.StandardID, s.age
                        select new { 
                                StudentName = s.StudentName, 
                                Age = s.age, 
                                StandardID = s.StandardID };

sortedStudents.ToList().ForEach(s => Console.WriteLine("Student Name: {0}, Age: {1}, StandardID: {2}", s.StudentName, s.Age , s.StandardID));
Output:
Student Name: Ron, Age: 21, StandardID: 0
Student Name: John, Age: 18, StandardID: 1
Student Name: Steve, Age: 21, StandardID: 1
Student Name: Bill, Age: 18, StandardID: 2
Student Name: Ram, Age: 20, StandardID: 2

Inner Join

Example: LINQ Inner join - C#
var studentWithStandard = from s in studentList
                          join stad in standardList
                          on s.StandardID equals stad.StandardID 
                          select new { 
                                  StudentName = s.StudentName, 
                                  StandardName = stad.StandardName 
                              };

studentWithStandard.ToList().ForEach(s => Console.WriteLine("{0} is in {1}", s.StudentName, s.StandardName  ));
Output:
John is in Standard 1
Steve is in Standard 1
Bill is in Standard 2
Ram is in Standard 2

Nested Query

C#:
var nestedQueries = from s in studentList
                    where s.age > 18 && s.StandardID == 
                        (from std in standardList
                        where std.StandardName == "Standard 1"
                        select std.StandardID).FirstOrDefault()
                            select s;

nestedQueries.ToList().ForEach(s => Console.WriteLine(s.StudentName));
Output:
Steve
Want to check how much you know LINQ?