LINQ in C#

LINQ stands for Language-Integrated Query. LINQ helps to remove the technical difficulties while using databases with .NET programming languages. It provides a solution for the problem of object-relational mapping, and simplifies the interaction between objects and data sources. LINQ is a structured query and it integrated in C# and VB.NET

System.Linq namespace contains the necessary classes & interfaces for LINQ. If an object implements IEnumerable interface, then you can use this object as a source of data in LINQ. The Enumerable class includes extension methods for classes that implements IEnumerable<T> interface.

The types of LINQ are mentioned below in brief.

  • LINQ to Objects
  • LINQ to XML(XLINQ)
  • LINQ to DataSet
  • LINQ to SQL (DLINQ)
  • LINQ to Entities

Advantages of LINQ

  • We have to write less code compare to other traditional approach.
  • There is no need to learn a new query language for each type of data source or data format.
  • LINQ makes the code more readable for programmers.
  • It provides type checking of objects at compile time.
  • Tables are automatically created into class
  • Columns are automatically created into properties
  • Easy to debug LINQ query with visual studio’s debugger.
  • LINQ queries provide IntelliSense within Visual Studio.

Example

using System;
using System.Linq;
class Program
{
    public static void Main()
    {
        int[] nums = { 10, -6, 3, 0, -4, 25 };
        // Query that selects only positive numbers.
        var result = from n in nums
                      where n > 0
                      select n;
        Console.Write("The positive values in nums: ");
        // Execute the query.
        foreach (int i in result)
        {
            Console.Write(i + " ");
        }            
        Console.ReadLine();
    }
}


Run the application you will get the following output.

The positive values in nums: 10 3 25

Above program will display only positive number. The first important thing is the use of System.Linq namespace. In order to use LINQ, you must include the System.Linq namespace in your application.

The variable “result” is called the query variable. Due to var keyword it is implicitly typed variable.

The given below query retrieves only positive numbers. All queries begin with from.

linq

Here n is range variable and nums works as data source which is an array. Range variable n receive elements from the data source. The next clause in the query is where. It filters the records according to query. There can be more than one where clause in a query.

Since where n > 0 is used, it will give you only positive numbers.
Linq queries end with either a select clause or a group clause.
You will get the result when foreach loop will get executed.

foreach (int i in result)
          {
            Console.Write ( i + " ");
          }


When the loop executes, the rules defined by the query specified by result are executed.
There is another method, which you can use in LINQ called as lambda expression.

You will get the same result if you use the following code

var result = nums.Where(n => n > 0);

Filter values with where clause

where clause is used to filter the data returned by a query. You can use more than one “where” clause.

Example

using System;
using System.Linq;
class Program
{
    public static void Main()
    {
        int[] nums = { 6, -2, 30, -3, 0, -8, 12, 7, 8, 9, 100 };
        // Query for obtains positive values less than 10.
        var result = from n in nums
                      where n > 0
                      where n < 10
                      select n;
        Console.Write("The positive values less than 10: ");
        // Execute the query.
        foreach (int i in result)
        {
            Console.Write(i + " ");
        }
        Console.ReadLine();
    }
}


Run the above program you will get the following output.

The positive values less than 10 : 6 7 8 9

Standard Query Operators

Query operators are extension methods and defined in System.Linq.Enumerable and System.Linq.Queryable classes. Operator provides the important functionalities such as filtering, sorting, grouping, aggregation, concatenation, etc. you have already seen the “where” clause.

Some of the important Standard Query Operators are given in the table. It is not complete list.

TypeStandard Query Operators
FilterWhere
SortingOrderBy, OrderByDescending, Reverse
GroupingGroupBy
JoinGroupJoin, Join
ProjectionSelect, SelectMany
QuantifiersAll, Any, Contains
SetExcept, Intersect, Union , Distinct
PartitioningSkipWhile, Take, TakeWhile, Skip
ConcatenationConcat
EqualitySequenceEqual
GenerationDefaultEmpty, Empty, Range, Repeat
AggregationAggregate, Average, Count, LongCount, Max, Min, Sum

Now let us take mode complex example to understand the “where” operator. It can be used in more complex way. Suppose that you want those employees whose names are starts with letter “R”, ends with “j” and age should be between 12 and 30. You can easily get the desired result using Linq. Just write your condition in where clause.

Example

using System;
using System.Collections.Generic;
using System.Linq;
class Employee
{
    public int EmployeeID { get; set; }
    public string EmpName { get; set; }
    public int Age { get; set; }
}
class Program
{
    public static void Main()
    {
        List<Employee> empList = new List<Employee>()
        {
        new Employee() { EmployeeID = 1, EmpName = "Raj", Age = 28 } ,
        new Employee() { EmployeeID = 2, EmpName = "Rajesh",  Age = 16 } ,
        new Employee() { EmployeeID = 3, EmpName = "Sunder",  Age = 35 } ,
        new Employee() { EmployeeID = 4, EmpName = "Ram" , Age = 20 } ,
        new Employee() { EmployeeID = 5, EmpName = "Ronit" , Age = 38 }
        };

        var empResult = from s in empList
                    where s.Age > 12 && s.Age < 30 && s.EmpName.StartsWith("R") &&
                    s.EmpName.EndsWith("j")
                    select s.EmpName;
        foreach (var emp in empResult)
        {
            Console.WriteLine(emp);
        }
        Console.ReadLine();
    }
}


OrderBy operator

OrderBy clause provides the result in ascending or descending order. By default it provides the sorted list in ascending order. There is no need to write “ascending” keyword explicitly. You can also use multiple sorting in query.

var empResult = from s in empList
                       orderby s.EmpName descending
                            select s;


GroupBy operator

The GroupBy operator provides the groups of elements, according to key value. It works similar as the GroupBy clause of SQL query.

Consider the following code snippet.

{
   new Employee() { EmployeeID = 1, EmpName = "Raj", Age = 28 } ,
   new Employee() { EmployeeID = 2, EmpName = "Rajesh",  Age = 28 } ,
   new Employee() { EmployeeID = 3, EmpName = "Sunder",  Age = 35 } ,
   new Employee() { EmployeeID = 4, EmpName = "Ram" , Age = 20 } ,
   new Employee() { EmployeeID = 5, EmpName = "Ronit" , Age = 20 }
};
var empResult = from s in empList  group s by s.Age;
        foreach (var emp in empResult)
        {
            Console.WriteLine("Employee Age Group = "+emp.Key);
            foreach (Employee e in emp)
            Console.WriteLine("Student Name: {0}", e.EmpName);
        }


Notice the foreach loop, where each group contains a key and inner collection.

Output:

Employee Age Group = 28
Student Name: Raj
Student Name: Rajesh
Employee Age Group = 35
Student Name: Sunder
Employee Age Group = 20
Student Name: Ram
Student Name: Ronit

Aggregate functions

Aggregate functions are extension methods in LINQ. The following are the Aggregate functions.

  • Average()
  • Count()
  • Max()
  • Min()
  • Sum()

Example

using System;
using System.Linq;
class Program
{
    public static void Main()
    {
       int [ ] nums ={10,20,30,40,50,1,2,3,4,5};
       Console.WriteLine("Total count = "+nums.Count());
       Console.WriteLine("Average     = "+nums.Average());
       Console.WriteLine("Summation   = "+nums.Sum());
       Console.WriteLine("Max value   = "+nums.Max());
       Console.WriteLine("Min Value   = "+nums.Min());    
       Console.ReadLine();
    }
}


Run the above application you will get the following output.

Total count    = 10
Average        = 16.5
Summation   = 165
Max value     = 50
Min Value     = 1

LINQ SET OPERATORS

Linq provides standard set operators such as Except, Intersect, Union, and Distinct.
These operators behave in same way as in DBMS.

Union operator
This operator requires two collections of items. After applying union operator, you will get a new collection. It removes the duplicate entries.

Intersect operator
This operator provides the common elements in both lists.

Except operator
Except operator provides the element only from first list. The new list does not contain common elements.

linq set operators

Example

using System;
using System.Linq;
class Program
{
    public static void Main()
    {
        string [ ] List1 = { "One", "Two", "three", "Four", "Six" };
        string [ ] List2 = { "Two", "THREE", "Four", "Five" };
        Console.WriteLine("Unique elements in both list\n");
        var result = List1.Union(List2);
        foreach (string str in result)
        {
            Console.WriteLine(str);
        }
        Console.WriteLine("\n Common elements in both list\n\n");
        var IntResult = List1.Intersect(List2);
        foreach (string str in IntResult)
        {
            Console.WriteLine(str);
        }
        Console.WriteLine("\n Element in list1 after minus from list2 \n");
        var ExceptResult = List1.Except(List2);
        foreach (string str in ExceptResult)
        {
            Console.WriteLine(str);
        }    
        Console.ReadLine();
    }
}


Output:

Unique elements in both list
One
Two
three
Four
Six
THREE
Five
Common elements in both list
Two
Four
Element in list1 after minus from list2
One
three
Six

Partitioning Operators

The following are the partitioning operators in Linq.

  • Skip
  • SkipWhile
  • Take
  • TakeWhile
The Skip & SkipWhile operator is not supported in C# query syntax.

The Skip() method ignore or bypass the specified number of element starting from first element and returns the remaining elements.

SkipWhile() method works on a condition. It checks the condition from first element, if condition not met it skips the first element and so on. For example, suppose that there are 10 elements in a list and any condition is also applied on the elements, then it checks the elements from starting. Suppose that condition met on fifth element, then it will skip the first four element and return the remaining element.

The inverse of Skip() is Take() method. The Take() method returns the elements from starting according to given value.

Example

using System;
using System.Collections.Generic;
using System.Linq;
class Program
{
    public static void Main()
    {
        List<int> intList = new List<int> {10,20,30,40,50,60 };
        List<string> strList = new List<string>()
         { "One","Two", "Three", "Four", "Five","Six"  };
        Console.WriteLine("Skips the first three elemts from integer list");
        var firstList = intList.Skip(3);
        foreach(var item in firstList)
        {
            Console.WriteLine(item);
        }
        Console.WriteLine("SkipsWhile method (Length < 4) by string list") ;
        var secondList = strList.SkipWhile(s => s.Length < 4);
        foreach (string str in secondList)
        {
            Console.WriteLine(str);
        }
        Console.WriteLine("Takes the first three elements from integer list");
        var thirdList = intList.Take(3);
        foreach (var item in thirdList)
        {
            Console.WriteLine(item);
        }
        Console.WriteLine("TakeWhile method (Length < 4) by string list");
        var fourthList = strList.TakeWhile(s => s.Length < 4);
        foreach (string str in fourthList)
        {
            Console.WriteLine(str);
        }
        Console.ReadLine();
    }
}


Output:

Skips the first three elements from integer list
40
50
60
SkipsWhile method (Length < 4) by string list
Three
Four
Five
Six
Takes the first three elemts from integer list
10
20
30
TakeWhile method (Length < 4) by string list
One
Two

In the above example, SkipWhile() skips first two elements (One, Two ) because their length is less than 4 and finds third element whose length is equal or more than 4. Here finds element “Three” whose length is five. Once it finds any element whose length is equal or more than 4 characters then it will return the remaining elements.

Quantifier Operators

Following are the Quantifier Operators in Linq:

  • All
  • Any
  • Contain
“All” operator checks whether all elements in the list is satisfies the specified condition or not. It returns true if all the elements satisfy a condition otherwise it returns false.

“Any” operator checks whether any element in the list satisfy given condition or not. It returns true if any element satisfy the given condition.

As it name indicates “Contains” operator checks whether a particular element exists in the collection or not. It also returns true or false. It returns true if any element available in the given list.

Example

using System;
using System.Collections.Generic;
using System.Linq;
class Employee
{
    public int EmployeeID { get; set; }
    public string EmpName { get; set; }
    public int Age { get; set; }
}
class Program
{
    public static void Main()
    {
        List<Employee> empList = new List<Employee>()
        {
        new Employee() { EmployeeID = 1, EmpName = "Raj", Age = 28 } ,
        new Employee() { EmployeeID = 2, EmpName = "Rajesh",  Age = 16 } ,
        new Employee() { EmployeeID = 3, EmpName = "Sunder",  Age = 35 } ,
        new Employee() { EmployeeID = 4, EmpName = "Ram" , Age = 20 } ,
        new Employee() { EmployeeID = 5, EmpName = "Ronit" , Age = 38 }
        };
        Console.WriteLine("Does All employee satisfies the condition. Age > 12 && Age < 20");
        bool emp = empList.All(s => s.Age > 12 && s.Age < 20);
        Console.WriteLine(emp);
        Console.WriteLine("Does Any employee satisfies the condition Age > 12 && Age < 20");
        bool anyEmp = empList.Any(s => s.Age > 12 && s.Age < 20);
        Console.WriteLine(anyEmp);
        Console.ReadLine();
    }
}


Output:

Does All employee satisfies the condition. Age > 12 && Age < 20
False
Does Any employee satisfies the condition Age > 12 && Age < 20
True

You can use Contains operator as given below.

List<int> intList = new List<int>() { 10, 20, 30, 40, 50 };
bool result = intList.Contains(10);  // returns true

Element Operators (Methods)

Following are the Element Operators (Methods) in Linq:

  • ElementAt
  • First
  • Last
  • Single
  • ElementAtOrDefault
  • FirstOrDefault
  • LastOrDefault
  • SingleOrDefault
The ElementAt() method returns an element according to given index from a collection. If the given index is out of the range of a collection then it will throw an Index out of range exception. In the given below example, intList.ElementAtOrDefault(11) returns 0 (default value of int) because intList does not include 12th element.

First() method returns the first element of a collection. First() method will throw an exception if a collection does not include any element.

The Last() method returns the last element from a collection. It can also check some condition using lambda expression or Func delegate.

Example

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
    public static void Main()
    {
        List<int> intList = new List<int>() { 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 };
        List<string> strList = new List<string>() { "One", "Two", "Three", null, "Five" };
        Console.WriteLine("1st Element in intList: {0}", intList.ElementAt(0));
        Console.WriteLine("1st Element in strList: {0}", strList.ElementAt(0));
        Console.WriteLine("4th Element in intList: {0}", intList.ElementAtOrDefault(3));
        Console.WriteLine("4th Element in strList: {0}", strList.ElementAtOrDefault(3));
        Console.WriteLine("11th Element in intList: {0}  :  default int value",
                        intList.ElementAtOrDefault(11));
        Console.WriteLine("11th Element in strList: {0}  : default string value is (null)",
                         strList.ElementAtOrDefault(11));
              Console.WriteLine("First Even Element in intList: {0}", intList.First(i => i % 2 == 0));
        Console.WriteLine("First Element in strList: {0}", strList.First());         
        Console.WriteLine("Last Element in intList: {0}", intList.Last());   
        Console.WriteLine("Last Element in strList: {0}", strList.Last());    
        Console.ReadLine();
    }
}


Output:

1st Element in intList: 10
1st Element in strList: One
4th Element in intList: 40
4th Element in strList:
11th Element in intList: 0  :  default int value
11th Element in strList:   : default string value is (null)
First Even Element in intList: 10
First Element in strList: One
Last Element in intList: 100
Last Element in strList: Five
▲