Hello,

I have a class that is using a composite-element for a many-to-many relationship with an extra column:

class Product {
productId;
ICollection<AditionalPropertyProduct> adProps;
}

class AditionalPropertyProduct {
value;
Product prod;
AditionalProperty prop;
}

class AditionalProperty {
code;
description;
}

Now I have to search those products that has some specific values for some specific adition property.

the resulting query should be something like
select * from Product where exists (select * from Product.adProps ad where ad.prop.code='MyCustomProp' and ad.value='20')

I tried different combinations like detached queries (getting a "No persister for") like Explresion.SQL in the ICriteria with no luck.

Is there anyone that faced this problem already?

Thanks,
Fernando

Have you tried

select p from Product p join p.adProps ad
where ad.prop.code='MyCustomProp' and ad.value='20'

Can you post the mappings ?

This is the mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="" assembly="">

<class name="PropiedadDTO" table="PROPIEDADES">

<id name="Id" column="id_prop" type="Int32" unsaved-value="-1">
<generator class="identity" />
</id>
<property name="Titulo" column="titulo" type="String" />

<set name="VariablesAdicionales" table="PROPS_VAR_ADIC" lazy="false" fetch="join" cascade="none">
<key column="id_prop"/>
<composite-element class="VarAdicPropiedadDTO">
<parent name="Propiedad"/>
<property name="Valor" column="valor" type="String"/>
<many-to-one name="VariableAdicional" column="cod_var_adic"/>
</composite-element>
</set>

</class>
</hibernate-mapping>

Does anyone faced this problem?

Making an "exists" clause using an ICriteria object and the exists should filter the data?

Thanks

You can perform subqueries with detached criterias:

crit.Add(Subqueries.Exists(detachedCriteria);

What have you tried so far ? Post the criterias and the exceptions.