Hibernate package release issue
I have the following script in nHibernate (sorry for the messed up XML, but the text editor "helped me" too much while I was trying to copy it ;-))
<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>
code>
I need to write a query to return all users included in a group collection.
Essentially I want to execute the following sql:
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);
code>
I tried above but these are errors and I cannot figure out what I am missing. in the request.
Can someone point me in the right direction as to how I need to structure this request.
Thanks, Max Schilling
source to share
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 is exactly what I am looking for.
source to share
Try the following:
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);
source to share