CODEWITHSUNDEEP
c#entity-frameworklinq-to-entitieslazy-loading
Charlie Salts
Charlie Salts

Reputation: 13488

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

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.

Upvotes: 1

Views: 1673

Answers (2)

Charlie Salts
Charlie Salts

Reputation: 13488

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838156

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

Upvotes: 5

Related Questions