0

Suppose I have an Order entity and an OrderItem entity, and that I'm trying to get the details (including items) of several orders at once.

I could get the Order and the OrderItem details together using .Include like so:

var orders = db.Orders
               .Include(o => o.OrderItem)
               .Where(o => orderIds.Contains(o.Id));

But, suppose that I can't use .Include because it doesn't work.

Instead, I could fetch all of the Orders, and then fetch all of the OrderItems, like this:

var orders = db.Orders.Where(o => orderIds.Contains(o.Id));

var orderItems = db.OrderItems.Where(i => orderIds.Contains(i.OrderId));

This will give me all the Orders and (separately) all the relevant OrderItems. But how do I "attach" the OrderItem objects to the relevant Order objects so that the navigation properties work correctly?

(In other words, how do I ensure that order.OrderItems and orderItem.Order work without causing EF to go fetch the data from the database rather than using what I've already retrieved).

Community
  • 1
  • 1
Gary McGill
  • 26,400
  • 25
  • 118
  • 202
  • Have you tried to switch of the proxy generation (incl. lazy loading) of EF and set the navigation proberties in your code? – boindiil Aug 08 '13 at 18:28
  • As long as the relationship is not many-to-many and change tracking isn't disabled it should happen automatically that all loaded `OrderItem`s are added to the navigation collections of the correct `Order`s (called "Relationship fixup"). Did you test it? Doesn't it work? – Slauma Aug 08 '13 at 18:30
  • @Slauma: thanks, it looks like it *is* working. What was confusing me is that examining the entities in the debugger was causing the data to be fetched afresh for some reason. If I avoid the debugger, but access the various members in code, then it seems not to go back to the database. Can you convert your comment into an answer so that I can accept & close the question? – Gary McGill Aug 09 '13 at 10:27
  • @Slauma: ...but wait - according to http://msdn.microsoft.com/en-US/data/jj691402 "if you have lazy loading enabled, accessing the posts property on one of the blog entities then EF will connect to the database to lazily load all posts, even though we have already loaded them all. This is because EF cannot know whether or not you have loaded all posts or if there are more in the database. If you want to avoid this then you will need to disable lazy loading". So presumably I'd have the same problem here. (My actual scenario isn't as simple as I've described, so it's hard for me to test). – Gary McGill Aug 09 '13 at 10:35
  • For EF5, what I am doing now is disable EF's lazy loading, and handle lazy loading where needed entirely from within my own project. IIRC, EF6 will provide the option to mark a navigation collection property as already loaded. Is upgrading to a prerelease version of EF an option for you? –  Aug 11 '13 at 11:45
  • @hvd: That's interesting about EF6, and sounds like just the sort of flexibility I'm looking for. I'll look into that. – Gary McGill Aug 12 '13 at 08:21

2 Answers2

0

About your last question - how do I ensure that order.OrderItems and orderItem.Order work without causing EF to go fetch the data from the database ... - you can first .Load() your queries into memory:

orders.Load();
orderItems.Load();

and then use them through Local property of your context:

var qo = db.Orders.Local.Where(...);
var qoi = db.OrderItems.Local.Where(...);
Amin Saqi
  • 18,549
  • 7
  • 50
  • 70
  • That doesn't answer the question: as already mentioned in the comments on the question, after that, `qo.First().OrderItems` will *still* go to the database if lazy loading is enabled. –  Aug 11 '13 at 11:49
  • @hvd - Of course it causes! He must only works with `Local` properties. Even `qo.First().OrderItems` does a database query. In fact, in-memory collections are store in `Local` properties and any query on any collection except `Local`s causes a db query... – Amin Saqi Aug 11 '13 at 11:56
  • `qo` was *your* `qo`, which is `db.Orders.Local.Where(...)`. Fully expanding that, I'm saying `db.Orders.Local.Where(...).First().OrderItems` will cause a database query to be executed. Even though it starts from `db.Orders.Local`. And a good answer should probably show how to avoid that. The question specifically includes "how do I ensure that order.OrderItems and orderItem.Order work without causing EF to go fetch the data [...]" –  Aug 11 '13 at 12:11
  • @hvd - I understand you but I just can't deliver my meaning... The data existing in Locals, has no problem. However if you're looking for some data which isn't present in Local, it indeed causes a new query to fetch those missing data... In fact, first of all, you should foresee what data should be in memory, then load them and use them... – Amin Saqi Aug 11 '13 at 12:19
  • The data *is* already available in memory, and if lazy loading is disabled, `order.OrderItems` already works exactly the way the OP intends. `order.OrderItems` will then only look at the locally retrieved order items: it will look at exactly those items that you could also get from `db.OrderItems.Local.Where(orderItem => orderItem.Order == order)`. Regardless, explaining why it doesn't work but not explaining how to make it work can't be sufficient. –  Aug 11 '13 at 12:24
  • @AminSaghi: Thanks for your answer - it helps me to know about "Local", which I wasn't aware of. However, as hvd points out - and you acknowledge, it doesn't help to make the objects behave as if they were loaded "normally". I guess what I'm looking for is some sort of way to tell EF "you have all the sub-items in this collection", which is a state/flag that it must use internally, I would imagine. – Gary McGill Aug 12 '13 at 08:20
0

Just to close out this question, here's what I did/learned:

1) As Slauma pointed out in his comment, even though the Orders and OrderItems are fetched separately, EF does actually fix up the navigation properties, so that for example each OrderItem's Order property correctly points to the correct Order.

2) Unfortunately, EF has no way of knowing that all of the OrderItems for the order have been fetched, and so if the Order.OrderItems navigation property is lazy-loaded, then accessing that property will cause EF to re-fetch all the OrderItem data from the database - and it'll do this once per Order. Clearly not good.

3) The only way to avoid the problem above seems to be to remove lazy-loading on that collection property (and other similar collections - one-to-one navigation properties are not affected by this issue).

Gary McGill
  • 26,400
  • 25
  • 118
  • 202