Nhibernate get collection by ICriteria

Question

Hello, colleagues. I've got a problem at getting my entity. MApping:

     <?xml version="1.0" encoding="utf-8" ?>
    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
           assembly="Clients.Core"
           namespace="Clients.Core.Domains">
     <class name="Sales, Clients.Core" table='sales'>
        <id name="Id" unsaved-value="0">
          <column name="id" not-null="true"/>
          <generator class="native"/>
        </id>
        <property name="Guid">
           <column name="guid"/>
         </property>
        <set name="Accounts" table="sales_users" lazy="false">
            <key column="sales_id" />
            <element column="user_id" type="Int32" />
        </set>
     </class>

Domain:

   public class Sales : BaseDomain
   {
        ICollection<int> accounts = new List<int>();
        public virtual ICollection<int> Accounts
        {
            get { return accounts; }
            set { accounts = value; }
   }
    public Sales() { }           
   }

I want get query such as

SELECT * 
 FROM sales s 
 INNER JOIN sales_users su on su.sales_id=s.id 
 WHERE su.user_id=:N

How can i do this through ICriterion object?

Thanks a lot.

Answer 1

var sales = session.CreateCriteria(typeof(Sales))
                     .SetFetchMode("Accounts", FetchMode.Join)
                     .SetResultTransformer(Transformers.DistinctRootEntity)
                     .List<Sales>();

Answer 2

This is what I think the answer should be:

public IEnumerable<Sales> GetSalesForUser(int userId)
{
    return session.CreateCriteria<Sales>()
        .CreateAlias("Accounts", "accounts")
        .Add(Restrictions.Eq("accounts.UserId", "userId"))
        .List<Sales>();
}

But I'm confused by your model. It appears that Accounts has a many-to-many relationship with Sales, but you haven't mapped it that way. I'm not sure how to filter an int collection (HashSet in this case). You could try:

public IEnumerable<Sales> GetSalesForUser(int userId)
{
    return session.CreateCriteria<Sales>()
        .Add(Restrictions.Eq("Accounts", userId))
        .List<Sales>();
}