Lately I have been doing some performance improvements in one of the modules of our web application in the office. I am writing this to share my observation between the 2 important subjects.

1. IEnumerable

– Exposes an enumerator, which supports a simple iteration over a non-generic collection.

2. IQueryable

– Provides functionality to evaluate queries against a specific data source wherein the type of the data is not specified.

Okay let’s go to the real stuff. Lets say we have a collection from our EDMX. And we want to pick the top 100 last inserted records. So how do we do that?

1. IEnumerable

using (var context = new MyContext()) 
{
    IEnumerable<Employee> employees = context.Employees.OrderByDescending(e => e.EmployeeId);
    var latestEmployees = employees.Take(100);
}

1. IQueryable

using (var context = new MyContext()) 
{
    IQueryable<Employee> employees = context.Employees.OrderByDescending(e => e.EmployeeId);
    var latestEmployees = employees.Take(100);
}

Okay that was easy. Now let’s see how it is being interpreted by SQL Server.

1. Using IEnumerable – As you can see below it gets all the records without applying the top 100 filter which is really a bad idea if you have thousands of rows.

IEnumerable

 

2. Using IQueryable – Now with IQueryable, the query result is now different which is what we want. Apply the top 100 first before we execute the query.

IQueryable
As you can see from the above comparison, there is a very huge difference between the 2 when they are executed. I would suggest IEnumerable if you are dealing with in memory collections. If you want to deal with database collections I would suggest IQueryable.

 

I hope I had shared some helpful information to you regarding these topics which we use everyday and hopefully if you like my blog please also visit my site http://www.francorobles.com to know more about me.

Advertisements