IEnumerable vs IQuerable

Introduction

IEnumerable and IQuerable both are the interfaces for .Net collections. And IQuerable inherits the IEnumerable interfaces, in other words all the functionality implemented by IQuerable also can be implemented. So the question here is the difference and why the Microsoft provided the IQuerable interface.

Differences

The IEnumerable is used to iterate a read-only collection. It has only one method, GetEnumerator(), that allows you to iterate the read-only collection using a foreach loop. It only iterates in the forward direction.

In the Entity Framework solution (that we create here as a sample), if your data loads in a collection then you can easily see the difference between both of these, it effects the performance. First we will create a sample Entity Framework sample here. Here is the code of Program.cs.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ManyToManyEfSample2
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer<SchoolContext>(null);
            using (SchoolContext db = new SchoolContext())
            {
                Course CS = new Course();
                Course IT = new Course();
                CS.Title = "Computer Science";
                IT.Title = "Information Technology";

                db.Courses.Add(CS);
                db.Courses.Add(IT);

                Person P1 = new Person();
                P1.FirstName = "Vivek";
                P1.LastName = "Tripathi";
                P1.Courses.Add(CS);
                P1.Courses.Add(IT);

                db.People.Add(P1);
                db.SaveChanges();
            }
        }
    }

    public class Person
    {
        public int PersonId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        public virtual ICollection<Course> Courses { get; set; }

        public Person()
        {
            Courses = new HashSet<Course>();
        }
    }

    public class Course
    {
        public int CourseId { get; set; }
        public string Title { get; set; }

        public virtual ICollection<Person> Students { get; set; }

        public Course()
        {
            Students = new HashSet<Person>();
        }
    }

    public class SchoolContext : DbContext
    {
        public DbSet<Course> Courses { get; set; }
        public DbSet<Person> People { get; set; }

        public SchoolContext()
            : base("ConnString")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Entity<Course>().
                HasMany(c => c.Students).
                WithMany(p => p.Courses).
                Map(
                    m =>
                    {
                        m.MapLeftKey("CourseId");
                        m.MapRightKey("PersonId");
                        m.ToTable("PersonCourses");
                    });

        }
    }
}

Now when fetching the data from the Person Table we will see the difference between the two .Net Collections.

using (SchoolContext db = new SchoolContext())
{
   IEnumerable<Person> P = db.People;
   IEnumerable<Person> P2 = P.Where(x => x.PersonId == 1).ToList<Person>();
}

In the block, x => x.PersonId == 1 is an anonymous method that can be executed like any other method. And the “Where” will execute the method once for each person, yielding values for which the method returned true. So you can say all the processing (anonymous function) call happened on the client side.

If we run this code we will see the query on the profiler.



So it's clear from the profiler that when using the IEnumerable, we fetch all the data from the DB and the filter criteria is implemented on the client side.

When changing the code it will be like this:

IQueryable<Person> P = db.People;
var P3 = P.Where(x => x.PersonId == 1).ToList();

In the second block, x => x.PersonId == 1 is an expression tree(), that can be thought of as "is the 'PersonId' property == 1".

You can see the query in the following window. The filter criteria (where clause) is applied in the DB Query. So, performance-wise the Iquerable is much more important.



Conclusion

• If you create IQueryable, then the query may be converted to SQL and will run on the database server.
  
  
• If you create IEnumerable, then all rows will be pulled into memory as objects before running the query.

So in short we can say that IEnumerable is great for working with in-memory collections, but IQueryable allows for a remote data source such as a database or web service.