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

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

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#

TrackbackPrint

Tags:

Categories:

Location: BlogsThe Brave Programmer