Re: Nested Linq Queries
"Wenig, Stefan" <[email protected]> Sat, 5 Sep 2009 23:32:50 +0200
| Newsgroups | gmane.comp.windows.dotnet.nhibernate.devel |
|---|---|
| Message-ID | <AE80EE02B149BA4BB0C88399E4E7056A2B17F788AB@at-vie-sun.int.rubicon-it.com> |
--===============3984882332709597655== Content-Language: en-US Content-Type: multipart/alternative; boundary="_000_AE80EE02B149BA4BB0C88399E4E7056A2B17F788ABatviesunintru_" --_000_AE80EE02B149BA4BB0C88399E4E7056A2B17F788ABatviesunintru_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This is the same dilemma we found ourselves in when we implemented eager fe= tching for re-store (re-motion's ORM). We went with Tuna's third option (ne= ither N+1 nor endlessly wide selects seemed too attractive to us, and we do= n't have fetch strategies in our mappings, or batching). The good news is that we implemented all of it in re-linq, only the FetchOn= e and FetchMany extension methods are in re-store (so any re-linq based pro= vider can decide to use our fetch syntax and strategy, their own or a mix o= f it). So if you want to look at the third option, I guess you're almost th= ere. Here's the re-store facade: https://svn.re-motion.org/svn/Remotion/trunk/Remotion/Data/DomainObjects/Li= nq/EagerFetchingExtensionMethods.cs And here's the re-linq meat: https://svn.re-motion.org/svn/Remotion/trunk/Remotion/Data/Linq/EagerFetchi= ng The basic idea is to transform this: (from o in orders where ... select o).FetchMany (o =3D> 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 quer= ies, there's not much more to do really. If it doesn't quite work out for this scenario, I guess you should have lit= tle 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 <[email protected]<mailto:[email protected]>= > wrote: > I believe 2nd is a better one, and in my opinion redundant data in this c= ase > 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=F6z > Eternal sunshine of the open source mind. > > http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.c= om/tehlike > > > > On Fri, Sep 4, 2009 at 8:41 PM, Steve Strong <[email protected]<mailto:s= [email protected]>> wrote: > > > Morning / Afternoon / Evening All > > > Quick question that I'd appreciate your opinions on. Given a query lik= e > > this: > > > var q =3D > > from o in db.Orders > > select new > > { > > o.OrderId, > > DiscountedProducts =3D > > from od in o.OrderLines > > where od.Discount > 0.0m > > select od, FreeShippingDiscount =3D > > o.Freight > > }; > > > I don't believe that it is possible to create exactly the shape that th= e > > 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 o= ut > > 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 nest= ing > > 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". Howev= er, > > there needs to be a default. Opinions on which one it should be, plus = any > > other alternatives, would be greatly appreciated. > > > Cheers, > > > Steve --_000_AE80EE02B149BA4BB0C88399E4E7056A2B17F788ABatviesunintru_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html dir=3D"ltr"><head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <style title=3D"owaParaStyle"><!--P { MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px } --></style> </head> <body ocsi=3D"x"> <p>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, an= d we don't have fetch strategies in our mappings, or batching).</p> <p><font face=3D"times new roman">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). </font>So if you want to look at the third option, I guess you're alm= ost there.</p> <p><font face=3D"times new roman"></font> </p> <p><font face=3D"times new roman">Here's the re-store facade: </font></p> <p><a href=3D"https://svn.re-motion.org/svn/Remotion/trunk/Remotion/Data/Do= mainObjects/Linq/EagerFetchingExtensionMethods.cs">https://svn.re-motion.or= g/svn/Remotion/trunk/Remotion/Data/DomainObjects/Linq/EagerFetchingExtensio= nMethods.cs</a></p> <p><font face=3D"times new roman">And here's the re-linq meat:</font></p> <p><font face=3D"times new roman"><a href=3D"https://svn.re-motion.org/svn/= Remotion/trunk/Remotion/Data/Linq/EagerFetching">https://svn.re-motion.org/= svn/Remotion/trunk/Remotion/Data/Linq/EagerFetching</a></font></p> <p><font face=3D"times new roman"></font> </p> <p><font face=3D"times new roman">The basic idea is to transform this:</fon= t></p> <p><font face=3D"times new roman">(from o in orders where ... select o).Fet= chMany (o =3D> o.OrderItems)</font></p> <p><font face=3D"times new roman">into the following two queries:</font></p= > <p><font face=3D"times new roman">from o in orders where ... select o</font= ></p> <p>from o in orders where ... from od in o.OrderItems select od</p> <p><font face=3D"times new roman">(plus an additional query for each ThenFe= tchMany/One call)</font></p> <p><font face=3D"times new roman"></font> </p> <p><font face=3D"times new roman">It all happens in the QueryModel, so if y= our provider can handle those queries, there's not much more to do really.<= /font></p> <p><font face=3D"times new roman"></font> </p> <p><font face=3D"times new roman">If it doesn't quite work out for this sce= nario, 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 vac= ation mode for the next two weeks :-)</font></p> <p><font face=3D"times new roman"></font> </p> <p><font face=3D"times new roman">Hope this helps,<br> Stefan (just arrived at the beautiful Gulf of Naples)</font></p> <p><font face=3D"times new roman"></font> </p> <p>On Sep 4, 8:21 pm, Tuna Toksoz <<a href=3D"mailto:[email protected]">= [email protected]</a>> wrote:<br> > I believe 2nd is a better one, and in my opinion redundant data in thi= s case<br> > is not important.<br> > another third possibility would be to execute 2 Queries, 1 for orders = and 1<br> > for OrderLines and do the shaping on the client.<br> > <br> > Tuna Toks=F6z<br> > Eternal sunshine of the open source mind.<br> > <br> > <a href=3D"http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comht= tp://twitter.com/tehlike"> http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.com= /tehlike</a><br> > <br> > <br> > <br> > On Fri, Sep 4, 2009 at 8:41 PM, Steve Strong <<a href=3D"mailto:srs= [email protected]">[email protected]</a>> wrote:<br> > <br> > > Morning / Afternoon / Evening All<br> > <br> > > Quick question that I'd appreciate your opinions on. Given = a query like<br> > > this:<br> > <br> > > = var q =3D<br> > > = from o in db.Orders<br> > > = select new<br> > > = &nb= sp; {<br> > > = &nb= sp; o.OrderId,<br> > > = &nb= sp; DiscountedProducts =3D<br> > > = &nb= sp; from = od in o.OrderLines<br> > > = &nb= sp; where= od.Discount > 0.0m<br> > > = &nb= sp; selec= t od, FreeShippingDiscount =3D<br> > > o.Freight<br> > > = &nb= sp; };<br> > <br> > > I don't believe that it is possible to create exactly the shape t= hat the<br> > > user wants using HQL, which leaves me with two options:<br> > <br> > > 1) Pull out the outer list in one query and the fill in the child= lists as<br> > > the user enumerates them<br> > <br> > > 2) Do a join across the parent & child entities and pull all = the data out<br> > > in one go, and then perform the shaping on the client.<br> > <br> > > Option 1 has the potential to be a N + 1 select, depending on= how many<br> > > items the user iterates through (I would assume that they are pro= bably going<br> > > to iterate all of them; if they only want a subset, then they sho= uld be<br> > > adding the appropriate where clause to the query). Obviousl= y the N + 1 gets<br> > > progressively worse the deeper the nesting becomes.<br> > <br> > > Option 2 will pull a wide data set with duplicated data in the pa= rent<br> > > columns, which again would get progressively worse as the level o= f nesting<br> > > increases. It does only hit the database once though.<br> > <br> > > Ultimately, I suspect there'll need to be some way of controlling= which<br> > > route is taken, since I don't think there is "one right answ= er". However,<br> > > there needs to be a default. Opinions on which one it shoul= d be, plus any<br> > > other alternatives, would be greatly appreciated.<br> > <br> > > Cheers,<br> > <br> > > Steve</p> <p><font face=3D"times new roman"></font> </p> </body> </html> --_000_AE80EE02B149BA4BB0C88399E4E7056A2B17F788ABatviesunintru_-- --===============3984882332709597655== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline ------------------------------------------------------------------------------ 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. http://p.sf.net/sfu/bobj-july --===============3984882332709597655== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Nhibernate-development mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/nhibernate-development --===============3984882332709597655==--