Nested Include With Sort

Question

I have a data structure that looks something like this:

main(main_id, name)
container(container_id), order, name)
item(item_id, name)
item_order(item_order_id, order, item_id)

• There is a many to one relationship between container and main
• There is a many to one relationship between item_order and container
• There is a one to one relationship between item_order and item (the same item can show up in multiple containers, which is why the ordering information is stored in a separate table rather than the same one like container)

What I want to do for a specific main is select all the containers in ascending order, along with all the items (through their order objects) in ascending order.

I have tried The Example I Found Here, and it works great for sorting the containers, what I cannot figure out is how to expand this to sorting the items within the containers.

Does anyone have any idea how to accomplish this?

Answer 1

You can't. EF materializes nested collections in the order they happen to come from the database. We can't do a thing like context.containers.Include(c => c.Items.OrderBy(...)).

It's been asked before whether the Include method could have an OrderBy, but to this day it hasn't. It's occasionally a pain, but I don't expect this to change any time soon. There are some alternatives:

• Order the items when necessary (e.g. for display)

• Project the containers and items to a view model in which the items are sorted.

• Create a (unmapped) property like SortedItems in container. But note that this property can only be addressed when the objects have been materialized, a query like context.containers.Select(c => c.SortedItems) would throw an error.