nHibernate bag query issue

1
votes

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?

2
c#nhibernate

2 Answers

0
votes

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);
0
votes

I ended up finding a good solution for this:

if (Groups!= null && Groups.Count > 0)
{
    var items = Groups.ConvertAll(i => i.Id).ToArray();

    criteria.CreateCriteria("Groups", "g", JoinType.LeftOuterJoin);
    criteria.Add(Restrictions.In("g.Id", items));
}

This does exactly what I am looking for.