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

Solution 1

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

Solution 2

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.