Entity Framework internals :IEnumerable and IQueryable

Now days, Entity framework is one the most used ORM in .NET world. Still I can see lots of people confused with IQueryable and IEnumerable. If they dont' know what is difference between them then they are going to make mistakes .When you first look at it both looks same. But there are quite differences and If you don’t understands internals of it then it will impact your query performance also.

IEnumerable vs IQueryable:

Here is the basic difference between IEnumerable and IQueryable. When you write queries with IEnumerable it executes queries on Server and load data in memory and then filter data on client side. This will be a problem when you are fetching large amount of data. While IQueryable executes queries on server side with all filters and then load all data into memory.

Example:

Sound complex!. Let’s write a simple example to understand it better. I am going to create a simple table in database called student table.



Here’s SQL Query for creating table.

CREATE TABLE [dbo].[Student](
    [StudentId] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Percentage] [int] NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Now we are going to insert some data into it.



Our database is ready, It’s time to write some code for the same. So Let’s write it.



After creating Console application, I’m going to add Entity Framework to my console application via Nuget.


In this example, We are going to use code first model, So We need to create Student Class respective to Student Table in our Database.  Following is a code for that.

using System.ComponentModel.DataAnnotations;

namespace EntityFrameworkIQAndIM
{
    public class Stduent
    {
        [Key]
        public int  StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Percentage { get; set; }
    }
}

And Following is my Data Context class.

using System.Data.Entity;

namespace EntityFrameworkIQAndIM
{
    public class StudentContext : DbContext
    {
        public StudentContext()
            : base("StudentConnectionString")
        {
            Database.SetInitializer<StudentContext>(null);
        }
        public DbSet<Student> Students { get; set; }
    }
}

Here I have set database initializer null because we are going to use it with existing database. Now it’s time to write a code.  In code we are going to find students who are having higher than 70 Percentage and then we are  going to print those students. 

IEnumerable Way:

First, We are going to write code IEnumerable way.

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

namespace EntityFrameworkIQAndIM
{
    class Program
    {
        static void Main(string[] args)
        {
            using (StudentContext studentContext = new StudentContext())
            {
                //IEnumerable way
                IEnumerable<Student> students = studentContext.Students.ToList();
                students = students.Where(s => s.Percentage > 70);
                PrintStudent(students);
            }            
        }

        private static void PrintStudent(IEnumerable<Student> students)
        {
            foreach (var student in students)
            {
                Console.WriteLine(student.FirstName);
                Console.WriteLine(student.LastName);
            }
        }
    }
}

Here in the above code. I have fetched students in Enumerable and then applied where condition. Let’s run example.



So our example works fine, Now let’s check what’s query has been created to fetch data from SQL Server via profiler. Let’s profile our SQL Server database using SQL Server profiler.



Now let’s again Run our application and see the query like below.



Here in the above screenshot you can see our where clause is applied on the client instead of SQL Server query. So there will be performance problem. If you have lots of data.

IQueryable way:

Now let’s run same example, IQueryable way. Following code I changed.

static void Main(string[] args)
{
    using (StudentContext studentContext = new StudentContext())
    {
        //IEnumerable way
        //IEnumerable<Student> students = studentContext.Students.ToList();

        //IQueryable way
        IQueryable<Student> students = studentContext.Students;
        students = students.Where(s => s.Percentage > 70);
        PrintStudent(students);
    }            
}

Now let’s run application and see profiler like below.



So now you can see that your where clause is there with SQL query itself.
That’s it. Hope you like it. Stay tuned for more.

Source code for this example is available on github at -https://github.com/dotnetjalps/EntityFrameworkIEnumerableIQueryable