1

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.

Charlie Salts
  • 13,109
  • 7
  • 49
  • 78
  • Is ForeignItem.Name something that needs to be eager loaded? – Brian Aug 08 '12 at 18:40
  • have you tried putting myItem.ForeignItem.Load(); before you actually try to fetch the ForeignItem.Name value ? – Thousand Aug 08 '12 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 Aug 08 '12 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 Aug 08 '12 at 18:45
  • so myItem.ForeignItem doesnt expose .Load(); or .LoadForeignItemReference()? – Thousand Aug 08 '12 at 18:48
  • @Jane Doe No, `ForeignItem` inherits from [`EntityObject`](http://msdn.microsoft.com/en-us/library/system.data.objects.dataclasses.entityobject.aspx). – Charlie Salts Aug 08 '12 at 18:51
  • the mechanics behind ToList are different than just doing a foreach loop. Just because it eager loads in a ToList doesn't mean it will eager load in other usages. – Brian Aug 09 '12 at 13:24

2 Answers2

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

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • I was sanitizing my object names for SO, the typo was irrelevant to the problem. I've updated the code in the question. – Charlie Salts Aug 08 '12 at 18:31
  • 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? – Major Productions Aug 08 '12 at 18:32
  • 1
    @kevinmajor1 Yes, `foreach` will force execution. – Servy Aug 08 '12 at 18:37
  • 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](http://msdn.microsoft.com/en-us/library/bb738708.aspx). – Charlie Salts Aug 08 '12 at 19:02
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.

Charlie Salts
  • 13,109
  • 7
  • 49
  • 78