NHibernate query collection

Question

I have a users and roles table. A user can have multiple roles.

I want to grab all of the users without a specific role. The problem is if a user has 2 roles, one being the role we don't want, the user will still be returned.

public IList<User> GetUserByWithoutRole(string role)
    {
        return CreateQuery((ISession session) => session.CreateCriteria<User>()
            .CreateAlias("Roles", "Roles")
            .Add(!Restrictions.Eq("Roles.RoleDescription", role))
            .List<User>());
    }

The only solution I came up with was client side

public IEnumerable<User> GetUserByWithoutRole(string role)
        {
            return CreateQuery((ISession session) => session.CreateCriteria<User>()
                .CreateAlias("Roles", "Roles")
                .Add(!Restrictions.Eq("Roles.RoleDescription", role))
                .List<User>()).Where(u => u.Roles.FirstOrDefault(r => r.RoleDescription == role) == null);
        }

Anyone know of a better solution? Thanks!

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

If you want to do that in nhibernate you'll have to do two queries or a subquery. Here is a similar post:

FluentNHibernate query on many-to-many relationship objects

edited May 23 '17 at 10:34

Community

1
1

answered Feb 12 '12 at 03:17

Cole W

15,123
6
51
85

score 1 · Accepted Answer · answered Feb 12 '12 at 10:16

Alternatively you can use Criteria API to create subquery

var subquery = DetachedCriteria.For<Role>("role");
subquery.Add(Restrictions.EqProperty("role.User.id", "user.id"))
    .SetProjection(Projections.Property("role.RoleDescription"));

var users = session.CreateCriteria<User>("user")
    .Add(Subqueries.NotIn(role, subquery))
    .List<User>();

score 0 · Answer 3 · answered Feb 12 '12 at 10:42

Thanks, for anyone interested this is what I finished up with:

public IEnumerable<User> GetUserByWithoutRole(string role)
        {
            var subQuery = DetachedCriteria.For<User>()
            .CreateAlias("Roles", "Roles")
            .SetProjection(Projections.Property("UserID"))
            .Add(Restrictions.Eq("Roles.RoleDescription", role));

             return _session.CreateCriteria<User>()
            .SetResultTransformer(new DistinctRootEntityResultTransformer())
            .Add(Subqueries.PropertyNotIn("UserID", subQuery))
            .AddOrder(Order.Asc("FirstName"))
            .List<User>();
        }

NHibernate query collection

3 Answers3