How can I add the Restriction Conjunction on Collection Nhibernate

Question

I have one collection where I need to queryOver and add the restriction.

public class User {
    public int Id {get;set;}
    public string Name {get;set;}
    public IList<Role> Roles {get;set;}
}

Now I want to query over in Nhibernate and also I need to get the result if the user passess to get any role with Admin role.

Conjunction conjunction = new Conjunction();
Disjunction disjunction = new Disjunction();

if (!string.IsNullOrEmpty(search))
{
    disjunction.Add(Restrictions.On<User>(e => e.Name)
        .IsLike(string.Format("%{0}%", search)));

    conjunction.Add(disjunction);
}

IList<User> users = NhSession.QueryOver<User>()
        .Where(conjunction)
        .OrderBy(x => x.Name).Asc()
        .Take(maxResults)
        .List();

How can I filter if I have a string parameter of RoleName and get those record from the query.

The way how to filter root object with restriction on its collection is via [16.8. Subqueries](http://nhforge.org/doc/nh/en/index.html#queryqueryover-subqueries). But because your mapping seems to be `many-to-many`, that would not be simple. I would suggest to change that mapping, and introduce pairing object... querying with subqueries will become very easy... — Radim Köhler, Sep 01 '14 at 10:58
how should I change the mapping. won't that disturb my entities — Moiz, Sep 01 '14 at 11:11
Yeap, it will for sure change your entities, maybe even disturb. Here is some [how to](http://stackoverflow.com/a/16827671/1679310). But you do not have to... It is my suggestion only. — Radim Köhler, Sep 01 '14 at 11:14

score 0 · Answer 1 · answered Sep 03 '14 at 13:49

You should just be able to join on Roles (assuming this is a mapped association) and filter:

Role roleAlias = null;

IList<User> users = NhSession.QueryOver<User>()
    .Where(conjunction)
    .JoinAlias(() => user.Roles, () => roleAlias)
        .Where(() => roleAlias.Name == "Admin") // or whatever your restriction is
    .OrderBy(x => x.Name).Asc()
    .Take(maxResults)
    .List();

How can I add the Restriction Conjunction on Collection Nhibernate

1 Answers1