Hi,

Just a quick question. If I've got 2 tables that are joined in a 3rd table with a many-to-many relationship, is it possible to write an ICriteria with expressions in one of the tables and the join table?

Lets say the mapping file looks something like:


Is it then possible to write an ICriteria like the following?



When I try this, it tells me I the expected type is IList<Bag>, whereas the actual type is Bag.

Thanks.

Hi,

yes you can. Do something like this

ICriteria crit = session.CreateCriteria(typeof(TABLE1));
crit.Add(Expression.Eq("Name", name));
crit.Add(Expression.Eq("TABLE2.Bag", data2IDNumber));

where Name belongs to Table1 and Bag is the Table2 attribute.

Hi, thanks for the help. I'm not fully understanding what you're saying.

For examples sake, lets call the tables classes: Table1, Table2, and TableJN. TableJN joins Table1_ID and Table2_ID.

Mapping:


You're saying my ICreateria would have to be setup like this?


Then it just says it couldn't resolve the property.

You have 2 options in this case. Try to change your mappings or use detached criteria. I'm using mappings like this:

<class name="ExchangeRate" table="ExchangeRates">
<id name="Id" type="System.Guid" column="Id">
<generator class="guid" />
</id>
</property>
<property name="Rate" type="Decimal">
<column name="Rate" sql-type="decimal(19,5)" not-null="true" unique="false"/>
</property>
<many-to-one name="Currency" class="Currency" column="CurrencyId" not-null="true" cascade="none" lazy="false" fetch="join" />
</class>

with this mappings you can use criteria like I told you. If you keepyour mappings as is, use detached criteria.

use this sample from NHibernate help:

DetachedCriteria avgWeightForSex = DetachedCriteria.For(typeof(Cat), "cat2")
.SetProjection( Projections.Avg("Weight") )
.Add( Expression.EqProperty("cat2.Sex", "cat.Sex") );
session.CreateCriteria(typeof(Cat), "cat")
.Add( Subqueries.Gt("weight", avgWeightForSex) )
.List();