.NET Entity Framework - IEnumerable VS. IQueryable

Question

Please see this line of code. This is an invocation of a stored procedure, which returns an ObjectResult<long?>. In order to extract the long values I added the Select:

dbContext.FindCoursesWithKeywords(keywords).Select(l => l.Value);

Based on intellisense this Select returns IEnumerable<long>.

I'm not sure whether I read it somewhere or maybe just got used to this assumption - I always thought that when the EF API returns an IEnumerable (and not IQueryable) then this means that the results have been materialized. Meaning they've been pulled from the database.

I found out today that I was wrong (or maybe that's a bug?). I kept getting the error

"New transaction is not allowed because there are other threads running in the session"

Basically, this error tells you that you're trying to save changes while the db reader is still reading records.

Eventually I solved it by (what I considered a long shot) and added ToArray() call to materialize the IEnumerable<long>...

So - the bottom line - should I expect IEnumerable results from EF to contain results that haven't materialized yet? If yes then is there a way to know whether an IEnumerable has been materialized or not?

Thanks and apologies if this is one of those 'duhhh' questions... :)

I'm not sure if there's anything specifically documented for EF, but for Linq in general, an `IEnumerable` is no more "materialized" than an `IQueryable` - both are generally assumed to use deferred execution. — Damien_The_Unbeliever, Sep 30 '11 at 06:36
@Damien_The_Unbeliever: However, I think ObjectResult will always execute when the function is called... (in this case FindCoursesWithKeywords) — Edwin de Koning, Sep 30 '11 at 06:39

score 33 · Accepted Answer · answered Sep 30 '11 at 09:17

IQueryable is used when you are using Linq-to-entities = you are building declarative LINQ query in your application which will be interpreted by LINQ provider as SQL and executed on the server. Once the query is executed (iterated) it will turn to become IEnumerable and objects will be materialized as needed for iteration = not immediately.

Once you call stored procedure you are not using Linq-to-entities because there is no declarative query built in your application. The query / SQL already exists on database server and you are just invoking it. This will return IEnumerable but again it will not materialize all results immediately. Results will be materialized as iterated. This is principle of database cursor / or .NET data reader when you explicitly ask for fetching object.

So if you call something like this:

foreach (var keyword in dbContext.FindCoursesWithKeywords(keywords)
                                 .Select(l => l.Value))
{
    ...   
}

You are fetching courses one by one (btw. why to load whole course if you are interested only in keywords?). Until you complete or break the loop your data reader is opened to fetch records.

If you instead call this:

foreach (var keyword in dbContext.FindCoursesWithKeywords(keywords)
                                 .ToList() // or ToArray 
                                 .Select(l => l.Value))
{
    ...
}

You will force query to materialize all results immediately and loop will perform on collection in memory instead of opened database reader.

Difference between IEnumerable and IQueryable is not in the way how data are fetched because IQueryable is IEnumerable. The difference is in backing construct (something must implement these interfaces).

Hi, thanks for this explanation! So since this is a stored proc it won't return an IQueryable, as it doesn't support all the features that a query does, right? (btw, I'm fetching courses not keywords) :) — justabuzz, Sep 30 '11 at 12:17

CodesInChaos · Answer 2 · 2011-09-30T08:37:37.167

12

Working on an IEnumerable<T> means that all further operations will happen in C# code, i.e. linq-to-objects. It does not mean that the query has already executed.

Once you degrade to linq-to-objects all data left at this point needs to be fetched from the database and sent to .net. This can degrade performance drastically(For example database indexes won't be used by linq-to-objects), but on the other hand linq-to-objects is more flexible, since it can execute arbitrary C# code instead of being limited by what your linq provider can translate to SQL.

A IEnumerable<T> can be both a deferred query or already materialized data. The standard linq operators typically are deferred, and ToArray()/ToList() are always materialized.

edited Sep 30 '11 at 08:37

answered Sep 30 '11 at 07:38

CodesInChaos

106,488
23
218
262

Thanks for this distinction! So is that something I should be aware of? Are there any implications or gotchas? Cheers! – justabuzz Sep 30 '11 at 07:53
The most obvious gotcha is that degenerating to `IEnumerable` can drastically reduce performance. Imagine you degrade at the very beginning of the query, then it's likely that the whole table needs to be fetched instead of filtering using SQL on the server using indexes on the database for speedup. So at the point where you degrade to `IEnumerable` you want to have as little items remaining as possible. – CodesInChaos Sep 30 '11 at 08:39
Not too sure this is correct. Further reading I did + the other answer here say a similar thing - IEnumerable may also not materialize till later when you iterate it. If I understand that right this is because when you exec a stored proc you get a full IQueryable, because this isn't a full queryable object. Makes sense? :) – justabuzz Sep 30 '11 at 12:20
As I said the execution of `IEnumerable` may be deferred, but isn't required to be deferred. That depends on the class implementing it. But if you for example execute a `Where` clause on an `IEnumerable` even the elements that don't fulfill the condition need to be sent to C# and tested one by one. But I don't know what a stored procedure returns, since I've never used them. – CodesInChaos Sep 30 '11 at 12:35

score 0 · Answer 3 · answered Aug 04 '16 at 19:56

IEnumerable will not hydrate until materialized. If calling a stored procedure I would think that there is no further filter required, I mean you send parameters to a stored procedure to produce the desired subset of data returned. IEnumerable tied to a stored procedure is fine. However if you are fetching the entire contents of a table, then filtering in the application you should have a strategy. Like, do not ToList() the IEnumerable of the table, you will materialize all rows. Sometimes this will cause an out of memory exception. Besides why consume memory without reason. Use IQueryable against the context, that way you can filter the table at the Data Source, and not in the application. In answer, you have to materialize it. IEnumerable is an interface, only materializing it will "initialize" its type and produce something in my understanding.

score -5 · Answer 4 · answered Apr 19 '12 at 11:54

-5

IEnumerable : LINQ to Object and LINQ to XML.

IQueryable : LINQ to SQL

answered Apr 19 '12 at 11:54

Najeebullah Shah

4,164
4
35
49

IQueryable is a type that has not yet been executed. Basically it is the "query". http://msdn.microsoft.com/en-us/library/system.linq.iqueryable(v=vs.100).ASPX Virutally has nothing to do with LINQ to SQL, or Linq to Anything. It is an interface to provide access to any sort of data provider. – Tony Sep 22 '14 at 14:11

.NET Entity Framework - IEnumerable VS. IQueryable

4 Answers4

Linked