I'm looking at projections now, I'm not familiar with them but it looks like I'll do a CreateCriteria for every inner join I want, is that correct? It also looks like it wont produce a very efficient query, I'd start with Score and to BaseGroup which is the opposite of the SQL query I'd write.
I had read about creating a SQL view and linking NHibernate to it, maybe that's what I should do instead? On Sep 20, 2:54 pm, José F. Romaniello <[email protected]> wrote: > you could do this in hql: > > from BaseGroup bg > inner join fetch bg.Specialities s > inner join fetch s.Measurements > > and then apply a DistinctRootTransformer. (I am not sure if it will work.) > > But just think what will happen if you have 100 Foo,, and each Foo has 10 > Bar and each Bar has 10 Baz.... You will have 100 x 10 x 10 records coming > from the db and then apply a distinct filter.... > > It doesn't make sense.. You can use others mechanism as batch size... or you > can do something better, write a projection and fetch the data into a DTO > with the important data. > > 2010/9/20 Scott <[email protected]> > > > > > Yeah, I want to use Linq, I already know exactly how to do it in SQL. > > > SELECT s.* FROM BaseGroup bg > > INNER JOIN BaseGroupSpecialty bgs ON bg.RefId = bgs.BaseGroupRefId > > INNER JOIN MeasurementSpecialty ms ON bgs.MeasuremenRefId = > > ms.MeasuremenRefId > > INNER JOIN BaseGroupMember bgm ON bg.RefId = bgm.BaseGroupRefId > > INNER JOIN Score s ON bgm.MemberRefId = s.MemberRefId AND > > ms.MeasurementRefId = s.MeasurementRefId > > WHERE br.RefId = '<Suplied RefId>' > > > The thing I'm not figuring out how to do is how to break off into two > > different table trees and then use all the criteria at the end to > > narrow down the Scores. > > > Using SQL is my last resort to make it work but I'd really rather not > > have any SQL which is why I asked about HQL if I can't do it in Linq. > > > On Sep 20, 12:19 pm, Fabio Maulo <[email protected]> wrote: > > > José, > > > Note: he is using the Linq provider of contrib > > > > On Mon, Sep 20, 2010 at 2:07 PM, José F. Romaniello > > > <[email protected]>wrote: > > > > > and you want something like > > > > > select ... > > > > from Foo > > > > inner join Bar > > > > inner join Baz > > > > > ? > > > > > 2010/9/20 Scott <[email protected]> > > > > > I think yes. > > > > >> On Sep 20, 9:58 am, José F. Romaniello <[email protected]> > > wrote: > > > >> > Let me try to understand your scenario, you have something like "Foo > > has > > > >> a > > > >> > collection of Bar, an each Bar has a collection of Baz?" and you > > want to > > > >> > eager load the entire graph of all Foos? > > > > >> > 2010/9/20 Scott <[email protected]> > > > > >> > > But really, I need to find a solution to this. If I upgrade to v3 > > is > > > >> > > there any way to do this? Should I use HQL? Or am I going to have > > to > > > >> > > fall back on running a stored procedure? > > > > >> > > On Sep 19, 5:53 pm, Fabio Maulo <[email protected]> wrote: > > > >> > > > LOL!! > > > > >> > > > On Sun, Sep 19, 2010 at 7:35 PM, Scott < > > [email protected]> > > > >> > > wrote: > > > >> > > > > That's what I was thinking I'd have to do. > > > > >> > > > > On Sep 19, 4:41 pm, Fabio Maulo <[email protected]> wrote: > > > >> > > > > > On Sun, Sep 19, 2010 at 4:19 PM, Scott < > > > >> [email protected]> > > > >> > > > > wrote: > > > >> > > > > > > Can anyone give me an idea on where to go with this? > > > > >> > > > > > To the church to pray ? > > > > >> > > > > > > On Sep 17, 9:33 am, Scott <[email protected]> > > wrote: > > > >> > > > > > > > I'm using version 2.1.2 with .NET 3.5 I was under the > > > >> impression > > > >> > > that > > > >> > > > > > > > v3 is still beta. > > > > >> > > > > > > > The structure gets pretty big, that's why I tried to > > make it > > > >> more > > > >> > > > > > > > generic and simple and I think if I could figure out how > > to > > > >> get > > > >> > > the > > > >> > > > > > > > intersection of two collections of any type > > (many-to-many, > > > >> > > one-to- > > > >> > > > > > > > many, etc) its just a matter of repeating it down my > > tree. > > > > >> > > > > > > > Here's an example of a situation: > > > >> > > > > > > > I have a BaseGroup which contains Specialties and > > Members, > > > >> > > > > Specialties > > > >> > > > > > > > have Measurements assigned to them. The Members are > > Scored > > > >> by the > > > >> > > > > > > > Measurements. I'm trying to get a list of all the Scores > > > >> where > > > >> > > the > > > >> > > > > > > > BaseGroup Specialties and the BaseGroup Members > > intersect. > > > > >> > > > > > > > The hbm.xml: > > > > >> > > > > > > > <class name="Models.BaseGroup, Project" > > table="BaseGroup"> > > > >> > > > > > > > <id name="RefId" type="guid"> > > > >> > > > > > > > <generator class="guid.comb" /> > > > >> > > > > > > > </id> > > > >> > > > > > > > <version name="Version" type="Int32" > > generated="never"> > > > >> > > > > > > > <column name="Version" not-null="true" /> > > > >> > > > > > > > </version> > > > >> > > > > > > > <property name="Title" column="Title" type="string" > > > >> > > not-null="true" > > > >> > > > > / > > > > >> > > > > > > > <bag name="Specialties" lazy="true" > > > >> table="BaseGroupSpecialty"> > > > >> > > > > > > > <key column="BaseGroupRefId" /> > > > >> > > > > > > > <many-to-many column="SpecialtyRefId" > > > >> > > class="Models.Specialty, > > > >> > > > > > > > Project" order-by="Title" /> > > > >> > > > > > > > </bag> > > > > >> > > > > > > > <bag name="Members" lazy="true" > > table="BaseGroupMember"> > > > >> > > > > > > > <key column="BaseGroupRefId" /> > > > >> > > > > > > > <many-to-many column="MemberRefId" > > class="Models.Member, > > > >> > > Project" > > > >> > > > > > > > order-by="Title" /> > > > >> > > > > > > > </bag> > > > >> > > > > > > > </class> > > > > >> > > > > > > > <class name="Models.Specialty, Project" > > table="Specialty"> > > > >> > > > > > > > <!--omitted RefId and version, all same as > > BaseGroup--> > > > >> > > > > > > > <property name="Title" column="Title" type="string" > > > >> > > not-null="true" > > > >> > > > > / > > > > >> > > > > > > > <bag name="BaseGroups" lazy="true" > > > >> table="BaseGroupSpecialty"> > > > >> > > > > > > > <key column="SpecialtyRefId" /> > > > >> > > > > > > > <many-to-many column="BaseGroupRefId" > > > >> > > class="Models.BaseGroup, > > > >> > > > > > > > Project" order-by="Title" /> > > > >> > > > > > > > </bag> > > > > >> > > > > > > > <bag name="Measurements" lazy="true" > > > >> > > table="MeasurementSpecialty"> > > > >> > > > > > > > <key column="SpecialtyRefId" /> > > > >> > > > > > > > <many-to-many column="MeasurementRefId" > > > >> > > > > class="Models.Measurement, > > > >> > > > > > > > Project" order-by="Title" /> > > > >> > > > > > > > </bag> > > > >> > > > > > > > </class> > > > > >> > > > > > > > <class name="Models.Measurement, Project" > > > >> table="Measurement"> > > > >> > > > > > > > <!--omitted RefId and version, all same as > > BaseGroup--> > > > >> > > > > > > > <property name="Title" column="Title" type="string" > > > >> > > not-null="true" > > > >> > > > > / > > > > >> > > > > > > > <bag name="Specialties" lazy="true" > > > >> > > table="MeasurementSpecialty"> > > > >> > > > > > > > <key column="MeasurementRefId" /> > > > >> > > > > > > > <many-to-many column="SpecialtyRefId" > > > >> > > class="Models.Measurement, > > > >> > > > > > > > Project" order-by="Title" /> > > > >> > > > > > > > </bag> > > > >> > > > > > > > </class> > > > > >> > > > > > > > <class name="Models.Member, Project" table="Member"> > > > >> > > > > > > > <!--omitted RefId and version, all same as > > BaseGroup--> > > > >> > > > > > > > <property name="Title" column="Title" type="string" > > > >> > > not-null="true" > > > >> > > > > / > > > > >> > > > > > > > <bag name="BaseGroups" lazy="true" > > > >> table="BaseGroupMember"> > > > >> > > > > > > > <key column="MemberRefId" /> > > > >> > > > > > > > <many-to-many column="BaseGroupRefId" > > > >> > > class="Models.BaseGroup, > > > >> > > > > > > > Project" order-by="Title" /> > > > >> > > > > > > > </bag> > > > > >> > > > > > > > <bag name="Scores" lazy="true"> > > > >> > > > > > > > <key column="MemberRefId" /> > > > >> > > > > > > > <one-to-many class="Models.Score, Project" /> > > > >> > > > > > > > </bag> > > > >> > > > > > > > </class> > > > > >> > > > > > > > <class name="Models.Scorer, Project" table="Score"> > > > >> > > > > > > > <!--omitted RefId and version, all same as > > BaseGroup--> > > > >> > > > > > > > <property name="Value" column="Value" type="double" > > not- > > > >> > > > > > > > null="false" /> > > > > >> > > > > > > > <many-to-one name="Measurement" > > column="MeasurementRefId" > > > >> > > > > > > > class="Models.Measurement, Project" not-null="true" /> > > > >> > > > > > > > <many-to-one name="Member" column="MemberRefId" > > > >> class="Member, > > > >> > > > > > > > Project" not-null="true" /> > > > >> > > > > > > > </class> > > > > >> > > > > > > > On Sep 17, 2:02 am, nadav s <[email protected]> wrote: > > > > >> > > > > > > > > what version are you using? (looks like the old one > > and > > > >> not > > > >> > > v3?) > > > >> > > > > > > > > what is you're actual scenario (with objects and > > > >> associations > > > >> > > that > > > >> > > > > make > > > >> > > > > > > > > sense)? > > > > >> > > > > > > > > On Thu, Sep 16, 2010 at 10:09 PM, Scott < > > > >> > > [email protected] > > > > >> > > > > > > wrote: > > > >> > > > > > > > > > I'm new to Linq and one thing I'm not understanding > > is > > > >> how to > > > >> > > > > form > > > >> > > > > > > > > > queries based on nhibernate objects that have > > > >> collections and > > > >> > > I > > > >> > > > > need > > > >> > > > > > > > > > to get down the tree on them. > > > > >> > > > > > > > > > Obj1.Bags.AnyObjInBag.MoreBags.AnyObjInBag > > > > >> > > > > > > > > > Maybe I'm doing it completely wrong but the only > > thing > > > >> that > > > >> > > some > > > >> > > > > what > > > >> > > > > > > > > > works on NH collections is if I do a cast on it > > like: > > > > >> > > > > > > > > > from O1 in NHObj.O1Bag.Cast<O1Type>() > > > >> > > > > > > > > > join O2 in NHSession.Linq<O2>() on O1 equals > > > >> O2.O1Reference > > > >> > > > > > > > > > select O2 > > > > >> > > > > > > > > > That works for a collection to a single parameter > > but > > > >> what > > > >> > > about > > > >> > > > > the > > > >> > > > > > > > > > intersection of 2 collections? > > > > >> > > > > > > > > > In TSQL I would have just had a bunch of inner joins > > > >> until I > > ... > > read more » -- 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.
