LINQ (Language Integrated Query) - PART - 1

Topics Included:

#Min   #Max  #Sum  #Count  #Average  #WHERE  #INDEX  #And  #Or  #SelectMany  #Distinct
#OrderBy  #ThenBy  #Reverse  #Take  #Skip  #TakeWhile  #SkipWhile  #Deferred  #Immediate
#ToDictionary  #ToLookUp  #Cast  #OfType 

---

• LINQ enables us to query any type of data store (SQL Server, XML documents, Objects in memory etc). 
• LINQ enables us to work with these different data sources using a similar coding style without having the need to know the syntax specific to the data source.
• LINQ provides intellisense and compile time error checking.

There are 2 ways to write LINQ queries-
1) Using SQL like query expressions

select
from
where
orderby
join
groupby

The Standard Query Operators are implemented as extension methods on IEnumerable<T> interface.

E.G. 1)
IEnumerable<Student> students = from student in Student.GetAllStudents()
                                                           where student.Gender == "Male"
                                                           select student;

E.G. 2)
IEnumerable<int> evenNumbers = from num in numbers
                                                           where num % 2 == 0
                                                           select num;


2) Using Lambda Expressions.

E.G. 1)
IEnumerable<Student> students = Student.GetAllStudents()
    .Where(student => student.Gender == "Male");

E.G. 2)
List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
            IEnumerable<int> evenNumbers = numbers.Where(num => num % 2 == 0);

Aggregate Function

int[] Numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

#Min()

int smallestNumber = Numbers.Min();
int smallestEvenNumber = Numbers.Where(n => n % 2 == 0).Min();

#Max()

int largestNumber = Numbers.Max();
int largestEvenNumber = Numbers.Where(n => n % 2 == 0).Max();

#Sum()

int sumOfAllNumbers = Numbers.Sum();
int sumOfAllEvenNumbers = Numbers.Where(n => n % 2 == 0).Sum();

#Count()

int countOfAllNumbers = Numbers.Count();
int countOfAllEvenNumbers = Numbers.Where(n => n % 2 == 0).Count();

#Average()

double averageOfAllNumbers = Numbers.Average();
double averageOfAllEvenNumbers = Numbers.Where(n => n % 2 == 0).Average();

E.G. 1)
string[] countries = { "India", "USA", "UK" };

int minCount = countries.Min(x => x.Length);
int maxCount = countries.Max(x => x.Length);

E.G. 2)
string[] countries = { "India", "US", "UK", "Canada", "Australia" };

string result = countries.Aggregate((a, b) => a + ", " + b);

E.G. 3)
int[] Numbers = { 2, 3, 4, 5 };

int result = Numbers.Aggregate((a, b) => a * b);

---

#WHERE

IEnumerable<int> evenNumbers = numbers.Where(num => num % 2 == 0);

Can be rewritten as shown below

Func<int, bool> predicate = i => i % 2 == 0;
IEnumerable<int> evenNumbers = numbers.Where(predicate);

#INDEX Value

List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

E.G. - Lambda Expression
IEnumerable<int> evenNumberIndexPositions = numbers
    .Select((num, index) => new { Number = num, Index = index })
    .Where(x => x.Number % 2 == 0)
    .Select(x => x.Index);

#AND

IEnumerable<Department> departments = context.Departments
                .Where(dept => dept.Name == "IT" && dept.Name == "HR");

#OR

IEnumerable<Department> departments = context.Departments
                .Where(dept => dept.Name == "IT" || dept.Name == "HR");

#SelectMany

E.G. - Lambda Expression
IEnumerable<string> allSubjects = Student.GetAllStudetns().SelectMany(s => s.Subjects);

E.G. - SQL Like Query
IEnumerable<string> allSubjects = from student in Student.GetAllStudetns()
                                from subject in student.Subjects
                                select subject;

E.G.
string[] stringArray =
{
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
    "0123456789"
};

E.G - Lambda Expression
IEnumerable<char> result = stringArray.SelectMany(s => s);

E.G - SQL Like Query
IEnumerable<char> result = from s in stringArray
                           from c in s
                           select c;

#Distinct

E.G - Lambda Expression
IEnumerable<string> allSubjects = Student.GetAllStudetns().SelectMany(s => s.Subjects).Distinct();                                                                                                         

E.G - SQL Like Query
IEnumerable<string> allSubjects = (from student in Student.GetAllStudetns()
                                                             from subject in student.Subjects
                                                             select subject).Distinct();


Difference between Select and SelectMany

Select and select Many can fetch the same data but in selectMany we can return a IEnumerable string where as select we can return list of IEnmerable list of string so too many loops can be used (at least two). By using SelectMany we can reduce nested loop

#OrderBy

E.G - Lambda Expression
IEnumerable<Student> result = from student in Student.GetAllStudents()
                                                      orderby student.Name
                                                      select student;
E.G - Lambda Expression
IEnumerable<Student> result = Student.GetAllStudents().OrderByDescending(s => s.Name);

E.G - SQL Like Query
IEnumerable<Student> result = from student in Student.GetAllStudents()
                                                      orderby student.Name descending
                                                      select student;

#ThenBy

OrderBy or OrderByDescending performs the primary sort. #ThenBy or

#ThenByDescending

is used for adding secondary sort. Secondary Sort operators (ThenBy or ThenByDescending ) can be used more than once in the same LINQ query.

E.G - Lambda Expression
IEnumerable<Student> result = Student.GetAllStudetns()
    .OrderBy(s => s.TotalMarks).ThenBy(s => s.Name).ThenBy(s => s.StudentID);

E.G - SQL Like Query
IEnumerable<Student> result = from student in Student.GetAllStudetns()
                                                      orderby student.TotalMarks, student.Name, student.StudentID
                                                      select student;

#Reverse

IEnumerable<Student> result = students.Reverse();


#Take, Skip, TakeWhile & SkipWhile

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

#Take

Take method returns a specified number of elements from the start of the collection. The number of items to return is specified using the count parameter this method expects.

IEnumerable<string> result = countries.Take(3);

IEnumerable<string> result = (from country in countries select country).Take(3);

#Skip

Skip method skips a specified number of elements in a collection and then returns the remaining elements. The number of items to skip is specified using the count parameter this method expects.

Note: For the same argument value, the Skip method returns all of the items that the Take method would not return.

IEnumerable<string> result = countries.Skip(3);

#TakeWhile

TakeWhile method returns elements from a collection as long as the given condition specified by the predicate is true.

IEnumerable<string> result = countries.TakeWhile(s => s.Length > 2);

#SkipWhile

SkipWhile method skips elements in a collection as long as the given condition specified by the predicate is true, and then returns the remaining elements.

IEnumerable<string> result = countries.SkipWhile(s => s.Length > 2);


Paging using Skip and Take

int PageSize = 3
IEnumerable<student> result = students.Skip((pageNumber - 1) * PageSize).Take(PageSize);

---

Query Type

#Deferred & Immediate

LINQ operators can be broadly classified into 2 categories based on the behaviour of query execution
1. #Deferred or Lazy Operators -  These query operators use deferred execution.
Examples - select, where, Take, Skip etc
2. #Immediate or Greedy Operators - These query operators use immediate execution.
Examples - count, average, min, max, ToList etc

List<Student> listStudents = new List<Student>
{
    new Student { StudentID= 101, Name = "Tom", TotalMarks = 800 },
    new Student { StudentID= 102, Name = "Mary", TotalMarks = 900 },
    new Student { StudentID= 103, Name = "Pam", TotalMarks = 800 }
};

// LINQ Query is only defined here and is not executed at this point
// If the query is executed at this point, the result should not display Tim
IEnumerable<Student> result = from student in listStudents
                              where student.TotalMarks == 800
                              select student;

// Add a new student object with TotalMarks = 800 to the source
listStudents.Add(new Student { StudentID = 104, Name = "Tim", TotalMarks = 800 });

// The above query is actually executed when we iterate thru the sequence
// using the foreach loop. This is proved as Tim is also included in the result
foreach (Student s in result)
{
    Console.WriteLine(s.StudentID + "\t" + s.Name + "\t" + s.TotalMarks);
}

Immediate

// Since we are using ToList() which is a greedy operator
// the LINQ Query is executed immediately at this point
            IEnumerable<Student> result = (from student in listStudents
                                           where student.TotalMarks == 800
                                           select student).ToList();

// Since we are using Count() operator, the LINQ Query is executed at this point
            int result = (from student in listStudents
                          where student.TotalMarks == 800

                          select student).Count();

---

#ToDictionary

Please Note: Keys in the dictionary must be unique. If two identical keys are created by the keySelector function, the following System.ArgumentException will be thrown at runtime.
Unhandled Exception: System.ArgumentException: An item with the same key has already been added.

List<Student> listStudents = new List<Student>
{
    new Student { StudentID= 101, Name = "Tom", TotalMarks = 800 },
    new Student { StudentID= 102, Name = "Mary", TotalMarks = 900 },
    new Student { StudentID= 103, Name = "Pam", TotalMarks = 800 }
};

Dictionary<int, Student> result = listStudents.ToDictionary(x => x.StudentID);

foreach (KeyValuePair<int, Student> kvp in result)
{
    Console.WriteLine(kvp.Key + "\t" + kvp.Value.Name + "\t" + kvp.Value.TotalMarks);
}

#ToLookUp

ToLookup creates a Lookup. Just like a dictionary, a Lookup is a collection of key/value pairs. A dictionary cannot contain keys with identical values, where as a Lookup can.

var employeesByJobTitle = listEmployees.ToLookup(x => x.JobTitle);

Console.WriteLine("Employees Grouped By JobTitle");
foreach (var kvp in employeesByJobTitle)
{
    Console.WriteLine(kvp.Key);
    // Lookup employees by JobTitle
    foreach (var item in employeesByJobTitle[kvp.Key])
    {
        Console.WriteLine("\t" + item.Name + "\t" + item.JobTitle + "\t" + item.City);
    }
}

---

Conversion Operator

#Cast

Cast operator attempts to convert all of the items within an existing collection to another type and return them in a new collection. If an item fails conversion an exception will be thrown. This method uses deferred execution.

ArrayList list = new ArrayList();
list.Add(1);
list.Add(2);
list.Add(3);

// The following item causes an exception
// list.Add("ABC");

IEnumerable<int> result = list.Cast<int>();

#OfType

OfType operator will return only elements of the specified type. The other type elements are simply ignored and excluded from the result set.


ArrayList list = new ArrayList();
list.Add(1);
list.Add(2);
list.Add(3);
list.Add("4");
list.Add("ABC");

IEnumerable<int> result = list.OfType<int>();

Difference Between Cast And OfType Operators

OfType operator returns only the elements of the specified type and the rest of the items in the collection will be ignored and excluded from the result.

Cast operator will try to cast all the elements in the collection into the specified type. If some of the items fail conversion, InvalidCastException will be thrown.

When to use Cast over OfType and vice versa?
We would generally use Cast when the following 2 conditions are met
1. We want to cast all the items in the collection &
2. We know for sure the collection contains only elements of the specified type

If we want to filter the elements and return only the ones of the specified type, then we would use OfType.