EF navigation property doesn't load

Question

I'm trying to access the Item navigation property of my OrderDetail. But when I'm trying to run it, I get the InvalidOperationException with the following error: There is already an open DataReader associated with this Command which must be closed first.. The weird thing is when I'm running a step-by-step debug, everything is fine, I can see the items and the page is rendered with no problem whatsoever if and only if I'm looking into the orderDetails down to the specific OrderDetail prior to getting the item from the orderDetails collection via foreach.

var orderDetails = from o in db.Orders
                   join od in db.OrderDetails
                   on o.Id equals od.OrderId
                   where o.CustomerId == CustomerId
                   select od;

var availableItems = new List<ViewModel.Data.SelectItemEditorViewModel>();

foreach (var od in orderDetails)
{
    var editorViewModel = new ViewModel.Data.SelectItemEditorViewModel();
    editorViewModel.Selected = false;
    editorViewModel.BaseItem = od.Item; //Exception is thrown when trying to access od.Item., but if I expand orderDetails in debug mode down to the specific orderDetail before entering the foreach loop, everything goes fine.

    availableItems.Add(editorViewModel);
}

I've trying to add an Include(so the second line of LINQ would be join od in db.OrderDetails.Include(od => od.Item)), but with no luck. What am I doing wrong?

DrewJordan · Accepted Answer · 2015-02-11T02:09:27.893

0

Change your query to this:

db.OrderDetails.Include(od => od.Item) // use Include to eager load navigation property
                    .Join(db.Orders, // join another table
                    od => od.OrderId, // using this foreign key
                    o => o.Id, // and this primary key
                    (od, o) => new {OrderDetail = od, Order = o}) // project the result of the join into new objects
                    .Select(x => x.OrderDetails).ToList(); // select the one you want and enumerate it immediately

I'm not sure the exact equivalent in Linq, but this should eager-load the Item property for you.

I'm guessing you're calling the dbContext somewhere inside your SelectItemEditorViewModel. EF defers execution whenever it can; it will only return the smallest possible dataset that can allow it to do the work you want it to do (hence, all of the Include()s to force eager loading). Calling ToList() or ToArray() forces it to load the whole set into memory when it's called, instead of one row at a time in your foreach. Doing that allows the reader to open and close before anything else tries to access it.

edited Feb 11 '15 at 02:09

answered Feb 10 '15 at 21:43

DrewJordan

5,266
1
25
39

It says that in line (od, o) => new {OrderDetails od, Order o}) the OrderDetails and Order are types but used as variables? Thanks for pointing on the mistake about Seat. – Jyrkka Feb 10 '15 at 21:51
oh, right. I fixed that; it's because you need to create the actual objects (Order Detail and Order) instead of referencing the context entities (OrderDetails and Orders). – DrewJordan Feb 10 '15 at 22:03
I don't have an OrderDetail it's called OrderDetails, and though I think it should be changed, but still it says the same thing for some reason. – Jyrkka Feb 10 '15 at 22:06
oh god sorry... simple mistake. need to assign them, like `OrderDetails = od, Orders = o`. my code was missing the '=' – DrewJordan Feb 10 '15 at 22:11
Great! now it compiles, but fails in the same place with same exception :( – Jyrkka Feb 10 '15 at 22:17
2

Rereading your original post, I'm guessing that you're using the variable `db` somewhere else in your code without disposing it... see this post: http://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c and try setting `MultipleActiveResultSets=true` in your connection string, or keeping your `db` variable in a tightly scoped `using` statement – DrewJordan Feb 10 '15 at 22:23
1

yes, that's exactly what your problem is. It works if you're debugging every single item because you've expanded it... you can do the same thing by adding a call to `ToList()` or `ToArray()` at the end of your Linq query. – DrewJordan Feb 10 '15 at 22:26
Bingo! ToList() has solved the problem. Yes, in the same method I'm getting the customer using the same db context `var customers = from c in db.Customers select c; var customer = customers.Where(c => c.Id.Equals(CustomerId)).FirstOrDefault();` should I dispose somehow the variable that is used to store the query? But I'm not in iteration over it, I don't really understand what is the problem... – Jyrkka Feb 10 '15 at 22:38
@Jyrkka I updated my answer, please select it when you can. I'm not sure without seeing ALL of your code where exactly the issue is coming from, but a good practice is to wrap any calls to the dbContext in a small using block, like `using (var db = new dbContext()) { // do stuff }` and keep the scope as limited as possible (get the results, write the ones you need into memory, and then go back out of the block. That should help keep the readers separated. – DrewJordan Feb 11 '15 at 02:13
My db context variable is defined as private member of the class, like I've seen in examples. Is this also considered a bad practice? – Jyrkka Feb 11 '15 at 10:04
1

Not necessarily; as often is the case, the answer is a mushy 'it dedepnds'. You can read some more at http://mehdi.me/ambient-dbcontext-in-ef6/ and http://blog.jongallant.com/2012/10/do-i-have-to-call-dispose-on-dbcontext.html#.VNtbePnF-mE, among plenty of other places. Basically, EF will manage the connections for you, but I think the short answer is yes, it's considered a best practice to use a `using` block. If you don't however, you should be fine. – DrewJordan Feb 11 '15 at 13:44

EF navigation property doesn't load

1 Answers1