I am not asking about how to load the entire tree in one round trip. I've already investigated that issue, and it is impossible for my database (CTEs are not supported by SQL Compact).
What I'm asking is: what code can I write against the NH API that will retrieve the entire Category tree AS IF I had lazy="false" on the SubCategories mapping. Maybe you are saying that a single "query" will not be sufficient to retrieve the entire tree. If that's right, then I didn't realize that one HQL query corresponds to exactly one SQL query. In any case, if you could just tell me what code to write against the NH API that would give me the desired effect, I would be very much appreciative. If I'm missing something obvious, then "kid speak" may be all I need. On Aug 3, 5:23 pm, Fabio Maulo <[email protected]> wrote: > HappyNomad,What NH do with lazy=false is: > load- investigate-load > load- investigate-load > load- investigate-load > load- investigate-load > So it run many SQL with many roundtrips > > What you are asking is "eagerly load recursive relation" that mean load > the entirely three in one roundtrip. > > The question "Can you write a SQL to eagerly load recursive relation ?" is > an invitation to think. > > 2009/8/3 HappyNomad <[email protected]> > > > > > > > The SQL to eagerly load the recursive relation would be the same as > > what NH generates if I were to add lazy="true" onto the > > SubCategories's list mapping in my OP, and then execute this query: > > string s = "from Category as t where t.ParentCategory is null"; > > session.CreateQuery( s ).List<Category>(); > > > Here is the generated SQL: > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.parentcategory...@p0;@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0);@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0));@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0)));@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0))));@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0)))));@p0 = 1 > > > NHibernate: SELECT subcategories0_.ParentCategoryID as ParentCa4_1_, > > subcategories0_.ID as ID1_, subcategories0_.PositionInList as > > Position5_1_, subcategories0_.ID as ID2_0_, > > subcategories0_.ParentCategoryID as ParentCa4_2_0_ FROM Category > > subcategories0_ WHERE subcategories0_.ParentCategoryID in (select > > subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE subcategories0_.ParentCategoryID in > > (select subcategories0_.ID FROM Category subcategories0_ WHERE > > subcategories0_.ParentCategoryID in (select subcategories0_.ID FROM > > Category subcategories0_ WHERE > > subcategories0_.parentcategory...@p0))))));@p0 = 1 > > > But I want the SubCategories list to load lazily by default. > > > On Aug 3, 4:48 pm, Fabio Maulo <[email protected]> wrote: > > > 2009/8/3 HappyNomad <[email protected]> > > > > > Really sorry, but I'm still drawing a blank. If you provide some > > > > sample code then it would be easier for me to understand your > > > > suggestion. > > > > The "sample" is exactly what I'm asking you. > > > Can you write a SQL to eagerly load recursive relation ? > > > > -- > > > Fabio Maulo > > -- > Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
