@boris I have tried what you said and it doesn't work, at all. 1) When I inner join the collections arn't loaded into the lists, it just ignores the data 2) When I run those 3 queries in a futures, it also does nothing. It populates no lists except for B inside A.
On Feb 8, 6:13 am, Boris Drajer <[email protected]> wrote: > Hi Brad, > > (Sorry for the late answer) > > You don't have to do a full cartesian product and you don't really > have to use left join fetch... You can do something like > > from A inner join fetch A.B where A = something > from B inner join fetch B.C where B.A = something > from C inner join fetch C.D where C.B.A = something > > If you execute each of the three queries separately, you'l retrieve B > + C + D number of rows and still have all collections loaded. > > The difference between left join fetch and inner join fetch is that > empty collections don't get initialized with inner, while they do with > left. For example, with > > from A inner join fetch A.B where A = something > > A's with no B's don't appear in query results at all and their A.B > collections remain uninitialized, which produces the lazy > initialization exception when they are accessed. If this isn't a > problem for you, then inner join is indeed a better solution. > > Best regards! > > On 2 феб, 17:32, Brad Laney <[email protected]> wrote: > > > > > > > > > Hey, > > > I understand that is how NH works, but you need to understand I don't > > want to do a Cartesian join for this. > > > I have A -> B -> C -> D > > > If I want D populated then i will be selecting the entire contents of > > A, like, 40 times. > > > I understand it needs to know the link. And I understand I can use > > futures to efficiently populate one level deep. > > What I want is an efficient way to populate 4 levels deep, just like I > > could with TSQL then join them together at the end. > > > I do not care the method, or technique to doing this. I just need an > > object so that my DBA does not tell me I am bringing down the server > > by having a high visited page run 500 queries every time it runs. > > > Doing this is not an acceptable solution, specially since it requires > > left outer join: > > > from A left outer join fetch A.B left outer join fetch B.C left outer > > join fetch C.D > > > On Feb 2, 6:15 am, Boris Drajer <[email protected]> wrote: > > > > Hi, > > > > Sorry if I misunderstand, but it seems to me that the crucial point > > > you're missing is that it's not enough to load the contents of the > > > collections, NHibernate needs to load the collection as a part of its > > > owner. If you retrieve individual objects, they will all be present in > > > the session but the collections that contain them will remain > > > uninitialized. When you access such collections, NHibernate executes a > > > query and then for each row retrieved discovers that it's already > > > cached in the session, so it only adds it to the collection. What you > > > need to do is to load them *together*. This is best illustrated with > > > HQL: > > > > from A a left join fetch a.Bs where [something] > > > > This loads the A's along with their collections of B's, not just A's > > > and B's separately. > > > > With QueryOver, I think it would look something like this: > > > > var q = session.QueryOver<A>().Fetch(a => > > > a.Bs).Eager.Where([something]); > > > > You can execute multiple different queries of this sort to load > > > different portions of the object graph. > > > > HTH! > > > > On 30 јан, 21:54, Brad Laney <[email protected]> wrote: > > > > > This relationship goes 4 deep. When it does lazy loading it ends up > > > > running like 500+ queries. > > > > This job can easily be done in 4 queries, and then the content > > > > aggregated. > > > > I have not touched batch size, because its already set, and the number > > > > of records coming back is not the issue. > > > > I have tried fetch but then you end up returning large amounts of > > > > unnecessary data, which is why I want to use futures. > > > > This is supposed work. It's documented as how it works. I just don't > > > > know how to get it to do join type select with QueryOver. > > > > I'm going to try it with the criteria API. > > > > > .SetFetchMode("Items", FetchMode.Select) <-- this is pretty much what > > > > I want. > > > > > What is subselect fetching? > > > > > Is there a way to fetch an entire list of data, instead of one at a > > > > time? > > > > x.Items.Items <-- I want to fill every single item inside that > > > > collection in one sql call instead of it lazy loading each one that I > > > > want. > > > > > What I want it to run is: > > > > > select A.* from A where A.id = ? > > > > select B.* from B where B.aid = ? > > > > select C.* from C left join B where B.aid = ? > > > > select D.* from D left join C left join B where b.aid = ? > > > > > I don't mind joining up to A instead of B. But yeah. Also notice I > > > > don't want every single column, because that's pointless. > > > > > On Jan 30, 1:44 pm, Oskar Berggren <[email protected]> wrote: > > > > > > 2012/1/30 Brad Laney <[email protected]>: > > > > > > > Hello. > > > > > > > I am trying to avoid N+1 and cartesian product when I need to do > > > > > > deep > > > > > > eager loads. I want to do this for performance. It is an EAV type > > > > > > of a > > > > > > data structure. > > > > > > Is the problem too many queries, or that you absolutely cannot have > > > > > more than one roundtrip to the database? > > > > > > [...] > > > > > > > var r = s.QueryOver<NHMapTest1>() > > > > > > .Where(x => x.Id == id) > > > > > > .Future(); > > > > > > > s.QueryOver<NHMapTest2>() > > > > > > .Where(x => x.Parent.Id == id) > > > > > > .Future(); > > > > > > Running r.Single(); returns the entity, but if you do .Items it'll > > > > > > query the DB, even tho they were selected in the 2nd future. Both > > > > > > queries run correctly and return valid results. > > > > > > When you access the collection, even though a number of objects of the > > > > > collection's member type has been loaded, NH doesn't know they are > > > > > part of the collection, that's why it needs to query again. Looking at > > > > > the child's Parent reference of loaded objects wouldn't be enough, > > > > > since it doesn't know if those are _all_ the children. > > > > > > Have you set proper batch size on your collections and classes? And > > > > > possibly specify subselect fetching for the collections. With that you > > > > > should be able to rely on lazy fetch, but with a limited number of > > > > > round trips. > > > > > > /Oskar -- 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.
