Have you looked at DetachedCriteria for the subselect? John Davidson
On Thu, Aug 5, 2010 at 1:46 PM, The CodeFool <[email protected]> wrote: > Hi, > > I have literally been trying to work it out for this long! > > That got me a little further > > If I do this in hql it works > Session.CreateQuery("from TableA a where a.id in (from a.TableATableB ab > where TableBTypeId = :id)") > .SetParameter("id", 2).List() > > How do I do it with an iCriteria - The rest of my code is all in iCriteria. > > My TableA class has an IList<TableATablB> TableATableBs > > I have a Mapping class for TableA that maps: > mapping.HasManyToMany(x => x.TableATableBs) > .Table("TableA_TableB") > .ParentKeyColumn("TableAId") > .ChildKeyColumn("TableBId") > .FetchType.Select(); > > I then have the TableB class which has a property > public virtual TableBType TableBType { get; protected set; } > > I then have a Mapping class for TableB that maps > mapping.HasOne(x => x.TableBType); > > All I want to do is query on TableBType.Id > > Thanks again for your help! > > > > On Tue, Aug 3, 2010 at 9:58 PM, José F. Romaniello <[email protected] > > wrote: > >> Probably something like this: >> >> session.createCriteria<TableA>() >> .CreateAlias("TableATableB", "tatb") >> >> .SetProjection(Projections.ProjectionList().Add(Projections.Property("tatb. >> TableBType"),"btype")) >> .Add(Expression.In("btype", new[]{1,2,3})) >> .List..... >> >> In linq will be something as follows: >> >> var values = new[]{1,2,3}; >> session.Query<TableA>().Where(ta => ta.TableATableB.Any(tatb => >> values.Contains(tatb.BType.Id)); >> >> in hql: >> >> from TableA ta >> join ta.TableATableB tatb >> where tatb.BType in :values >> >> with queryover api I don't know. >> >> >> >> >> 2010/8/3 thecodefool <[email protected]> >> >>> >>> Thanks for the reply. >>> >>> I need data from all the tables. I am using fluent nhibernate >>> How do I perform the in clause on the above mentioned with an >>> ICriteria? >>> >>> Thanks again >>> >>> On Aug 3, 9:08 pm, José F. Romaniello <[email protected]> wrote: >>> > I think you should not use many-to-many because you have a property in >>> the >>> > middle table... so map the middle as an Entity... >>> > >>> > <class name="TableA"> >>> > <id name="ID"> >>> > <generator class="hilo"> >>> > </generator> >>> > </id> >>> > <property name="Name"/> >>> > <set name="TableATableB" inverse="false"> >>> > <key column="TableAID "/> >>> > <one-to-many class="TableATableB" /> >>> > </set> >>> > </class> >>> > >>> > <class name="TableB"> >>> > <id name="ID"> >>> > <generator class="hilo"> >>> > </generator> >>> > </id> >>> > <property name="Name"/> >>> > </class> >>> > >>> > <class name="TableBType"> >>> > <id name="ID"> >>> > <generator class="hilo"> >>> > </generator> >>> > </id> >>> > <property name="Name"/> >>> > </class> >>> > >>> > <class name="TableATableB"> >>> > <composite-id> >>> > <key-many-to-one name="TableA" /> >>> > <key-many-to-one name="TableB" /> >>> > <key-many-to-one name="TableBType" /> >>> > </composite-id> >>> > </class> >>> > >>> > 2010/8/3 thecodefool <[email protected]> >>> > >>> > >>> > >>> > > Hi my question, I hope is a simple one >>> > >>> > > I have four tables >>> > >>> > > TableA >>> > > TableAId >>> > > Name >>> > >>> > > TableB >>> > > TableBId >>> > > Name >>> > >>> > > TableBType >>> > > TableBTypeId >>> > > Name >>> > >>> > > TableA_TableB >>> > > TableAId >>> > > TableBId >>> > > TableBTypeId >>> > >>> > > I have normal classes for each nothing fancy, but I do not have a >>> > > class TableA_TableB (Not sure what to do) >>> > >>> > > I would like to create the following using an ICriteria but I cannot >>> > > for the life of me figure it out. >>> > >>> > > SELECT a.* FROM TableA a >>> > > INNER JOIN TableA_TableB ab ON a.TableAId = ab.TableAId >>> > > WHERE ab.TableBTypeId IN(1,2,3) >>> > >>> > > Any help is greatly appreciated >>> > >>> > > Thanks >>> > >>> > > -- >>> > > 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]<nhusers%[email protected]> >>> <nhusers%[email protected]<nhusers%[email protected]>> >>> > > . >>> > > For more options, visit this group at >>> > >http://groups.google.com/group/nhusers?hl=en. >>> >>> -- >>> 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]<nhusers%[email protected]> >>> . >>> For more options, visit this group at >>> http://groups.google.com/group/nhusers?hl=en. >>> >>> >> -- >> 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]<nhusers%[email protected]> >> . >> For more options, visit this group at >> http://groups.google.com/group/nhusers?hl=en. >> > > -- > 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]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > -- 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.
