Java Mailing List Archive

Home » the NHibernate development list »

Re: [NHibernate-development] Nested Linq Queries

Wenig, Stefan


Author LoginPost Reply

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:

And here's the re-linq meat:


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 <> 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.
> On Fri, Sep 4, 2009 at 8:41 PM, Steve Strong <> 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


Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.
Nhibernate-development mailing list
©2008 - Jax Systems, LLC, U.S.A.