Entity Framework exception when looping through entities (works when using ToList( ), though)

Question

I have a table of entities called MyItems, and each item in this table has a foreign key to another table. I'd like to simply loop through my items and access the string 'Name' property of each item's foreign key object, like so:

foreach (var myItem in (from q in context.MyItems select q))
{
   string testName = myItem.ForeignItem.Name
}

When I do this, ForeignItem is null, and I get an InvalidOperationException when I attempt to access ForeignItem:

There is already an open DataReader associated with this Command which must be closed first.

However - if I instead call ToList() like so:

(from q in context.MyItems select q).ToList()

my foreign key objects populate just fine. I realise that ToList() is going to be expensive, since the whole table is going to be retrieved at once. I suspect that something is going wrong behind the scenes with lazy loading, but I'm working with the understanding that it should work without calling ToList().

Have I overlooked something?

Edit: I was thinking perhaps the use of var was causing problems, so I tried using

foreach (MyItem myItem in (from q in context.MyItems select q))
{
   // loop contents
}

instead - same results. The properties of myItem are populated except for the foreign key objects, which remain null.

Edit #2: I only ever use one object context, and one Linq-to-Entities statement in my entire application, so I'm at a loss to explain where this other DataReader is operating. I should also note that this error happens for the first object in the loop, so it's not as a result of a previous item being retrieved.

Is ForeignItem.Name something that needs to be eager loaded? — Brian
– Brian, Commented Aug 8, 2012 at 18:40
have you tried putting myItem.ForeignItem.Load(); before you actually try to fetch the ForeignItem.Name value ? — Thousand
– Thousand, Commented Aug 8, 2012 at 18:43
@Brian It should be loaded, period. Why it works when I call ToList() but not when I don't is the big question. — Charlie Salts
– Charlie Salts, Commented Aug 8, 2012 at 18:43
@Jane Doe Even if ForeignItem exposed such a method, ForeignItem is null, so I'd be calling an instance method on a null object. — Charlie Salts
– Charlie Salts, Commented Aug 8, 2012 at 18:45
so myItem.ForeignItem doesnt expose .Load(); or .LoadForeignItemReference()? — Thousand
– Thousand, Commented Aug 8, 2012 at 18:48

Community · Accepted Answer · 2017-05-23 10:34:39Z

5

You wrote myItem instead of q. You should use this:

(from q in context.MyItems select q)

Or more simply, you can just write this:

context.MyItems

Regarding your updated question, it seems to be because you start a new query before the first has finished executing. Possible solutions:

Include the child items when you run the original query.

foreach (var myItem in context.MyItems.Include("ForeignItem")) { ... }

Enable MultipleActiveResultSets.

Related

Entity Framework: There is already an open DataReader associated with this Command

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Aug 8, 2012 at 18:28

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Charlie Salts Over a year ago

I was sanitizing my object names for SO, the typo was irrelevant to the problem. I've updated the code in the question.

Major Productions Over a year ago

Isn't there also an issue of delayed execution? IIRC, LINQ queries defer their execution until they're acted upon via something like a ToList/ToArray. Would a ForEach force execution?

Servy Over a year ago

@kevinmajor1 Yes, foreach will force execution.

Charlie Salts Over a year ago

Thanks for updating your answer. Include() seems to accept string parameters only. I suspect that that code is not correct. I'll try using the type name as specified in the relevant MSDN article.

Charlie Salts · Accepted Answer · 2012-08-08 19:23:15Z

0

Here's what I ended up doing, as per Mark's suggestion to use Include( ):

(from q in context.MyItems.Include("ForeignItemOne").Include("ForeignItemTwo").Include("ForeignItemThree") select q)

Incidently, setting MultipleActiveResultSets to true also works. For now, I'll use Include() since the application is small and only has one Linq statement in it.

answered Aug 8, 2012 at 19:23

Charlie Salts

13.6k8 gold badges54 silver badges80 bronze badges

Collectives™ on Stack Overflow

Entity Framework exception when looping through entities (works when using ToList( ), though)

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related