RSS

Tag Archives: IQueryable

IEnumerable vs. IQueryable

When you are heavily working with the Entity Framework using LINQ you have certainly touched both interfaces. In one hand IEnumerable and on the other hand IQueryable. Looking at their inheritance tree you could see following:

IEnumerable
-IEnumerable
–IQueryable

So what is the great difference between both of them. According the interface declaration they implement the same methods, therefore thus you can think that the methods work the same way. But the reality is far away of them.

To test my apprehension that both of them do not call the same SQL-Command on the server i constructed following:

public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.Where(d=>d.Kolli > 0).Count();
}

public void IQueryableTestMethod(IQueryable coll)
{
    int count = coll.Where(d => d.Kolli > 0).Count();
}

and I made the call following:

IQueryable sentItems= DBSetting.ObjectContext.DeliveryItem.Where(d=>d.Pieces > > 100);
IQueryableTestMethod(sentItems);
IEnumerableTestMethod(sentItems);

Both methods were called with the same IQueryable as input. Also the method body are exactly the same. The only and only difference is the type of parameter that was used. In the first method called IEnumerableTestMethod the type of parameter is IEnumerable<T> the second one called IQueryableTestMethod IQueryable<T>.

Now, at first sight this is the only difference. I agree with you.

But if you are going to start testing what kind of SQL-Statements were executed against the SQL-Server using IntelliTrace you will see following result:

IQueryableTestMethod:

SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[DeliveryItem] AS [Extent1]
WHERE ([Extent1].[Pieces ] > 100) AND ([Extent1].[Kolli] > 0)
) AS [GroupBy1]

IEnumerableTestMethod:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[DeliveryNoteId] AS [DeliveryNoteId],
[Extent1].[OrderId] AS [OrderId],
[Extent1].[Pieces] AS [Pieces],
[...continuing all other columns of DeliveryItem...]
FROM [dbo].[DeliveryItem] AS [Extent1]
WHERE [Extent1].[Pieces] > 100

So if you be aware you see the big difference. Ignoring the fact that the second query returns a SELECT * of all filtered DeliveryItem you see at first sight that in the second query the COUNT is missing. Now the question is if this is bad or not. Obviously this is bad, but why?

The first query returns only the number of items that matches the query, but the second query returns all data that matches the condition and computes the number of items on the client-side. This might be a huge performance issue if the returning data set is getting bigger and bigger.

If you have IEnumerable<T> as paramenter the first method executed on that collection will create the SQL-Statement that is executed against the SQL-Server. Therefore having the statement coll.Where(a => a.Kolli > 0).Where(a => a.Kolli < 0).Count(); (unnoticed that it is useless) will not change the query in IEnumerable but using IQueryable there will be the change in the WHERE condition

But if you have a IEnumerable parameter you have another possibility, using AsQueryable, to speed up the query and not to load all data.

This can be done following:

//#1
public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.AsQueryable().Where(a => a.Kolli > 0).Count();
}

Then you will get the same SQL-Query like in the IQueryalbeTestMethod, but be aware of using AsQueryable, using it following will have no improvement of the SQL-Query, it still remains like the SQL-Query you will get in the original IEnumerableTestMethod:

//#2
public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.Where(a => a.Kolli > 0).AsQueryable().Count();
}

In this solution the Where Extension method already executes the above showed SQL-Statement against the SQL-Server and the AsQueryable has no effect.

Also be aware, because if you are using the method seen in #1 and you call it trough following example you are fethching all data from the server and you have no improvement of the SQL-Query.

var list = collection.ToList();
//now you have loaded the data via a "SELECT * FROM Table"
IEnumerableTestMethos(list);

In conclusion I want to say that using IQueryable and IEnumerableis very risky if you just write IEnumerable<T> as method parameter. You should always be aware and use as much as possible the IQueryable<T>.

Advertisements
 
1 Comment

Posted by on August 7, 2011 in EF, LINQ, SQL

 

Tags: , , ,