IEnumerable and IQueryable clarification?

Question

After reading this question, I need to clear up some things.

IQueryable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;

IEnumerable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;

Questions:

1) Is it ok to say that: in the first query the SQLServer is running the whole operation including where clause and returning ONLY the relevant rows - while the second one does SELECT * ... and returns all rows into C# and THEN filters ?

2) What about if I have a collection merely - in memory. ( var lstMyPerson = new List<MyPerson>() )

IQueryable<MyPerson> lst = from c in lstMyPerson 
where c.City == "<City>"
select c;

vs

IEnumerable<MyPerson> custs = from c in lstMyPerson 
where c.City == "<City>"
select c;

what will be the difference in execution now?

Take a look at this relevant topic: http://stackoverflow.com/questions/252785/what-is-the-difference-between-iqueryablet-and-ienumerablet. And also, you are correct in your assumption in #1. Not 100% sure about #2 so I'll leave it to another. — blizz, Jun 21 '12 at 06:50
@blizz i already read that. all answers there are from a book POV. nothing answering my question there.... :(... ill be **happy** to see which line answeres my question — Royi Namir, Jun 21 '12 at 06:51

Lasse V. Karlsen · Accepted Answer · 2012-06-21T07:00:09.913

34

1: No, that is incorrect

Since you're only storing the result into an IEnumerable<Customer>, but still have the exact same expression that produces the result, they will both execute on the server and return only the relevant rows.

You would get the difference in behavior with this:

IEnumerable<Customer> custs = from c in (IEnumerable<Customer>)db.Customers
    where c. City == "<City>"
    select c;

In this case you are forcing the db.Customers collection to be used as IEnumerable<T>, which when enumerated will fetch the entire collection.

Note that this:

IEnumerable<Customer> x = from c in db.Customers
                          where c.City == "<City>"
                          select c;

is not the same as this:

IEnumerable<Customer> x = from c in db.Customers
                          select c;
IEnumerable<Customer> y = x.Where(c => c.City == "<City>");

In the first case, the where clause will be a part of the SQL, in the second it won't. That is why the linked question/answer involves a difference, whereas your code does not.

Also note that only the statements you have written will not in fact execute anything at all on the server, since they will effectively only store a lazy collection. If you go ahead and enumerate those collections, at that point the relevant bits will be executed on the server.

2: List<T> does not implement or have extension methods for IQueryable<T>, nor will the LINQ operators involved return anything compatible with IQueryable<T>

In this case, the first will not compile.

edited Jun 21 '12 at 07:00

answered Jun 21 '12 at 06:52

Lasse V. Karlsen

380,855
102
628
825

No, as I said, they will both do the same. However, in the case of the linked code, the `Where` LINQ operator is applied to the `IEnumerable` or the `IQueryable`, you have added a `where` clause to the original query. This changes the order of the operations, so in your case, they will execute the same way. In the linked answer, they will not. – Lasse V. Karlsen Jun 21 '12 at 06:57
regarding _forcing the db.Customers collection to be used as IEnumerable, which when enumerated will fetch the entire collection_ , if i understood correctly , this code will get ALL CUSTOMERS and THEN Filter ? – Royi Namir Jun 21 '12 at 07:05
1

Correct, that's what's going to happen. – Lasse V. Karlsen Jun 21 '12 at 10:12
@LasseV.Karlsen Hi. (after a while...) in your `3'rd` code sample : the first line doesnt actually fetch the data from db. sow why the next line can't just add the `where` clause ? ( so when enumerating the whole query will be sent to db) ? is this kind of behavior is because the first line uses `Ienumerable` or because the second line uses `Ienumerable` or because the `x.where` – Royi Namir Oct 11 '12 at 09:36
3

Sorry for the late reply. The reason is that the compiler no longer knows that the collection is one that can be queried. Meaning that since the collection has been stored into `IEnumerable`, the `IQueryable` operators are no longer available. As such, the last assignment in the answer will generate another lazy collection, but this collection will, when enumerated over, first fetch every customer from the database, and then filter in memory. – Lasse V. Karlsen Mar 03 '13 at 22:09

IEnumerable and IQueryable clarification?

1 Answers1

Linked