.NET Entity Framework - IEnumerable VS. IQueryable

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... :)

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.

`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).

IEnumerable<T> : LINQ to Object and LINQ to XML.


IQueryable<T> : LINQ to SQL

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.