Going direct from Linq to Sql would have the same issue for this
scenario. So far, the HQL route has worked out ok - I've got quite a
number of queries working just fine.
On 4 Sep 2009, at 20:02, Fabio Maulo wrote:
How should look the better HQL to do the same ?
mmmm I'm starting thinking that even LINQ->HQL-AST->SQL is not a
real good idea.
Perhaps we should go to LINQ->SQL (using session factory of course).
2009/9/4 Steve Strong <[email protected]>
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
--
Fabio Maulo
