Nested Linq Queries

[Steve Strong]

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]

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 <srst...@gmail.com>

--
Fabio Maulo

[Steve Strong]

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. 

[Tuna Toksoz]

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_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike

[Richard Brown]

In the case of option 1, where does the lazy-query get stored?  And does the fact that the user could enumerate potentially 'much' later cause any grief?

Sounds easier to issue 1 query with a join to me - but I think I'd vote ultimately for what's simplest/easiest.

Sent from my Android phone.

On Sep 4, 2009 7:21 PM, "Tuna Toksoz" <teh...@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_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike

On Fri, Sep 4, 2009 at 8:41 PM, Steve Strong <srst...@gmail.com> wrote: > > > Morning / Aftern...

[Chad Lee]

Shouldn't the fetch strategy in the mapping file determine how it pulls that data?

[Fabio Maulo]

2009/9/4 Chad Lee <chad...@gmail.com>

Shouldn't the fetch strategy in the mapping file determine how it pulls that data?

No.

--
Fabio Maulo

[Chad Lee]

No.

why not?

[Steve Strong]

Because the user is effectively just creating a DTO, we don't really have any mapping that's useful.  The way they want to fetch for one DTO may be different to the next.  Long term, I think we'll need to support both options with some way of the user indicating which they want.  But for now, I only want to write one of them :)

[Steve Strong]

2 queries could certainly be an option; I'll take a look to see how that would fit in

[Fabio Maulo]

select o.id, l,  o.Freight from Order o join fetch o.Lines l where l.Discount > 0

Then, it should need a client side part 

2009/9/4 Steve Strong <srst...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

2009/9/4 Fabio Maulo <fabio...@gmail.com>

select o.id, l,  o.Freight from Order o join fetch o.Lines l where l.Discount > 0

Then, it should need a client side part 

The client side part should be a new special ResultTrasformer

--
Fabio Maulo

[Steve Strong]

That was exactly my plan.  Good to know that I'm not going mad :)

[Tuna Toksoz]

You're both mad.

Tuna Toksöz
Eternal sunshine of the open source mind.

http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike

[Fabio Maulo]

2009/9/4 Steve Strong <srst...@gmail.com>

That was exactly my plan.  Good to know that I'm not going mad :)

Yes but... autodiscover the client side part in each scenario is not trivial especially with "reports" style queries (DTO).

--
Fabio Maulo

[Steve Strong]

Why thank you :)

[Ayende Rahien]

I think that option 1 with batch-size would be the easiest and the most performant as the general case.

[Stefan Wenig]

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 of a second select (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 that 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 individually, 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:52 pm, Tuna Toksoz <tehl...@gmail.com> wrote:
> You're both mad.
>
> 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 9:51 PM, Steve Strong <srstr...@gmail.com> wrote:
> > That was exactly my plan.  Good to know that I'm not going mad :)
>
> > On 4 Sep 2009, at 20:39, Fabio Maulo wrote:
>

> > 2009/9/4 Fabio Maulo <fabioma...@gmail.com>

>
> >> select o.id, l,  o.Freight from Order o join fetch o.Lines l where
> >> l.Discount > 0
> >> Then, it should need a client side part
>
> >  The client side part should be a new special ResultTrasformer
> > --

> > Fabio Maulo- Hide quoted text -
>
> - Show quoted text -