i'd like to do this query by using Nhibernate : select productname,categoryname,companyname from products p, categories c , suppliers s where s.supplierid=s.supplierid and p.categoryid=c.categoryid. ( it's an ansi SQL query ). I'd like to select many properties from many diffirent presistent classes. Can i use ICriteria?

How can I do this?

This is the mapping file.

// product.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NorthwindCS" assembly="NorthwindCS">
<class name="Product" table="Products" lazy ="0">
<id name="ProductID" column="ProductID">
<generator class="increment"/>
</id>
<property name="ProductName" column="ProductName"/>
<property name="QuantityPerUnit" column="QuantityPerUnit"/>
<property name="UnitPrice" column="UnitPrice"/>
<property name="UnitsInStock" column="UnitsInStock"/>
<property name="UnitsOnOrder" column="UnitsOnOrder"/>
<property name="ReorderLevel" column="ReorderLevel"/>
<property name="Discontinued" column="Discontinued"/>
<property name ="SupplierID" column ="SupplierID"/>
<property name="CategoryID" column="CategoryID"/>
<many-to-one name="Supplier" column="SupplierID" class="Supplier"/>
<many-to-one name="Category" column="CategoryID" class="Category"/>
</class>
</hibernate-mapping>


//category.hbm.xml
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NorthwindCS" assembly="NorthwindCS">
<class name="Category" table ="Categories" lazy ="0">
<id name="CategoryID" column ="CategoryID">
<generator class="increment"/>
</id>
<property name="CategoryName" column="CategoryName"/>
<property name="Description" column="Description"/>
<property name="Picture" column="Picture"/>
<bag name="Products" lazy ="0">
<key column="CategoryID"/>
<one-to-many class ="Product"/>
</bag>
</class>
</hibernate-mapping>

// supplier.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NorthwindCS" assembly="NorthwindCS">
<class name="Supplier" table="Suppliers" lazy ="0">
<id name="SupplierID" column="SupplierID">
<generator class="increment"/>
</id>
<property name="CompanyName" column="CompanyName"/>
<property name="ContactName" column="ContactName"/>
<property name="ContactTitle" column="ContactTitle"/>
<property name="Address" column="Address"/>
<property name="City" column="City"/>
<property name="Region" column="Region"/>
<property name="PostalCode" column="PostalCode"/>
<property name="Country" column="Country"/>
<property name="Phone" column="Phone"/>
<property name="Fax" column="Fax"/>
<property name="Homepage" column="Homepage"/>
<bag name="Products">
<key column="SupplierID"/>
<one-to-many class ="Product"/>
</bag>
</class>
</hibernate-mapping>