The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

The difference between IQueryable and IEnumerable

Mar14

Written by:
2013/03/14 09:20 AM RssIcon

What is the difference between IQueryable and IEnumerable? Why do we have to understand the difference between IQueryable and IEnumerable? Will it really matter?

Well it will matter, depending on what you are doing. Choosing to return IQueryable or IEnumerable when querying 1 million rows can make a big difference in performance. It could also impact on how you work with the data.

The main differences between IQueryable and IEnumerable are:

  • IEnumerable is best suitable for working with in-memory collection, whereas IQueryable is best suited for remote data sources, like a database or web service.
  • IEnumerable has immediate execution, whereas IQueryable has deferred execution or lazy loading.

So when you have to simply iterate through the in-memory collection, use IEnumerable, if you need to do any manipulation with the collection like Dataset and other data sources, use IQueryable.

Let's have a look at a practical example. First we will look at IEnumerable and then at IQueryable.

IEnumerable

The following Linq query returns a filtered set of data from a customer table in a database. For the sake of this example the Customer table has 15,000,000 rows in it.

 

MyDataContext ctx = new MyDataContext();

IEnumerable<Customer> cust = Customers

.Where(c => c.LastName.Contains("A"));

Cust = cust.Take(10);

The result is that a Customer object of 10 items (rows) is returned. This is what we expect. However when we look at the SQL that is generated, we soon realize that this is not what we wanted.

 

DECLARE @p0 NVarChar(1000) = '%A%'

SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], 

[t0].[FirstName], [t0].[MiddleName], [t0].[LastName], 

[t0].[Suffix],[t0].[CompanyName], [t0].[SalesPerson],

[t0].[EmailAddress],[t0].[Phone], [t0].[PasswordHash],

[t0].[PasswordSalt],[t0].[rowguid] AS [Rowguid],

[t0].[ModifiedDate] 

FROM [SalesLT].[Customer] AS [t0] 

WHERE [t0].[LastName] LIKE @p0

What's wrong with this SQL statement? Well, the most obviousis, it is returning all rows in the table that contain the letter "A" in the LastName Column. If our table has 15 million rows, and a great percentage of the lastname will contain the letter "A", then it stands to reason that a few million, perhaps over 10 or even over 14 million rows will be returned. This could have serious implications for memory, performance and a bunch of other things.

But the amount of rows that were finally returned were only 10. Why? Because IEnumerable executes the first statement immediately (IEnumerable cust = Customers.Where (c => c.LastName.Contains("A"));) which pulls through millions of rows into memory. It then filters on those millions of rows (cust = cust.Take(10);) in memory resulting in 10 rows finally being returned.

IQueryable

Now let's do the exact same thing, but this time return an  IQueryable. The following Linq  query returns a filtered set of data from a customer table in a database. For the sake of this example the Customer table has 15,000,000 rows in it.

 

MyDataContext ctx = new MyDataContext();

IQueryable< Customers > cust = Customers

.Where(c => c.LastName.Contains("A"));

cust = cust.Take(10);

The result is that a Customer object of 10 items (rows) is returned. This is what we expect. When we look at the SQL that is generated, we realize that this is exactly what we expected.

 

DECLARE @p0 NVarChar(1000) = '%A%'

SELECT TOP (10) [t0].[CustomerID], [t0].[NameStyle],

[t0].[Title],[t0].[FirstName], [t0].[MiddleName],

[t0].[LastName], [t0].[Suffix],[t0].[CompanyName],

[t0].[SalesPerson],[t0].[EmailAddress], [t0].[Phone],

[t0].[PasswordHash],[t0].[PasswordSalt],

[t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate] 

FROM [SalesLT].[Customer] AS [t0] 

WHERE [t0].[LastName] LIKE @p0

Notice the use of the "Top (10)" in the SQL Statement. This is applying a further filter to the data. This was added because of our cust.Take(10) addition to our Linq  query. What's happening here? Well because of deferred execution or lazy loading of IQueryable the SQL query is constructed at the very last moment, only when it is enumerated. So any additions to the Linq  query will be part of the SQL as long as it is not enumerated. While in our IEnumerable example the SQL is constructed immediately and returned and then the Take(10) additional filter is applied when in memory.

In this example only 10 rows are returned from the SQL database. The IQueryable took 0.7 seconds. The IEnumerable looked like it returned 10 rows as well, but it took 34.7 seconds. This is clearly not what you wanted. That's a 4857% loss in performance.  Never mind the memory bloating that is taking place.

IEnumerable and IQueryable Compared

IEnumerable

IQueryable

IEnumerable exists in System.Collections Namespace.

IQueryable exists in System.Linq Namespace.

IEnumerable can move forward only over a collection, it can't move backward and between the items.

IQueryable can move forward only over a collection, it can't move backward and between the items.

IEnumerable is best to query data from in-memory collections like List, Array etc.

IQueryable is best to query data from out-memory (like remote database, service) collections.

While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.

While query data from database, IQueryable execute select query on server side with all filters

IEnumerable is suitable for LINQ to Object and LINQ to XML queries.

IQueryable is suitable for LINQ to SQL queries

IEnumerable supports deferred execution.

IQueryable supports deferred execution

IEnumerable doesn't supports custom query.

IQueryable supports deferred execution

IEnumerable doesn't supports custom query

IQueryable supports custom query using CreateQuery and Execute methods

IEnumerable doesn't support lazy loading. Hence not suitable for paging like scenarios.

IQueryable support lazy loading. Hence it is suitable for paging like scenarios.

Extension methods supports by IEnumerable takes functional objects

Extension methods supports by IEnumerable takes expression objects means expression tree

Conclusion

There is a difference between IQueryable and IEnumerable. One needs to understand those differences in order to get the best performance and the best result out of our Linq queries. Both have their place and when used effectively will enhance our code and make our applications not only scream but also correct (as far as IQueryable and IEnumerable is concerned).

Technorati Tags: IQueryable,IENumerable,Linq,C#

   



Tags:
Categories:
blog comments powered by Disqus
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<December 2017>
SunMonTueWedThuFriSat
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print