JumpStart # 17–Difference between IEnumerable<T> and IQueryable<T> in EntityFramework SQL

Before looking at the code, Read about – What is JumpStart?

 

IQueryable<T> basically generates the complete expression tree and then executes it to get results, where are it is not true in IEnumerable<T> scenario. But still IQueryable<T> is inherited from IEnumerable<T>. IQueryable<T>’s extension methods take expressions based on which expression trees are build, while for IEnumerable<T>’s extensions methods takes functions based on which results are enumerated and published. From afore said reasons, IEnumerable<T> is better on Collections stored in memory, where as IQueryable<T> can optimize performance of Remote Data Sources like Databases.

Lets get started with simple Database called “Sample” having a table “Employee”. Put some data into “Employee” table for our testing purpose.

IEnumerableIQueryableDemo1

Now lets create a C# Console Application – “IEnumerableIQueryableDemo”. Add EntityFramework Model (edmx) to map above created database to C# objects.

IEnumerableIQueryableDemo2

Finally write the following code to check out the difference between IEnumerable<T> and IQueryable<T>.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IEnumerableIQueryableDemo;

namespace IEnumerableIQueryableDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            IEnumerable<Employee> _enumerableEmployees;
            IQueryable<Employee> _queryableEmployees;

            using (SampleEntities entities = new SampleEntities())
            {
                _enumerableEmployees = (from p in entities.Employees
                                        where p.EmpId > 5
                                        select p);

                _queryableEmployees = (from p in entities.Employees
                                       where p.EmpId > 5
                                       select p);

                Console.WriteLine("IEnumarable Collection ---->");
                foreach (Employee e in _enumerableEmployees.Take(3))
                {
                    Console.WriteLine(e.Name);
                }

                Console.WriteLine("IQueryable Collection ---->");
                foreach (Employee e in _queryableEmployees.Take(3))
                {
                    Console.WriteLine(e.Name);
                }
            }

            Console.ReadLine();
        }
    }
}

 

We can get to know the difference between by starting SQL Profiler before execution of this program. Once we execute this program, EntityFramework generated SQL can be easily viewed in SQL Profiler.

For IEnumerable<T> –

SELECT 
[Extent1].[EmpId] AS [EmpId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Email] AS [Email]
FROM (SELECT 
      [Employee].[EmpId] AS [EmpId], 
      [Employee].[Name] AS [Name], 
      [Employee].[Email] AS [Email]
      FROM [dbo].[Employee] AS [Employee]) AS [Extent1]
WHERE [Extent1].[EmpId] > 5

For IQueryable<T> –

SELECT TOP (3) 
[Extent1].[EmpId] AS [EmpId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Email] AS [Email]
FROM (SELECT 
      [Employee].[EmpId] AS [EmpId], 
      [Employee].[Name] AS [Name], 
      [Employee].[Email] AS [Email]
      FROM [dbo].[Employee] AS [Employee]) AS [Extent1]
WHERE [Extent1].[EmpId] > 5

Difference goes with TOP(3), so in the first case of IEnumerable<T> all records are sent across to client and then Take operation performed with in-memory objects. But with IQueryable<T> only Top(3) records are sent to client.

Final output of Program –

IEnumerableIQueryableDemo3

You may also like...

  • saurabh nijhawan

    Loved this article!!