Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 6 - Restriction Operators in LINQ

Suggested Videos
Part 3 - Extension Methods
Part 4 - LINQ Aggregate Functions
Part 5 - Aggregate function in LINQ



The WHERE standard query operator belong to Restriction Operators category in LINQ. Just like SQL, the WHERE standard query operator in LINQ is used to filter rows. The filter expression is specified using a predicate.



The following are the 2 overloaded versions of WHERE extension method in Enumerable class
public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, bool> predicate);

public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, int, bool> predicate);

What is a Predicate?
A predicate is a function to test each element for a condition

In the following example, the Lambda expression (num => num % 2 == 0) runs for each element in List<int>. If the number is divisible by 2, then a boolean value true is returned otherwise false.

using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo
{
    class Program
    {
        static void Main()
        {
            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);

            foreach (int evenNumber in evenNumbers)
            {
                Console.WriteLine(evenNumber);
            }
        }
    }
}

// Using SQL like syntax
IEnumerable<int> evenNumbers = from num in numbers
                                                           where num % 2 == 0
                                                           select num;

Note: The where query operator is optional.

The program prints all the even numbers
where linq method example

When you hover the mouse ove WHERE method in the above example, visual studio intellisense shows the following. Notice that in this case, the predicate expects an int input parameter and returns a boolean value. The lambda expression that is passed operates on an int type and should return boolean, otherwise there will be compile time error.
Restriction Operators in LINQ

So this means, the line below from the above example
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);

or like below
using System;
using System.Collections.Generic;
using System.Linq;

namespace Demo
{
    class Program
    {
        static void Main()
        {
            List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

            IEnumerable<int> evenNumbers = numbers.Where(num => IsEven(num));

            foreach (int evenNumber in evenNumbers)
            {
                Console.WriteLine(evenNumber);
            }
        }

        public static bool IsEven(int number)
        {
            if (number % 2 == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

Example 2:
The int parameter of the predicate function represents the index of the source element
public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, intbool> predicate);

The following program prints the index position of all the even numbers
using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo
{
    class Program
    {
        static void Main()
        {
            List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

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

            foreach (int evenNumber in evenNumberIndexPositions)
            {
                Console.WriteLine(evenNumber);
            }
        }
    }
}

Example 3:
Use the following SQL to create Departments and Employees tables

Create table Departments
(
     ID int primary key identity,
     Name nvarchar(50),
     Location nvarchar(50)
)
GO

Create table Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int,
     DepartmentId int foreign key references Departments(Id)
)
GO

Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Sydney')
GO

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000, 1)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000, 3)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000, 1)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000, 2)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000, 2)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000, 1)
GO

Add an ADO.NET entity data model based on the above 2 tables.

Write a LINQ query to retrieve IT and HR department names and all the male employees with in these 2 departments.
using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo
{
    class Program
    {
        static void Main()
        {
            EmployeeDBContext context = new EmployeeDBContext();

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

            foreach (Department department in departments)
            {
                Console.WriteLine("Department Name = " + department.Name);
                foreach (Employee employee in department
                    .Employees.Where(emp => emp.Gender == "Male"))
                {
                    Console.WriteLine("\tEmployee Name = " + employee.FirstName
                        + " " + employee.LastName);
                }
                Console.WriteLine();
            }
        }
    }
}

Output:
where method in linq example

linq tutorial

3 comments:

  1. Hi I have seen your video on EF.
    If i use linq query in above example like
    var lst = from Dept in employeeDBContext.Departments
    from Emp in Dept.Employees
    where (Dept.Name == "IT" || Dept.Name == "HR") && Emp.Gender == "Male"
    select new { Dept.Name, Emp.FirstName, Emp.LastName };

    I will get same result in single query.
    I have to write above LINQ query using Lambada Expression. Please let me know how to write.
    Thanks-- Ashish

    ReplyDelete
  2. LinqPad tool will help you to learn both Lambda expression Query and it's related LINQ-SQL query.

    ReplyDelete
  3. using(CompanyEntities db=new CompanyEntities())
    {
    IEnumerable departments = db.Departments.Where(x => x.Name == "IT" || x.Name == "HR");
    foreach (Department dept in departments)
    {
    Console.WriteLine("Department Name : " + dept.Name);
    IEnumerable employees = db.Employees.Where(x => x.DepartmentId == dept.ID && x.Gender=="Male");
    foreach(Employee emp in employees)
    Console.WriteLine("Employee: " + emp.FirstName+" "+emp.LastName+" "+emp.Gender);

    Console.WriteLine("======================= " );

    }

    }

    ReplyDelete

It would be great if you can help share these free resources