Deferred vs Immediate Query Execution in LINQ

There are two execution behaviours in LINQ namely Deferred and Immediate.

Deferred Execution : In this type query execution does not take place at time of declaration rather execution takes place whenever query variable is iterated through various loops like for, foreach etc.

Example to demonstrate Deferred Execution :-

using System;
using System.ComponentModel;
using AutoMapper;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Student
    {
        public int stu_id{get;set;}
        public string stu_name{get;set;}
        public int age{get;set;}

    }

    class Program
    {
        static void Main()
        {
           var stulist = new List<Student>(
               new Student[]{
                   new Student{stu_id = 1,stu_name = "Deepak", age = 24 },
                   new Student{stu_id = 2,stu_name = "Pooja", age = 23 },
                   new Student{stu_id = 3,stu_name = "Onam", age = 26 },
                   new Student{stu_id = 4,stu_name = "Suraj", age = 29 }
               });

            var student_list = from s in stulist
                               where s.age > 25
                               select new{s.stu_name};

            stulist.Add(new Student { stu_id = 5, stu_name = "aditi", age = 30 });

            foreach(var s in student_list)
                Console.WriteLine(s.stu_name);

            Console.ReadKey();
        }
    }
}

Output :

Students whose age is more than 25
Onam
Suraj
aditi

In above example query execution takes place when the query variable is used within foreach loop. This can be proved as a new student is added to query variable even after the query. This type of execution is used whenever frequent execution of query is required with latest information from database which are updated frequently.

Immediate Execution :

In this type query execution takes place at time of declaration. Immediate execution is performed on query returning (a single or set of value) like sum(),average() etc. A query can be forced to execute immediately by calling various methods from query like Count(),Avg(),Sum() which return single value. By calling ToList(), ToArray(), ToDictionary() methods on a query returning multiple value, can be forced to execute immediately.

using System;
using System.ComponentModel;
using AutoMapper;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Student
    {
        public int stu_id{get;set;}
        public string stu_name{get;set;}
        public int age{get;set;}

    }

    class Program
    {
        static void Main()
        {
           var stulist = new List<Student>(
               new Student[]{
                   new Student{stu_id = 1,stu_name = "Deepak", age = 24 },
                   new Student{stu_id = 2,stu_name = "Pooja", age = 23 },
                   new Student{stu_id = 3,stu_name = "Onam", age = 26 },
                   new Student{stu_id = 4,stu_name = "Suraj", age = 29 }
               });

            var student_list = (from s in stulist
                               where s.age > 25
                               select new{s.stu_name}).Count();

            stulist.Add(new Student { stu_id = 5, stu_name = "aditi", age = 30 });

            Console.WriteLine("Total students {0}",student_list);

            Console.ReadKey();
        }
    }
}

Output :

Total students 2

In above example query execution takes place at time of declaration as new student which is added after query variable is not considered in counting of total students whose age is more than 25. This kind of execution is referred to as Immediate Query Execution.