IEnumerable vs IQueryable in LINQ – C#

Today we are going to discuss about one of the most confused topics of LINQ, IEnumerable and IQueryable. They look very similar to one another and that’s the reason why they creates alot of confusion even to the developers. Moreover it is one of the most favorite question of Interviewers.

First thing to know that both are used to query data from Database and Collections. So how they differ from each other ? Let us take an example first and notice how the two works and then go through the TABLE OF DIFFERENCES.

Example
Step 1 : Let us create a Table named ‘Employee’ in SQL Server.

T-SQL Code

CREATE TABLE Employee
  (
     ID INT,
     Name VARCHAR(10),
     Salary FLOAT,
     Department VARCHAR(15),
     Gender VARCHAR(10)
  )

GO

INSERT INTO Employee VALUES(1,'George',50000,'CSE', 'Female')
INSERT INTO Employee VALUES(2,'John',70000,'IT', 'Male')
INSERT INTO Employee VALUES(3,'Lucy',40000,'EC', 'Male')
INSERT INTO Employee VALUES(4,'Lewis',30000,'MEC', 'Female')
INSERT INTO Employee VALUES(5,'David',35000,'AERO', 'Male')

Step 2 :

Open Visual Studio and create a new project. After that create a new ITEM (right click on the solution -> Click Add -> New Item )
Make sure Data is selected in left panel and in right select ‘LINQ to SQL Classes’. Name it – EmployeeDB.dbml

Then Open ‘Server Explorer’ (View Menu -> Server Explorer). Then Expand ‘Data Connections’ -> Tables -> Employee. Drag and Drop the Table to right side panel.

Step 3 :
Let us create object of EmployeDB to access our table. and create 2 methods one for IEnumerable and other IQueryable.

using System;
class Program
{
    static void Main(string[] args)
    {
        EmployeeDBDataContext db = new EmployeeDBDataContext();
        ProcessEnumerable(db);
        //ProcessQueryable();
        Console.ReadKey();
    }
    private static void ProcessEnumerable(EmployeeDBDataContext db)
    {
        var res = db.Employees
                .AsEnumerable()
                .Where(x => x.Gender == "Male")
                .OrderByDescending(x => x.Salary)
                .Take(2); Console.WriteLine("Enumerable");

        foreach (var e in res)
        {
            Console.WriteLine(e.Name + " " + e.Salary + " " + e.Gender);
        }
    }
    private static void ProcessQueryable()
    {
        EmployeeDBDataContext db = new EmployeeDBDataContext();
        var res = db.Employees
        .AsQueryable()
        .Where(x => x.Gender == "Male")
        .OrderByDescending(x => x.Salary)
        .Take(2);

        Console.WriteLine("Queryable");
        foreach (var e in res)
        {
            Console.WriteLine(e.Name + " " + e.Salary + " " + e.Gender);
        }
    }
}

NOTE : Read the explaination carefully

Explaination

AsEnumerable()

It divides the query into 2 sql queries one with its above lines of code and 2nd from its below lines of code.

What this functions does it fetch all the records from the Employee table and then when it have all the record it will do the filtration whatever provided.
To check this Let us go to SQL Server and run the SQL Server Profiler (Tools -> SQL Server Profiler). Then click on the ‘Event Selection’ Tab. Deselect the event as they are not required now. Then run it. See image.

Now flip to Visual Studio and Run your application. Go to Trace Window you can see the query generated by the code.

You can see here that 1st part is executed in the SQL i.e., that is lines of code before ‘AsEnumerable()’.

Now Let us move ‘AsEnumerable()’ below orderbydesc, as show below

Again observe it in SQL profiler, you will get

Now you can see the where and orderby clause in the query.

AsQueryable()

Here it does not matters where you write the AsQueryable(), all the filters get applied to the SQL Query. You can test yourself by changing the position of ‘AsQueryable’ as we done with ‘AsEnumerable’.

Table of Differences