Error when trying to iterate through Queryable

Question

foreach(var name in _cust.Select(s => s.Username).Distinct())
{
    var x = _cust.Select(s => s.Username == name); //ERROR HERE
    //rest of the code here
}

will throw There is already an open DataReader associated with this Command which must be closed first. Exception, however it doesn't happen when I add .ToList().

I've search around so far but haven't found a satisfied answer. So my question is :

Is there any other way to do this beside adding .ToList()?
If I use .ToList() as a solution, will this cause a performance problem when it goes to production? Because AFAIK ToList() will try to load everything in the memory.
I have a lot of navigation properties in my Entities, will ToList() tried to load everything inside navigation properties?

Thanks

what is _cust ? Why do you bother making a `ToList()`, as you use a foreach (so you enumerate all your `UserName`s anyway ? — Raphaël Althaus, Aug 27 '15 at 09:42
_cust is a `DbSet`. Without `.ToList()`, it will throw exception `There is already an open DataReader associated with this Command which must be closed first.` See this http://stackoverflow.com/questions/4867602/entity-framework-there-is-already-an-open-datareader-associated-with-this-comma — tickwave, Aug 27 '15 at 09:43
1. I think the Error happens in the calculation of _cust. seems like the time of execution is important in this context (toList-> after Execution vs foreach() -> in this line) 2. ToList() will enumerate the query, foreach does the same. 3. this depends on your query, meaning: how long is your context open? Do you include anything? Do you use AsNoTracking()? — DevilSuichiro, Aug 27 '15 at 09:44
what is _cust? what query is behind it? as you know, any query will only be executed when the value is needed, meaning you have to calculate the value because you are using ToList(), foreach(), First() or Whatever. — DevilSuichiro, Aug 27 '15 at 09:47
@DevilSuichiro: _cust is `DbSet`. I simply select distinct `username` from Customer Repository. I believe the error isn't caused by calculation, but because there are multiple data retrieval commands executed on single connection, iterate through result of the query (IQueryable) will trigger lazy loading for loaded entity inside the iteration. — tickwave, Aug 27 '15 at 09:58
You surely have some connection open before the `foreach`. Not sure why `ToList()` works but if you do not have any open connectoin before `foreach` this foreach should also work without any ToList(). If you don't believe it, try making a totally clean project and test it with just the foreach. — Hopeless, Aug 27 '15 at 10:38
@Hopeless: Sorry I should've be more specific, please read my updated code block — tickwave, Aug 27 '15 at 10:43
@warheat1990 ah, then it's easier to understand. Your `foreach` still uses an open connection, while inside you have another `Select` which will attempt to use that same connection. That's not allowed. By using `ToList()` the connection will be closed first after returning all entries stored locally on client side. The inner `Select` then can use the connection OK without throwing exception. — Hopeless, Aug 27 '15 at 10:46

score 0 · Accepted Answer · answered Aug 27 '15 at 10:26

Is there any other way to do this beside adding .ToList()?

I guess enabling MARS in your connection string may be an alternative to ToList().

If I use .ToList() as a solution, will this cause a performance problem when it goes to production?

It depends on the number of distinct UserNames in your DataBase

Because AFAIK ToList() will try to load everything in the memory.

It will just try to load all distinct UserNames in the memory

I have a lot of navigation properties in my Entities, will ToList() tried to load everything inside navigation properties?

No, it will perform the appropriate query and make a projection on the UserName column.

IMHO, it is good practice to monitor generated queries in debug mode

Error when trying to iterate through Queryable

1 Answers1