This is the same dilemma we found ourselves in when we implemented eager fetching for re-store (re-motion's ORM). We went with Tuna's third option (neither N+1 nor endlessly wide selects seemed too attractive to us, and we don't have fetch strategies in
our mappings, or batching).
The good news is that we implemented all of it in re-linq, only the FetchOne and FetchMany extension methods are in re-store (so any re-linq based provider can decide to use our fetch syntax and strategy, their own or a mix of
it). So if you want to look at the third option, I guess you're almost there.
Here's the re-store facade:
https://svn.re-motion.org/svn/Remotion/trunk/Remotion/Data/DomainObjects/Linq/EagerFetchingExtensionMethods.cs
And here's the re-linq meat:
https://svn.re-motion.org/svn/Remotion/trunk/Remotion/Data/Linq/EagerFetching
The basic idea is to transform this:
(from o in orders where ... select o).FetchMany (o => o.OrderItems)
into the following two queries:
from o in orders where ... select o
from o in orders where ... from od in o.OrderItems select od
(plus an additional query for each ThenFetchMany/One call)
It all happens in the QueryModel, so if your provider can handle those queries, there's not much more to do really.
If it doesn't quite work out for this scenario, I guess you should have little trouble tweaking it for what you need. However, I can't be bothered to look into it any further since I'm in vacation mode for the next two weeks
Hope this helps,
Stefan (just arrived at the beautiful Gulf of Naples)
On Sep 4, 8:21 pm, Tuna Toksoz <tehl... <at> gmail.com> wrote:
> I believe 2nd is a better one, and in my opinion redundant data in this case
> is not important.
> another third possibility would be to execute 2 Queries, 1 for orders and 1
> for OrderLines and do the shaping on the client.
>
> Tuna Toksöz
> Eternal sunshine of the open source mind.
>
>
http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.com/tehlike
>
>
>
> On Fri, Sep 4, 2009 at 8:41 PM, Steve Strong <srstr... <at> gmail.com> wrote:
>
> > 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
RSS Feed