C# – nHibernate bag query issue

cnhibernate

I have the following scenario in nHibernate: 

<class name="TestApp.Components.User,TestApp.Components"  table="Users">
    <id name="Id" column="UserId" type="Int32" unsaved-value="0">
      <generator class="native"></generator>
    </id>
    <property name="UserName" type="string" length="100" not-null="false"/>
    <bag name="Groups" table="User_Group" lazy="true" >
      <key column="UserId"></key>
      <many-to-many class="Group" column="GroupId" />
    </bag>  
<class>

I need to be able to write an query to return all the users that are in a collection of groups.

Essentially I want the following sql to execute:

SELECT DISTINCT username from users u, user_group ug  
WHERE u.userid = ug.userid  
AND (ug.groupid = 1 OR ug.groupid = 2 OR ug.groupid = 3)

I know I can do this in HQL, but I want to do it using the ICriteria interface.

var session = sessionManager.GetSession();  
var items = session.CreateCriteria(typeof(User));  

ICriterion criterion = null;  

foreach (var groupid in Groups)  
{  
    ICriterion tempCriterion = Restrictions.Eq("Groups.GroupId", groupid);  
    criterion = criterion == null ? tempCriterion : Restrictions.Or(criterion, tempCriterion);  
}  
items.Add(criterion);

I've tried the above but it errors, and I can't seem to figure out what I am missing. in the query.

Can someone point me in the right direction with regards to how I need to structure this query?

Best Answer

Try this:

var groupsCrit = items.CreateCriteria("Groups");
var groupIds = Restrictions.Disjunction();
foreach (var groupid in Groups)
{
    groupIds.Add(Restrictions.Eq("Id", groupid)); // "Id" should be the name of the Id property on the Group class
}
groupsCrit.Add(groupIds);
Related Topic