Morning / Afternoon / Evening All
Quick question that I'd appreciate your opinions on. Given a query
like this:
var q =
from o in db.Orders
select new
{
o.OrderId,
DiscountedProducts =
from od in o.OrderLines
where od.Discount > 0.0m
select od, FreeShippingDiscount =
o.Freight
};
I don't believe that it is possible to create exactly the shape that
the user wants using HQL, which leaves me with two options:
1) Pull out the outer list in one query and the fill in the child
lists as the user enumerates them
2) Do a join across the parent & child entities and pull all the data
out in one go, and then perform the shaping on the client.
Option 1 has the potential to be a N + 1 select, depending on how many
items the user iterates through (I would assume that they are probably
going to iterate all of them; if they only want a subset, then they
should be adding the appropriate where clause to the query).
Obviously the N + 1 gets progressively worse the deeper the nesting
becomes.
Option 2 will pull a wide data set with duplicated data in the parent
columns, which again would get progressively worse as the level of
nesting increases. It does only hit the database once though.
Ultimately, I suspect there'll need to be some way of controlling
which route is taken, since I don't think there is "one right
answer". However, there needs to be a default. Opinions on which one
it should be, plus any other alternatives, would be greatly appreciated.
Cheers,
Steve
